title: DB2 LOOP statement with cursor terminating early
body: <p>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.</p>
  
  <p>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?</p>
  
  <p>Here is a stripped down version of the procedure: -</p>
  
  <pre><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 &lt;&gt; 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@
  </code></pre>