Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2013
    Posts
    2

    Unanswered: DB2 LOOP statement with cursor terminating early

    I have a stored procedure in DB2 (iSeries V5R3M0) which opens a cursor and enters a loop statement. The loop fetches the next row from the input (work queue) table, processes the record, then deletes the row from the input table. The process works fine, except that a random number of rows is processed before the loop terminates - often 1 row, sometimes 2 and occasionally 3 rows.

    Does anyone have any ideas why it finishes early? I can't see any faults in the logic, so I am assuming it maybe to do with deleting the input row, but in that case why does it sometimes process 2 or 3 rows?

    Here is a stripped down version of the procedure: -

    Code:
    CREATE PROCEDURE DOTRANSFER (
     OUT PROCESSED INTEGER)
    LANGUAGE SQL
    BEGIN
    DECLARE V_PTNO INTEGER ;
    *** lot more declares
    --
    DECLARE AT_END SMALLINT DEFAULT 0 ;
    DECLARE NOT_FOUND CONDITION FOR SQLSTATE '02000' ;
    DECLARE CURREQUESTS ASENSITIVE NO SCROLL CURSOR WITH HOLD WITHOUT RETURN FOR
        SELECT *
        FROM PENDTRPF
        ORDER BY PTNO ;
    DECLARE CONTINUE HANDLER FOR NOT_FOUND
        SET AT_END = 1 ;
    --
    -- Initialise
    --
    SET PROCESSED = 0 ;
    OPEN CURREQUESTS ;
    FETCH_LOOP : LOOP
        --
        -- Fetch the next transfer request
        --
        FETCH CURREQUESTS INTO
            V_PTNO , ...;
        IF AT_END <> 0 THEN
            LEAVE FETCH_LOOP ;
        END IF ;
    *** Initialisation code
        --
        -- Start the update
        --
        COMMIT ;
    *** Update or insert target table records
        --
        -- Done
        --
        DELETE FROM PENDTRPF WHERE PTNO = V_PTNO ;
        SET PROCESSED = PROCESSED + 1 ;
        COMMIT ;
    END LOOP ;
    END@

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Any statement in your loop could set off your condition handler that will set the AT_END variable which will terminate your loop. What you need to do is to SET AT_END = 0 just before the fetch.

    Andy

  3. #3
    Join Date
    Feb 2013
    Posts
    2
    That fixed it - thanks a lot.

Posting Permissions

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