Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2012
    Posts
    82

    Unanswered: Db2 SP stuck on cursour

    Hi,
    I am writing an SP and a cursor is causing a problem. The problem code is

    DECLARE GLOBAL TEMPORARY TABLE ACCOUNT_TABLE ( ACCOUNT VARCHAR (20) ) WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED IN USERTEMP_2;
    INSERT INTO SESSION.ACCOUNT_TABLE (ACCOUNT) SELECT ITEM FROM TABLE(DB2INST1.SPLIT_LIST(v_CHIP_ID_01,','));
    acurs: BEGIN
    DECLARE a1 CURSOR WITH HOLD FOR select ACCOUNT FROM SESSION.ACCOUNT_TABLE WITH UR;
    OPEN a1;
    FETCH FROM a1 INTO v_CHIP_ID_DUMMY;
    SET v_SQLCODE = SQLCODE;

    WHILE (v_SQLCODE != 100) DO
    CALL SECURITY.SECURITY_ACCT_UD( 'ADD', v_GROUP_NAME,v_CHIP_ID_DUMMY, 'INCLUDE', 0);
    FETCH FROM a1 INTO v_CHIP_ID_DUMMY;
    END WHILE;
    CLOSE a1;
    END acurs;



    The problem I am facing is that the SP stucks at the "DECLARE a1 CURSOR WITH HOLD FOR select ACCOUNT FROM SESSION.ACCOUNT_TABLE WITH UR;" line. Any ideas why it is doing so. If I comment this part out the rest of SP runs fine.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Define "stuck".
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jan 2012
    Posts
    82
    The SP keeps executing and when I check the current SQL being executed it says that the statement

    "DECLARE a1 CURSOR WITH HOLD FOR select ACCOUNT FROM SESSION.ACCOUNT_TABLE WITH UR;"

    is not executed and is stated as IDLE.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Well, since you only set the value of v_SQLCODE outside the loop, there is very little chance that it will change its value to 100 by itself.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Jan 2012
    Posts
    82
    Oh crap, my bad its 2 Am in the morning here and I am knackered, totally missed that

    Thanks alot

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    That's why I never work after midnight (anymore).
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •