I'm sort of new to DB2, using v8.1 on AIX...

I'm trying to figure out the best way to determine when I'm at the end of a cursor. So far I've seen it done two ways in examples.
1) run a count(*) with the same where clause used in the cursor and assign the result to a variable, then use that count for a fixed iteration loop.

2) declare a CONTINUE HANDLER for SQLSTATE 02000 (not found) and iterate until the CONTINUE HANDLER fires

Both approaches seem flawed. With option 1 you run the risk of the table changing between the select for the cursor and the select of the counter variable.
With option 2 you can get a SQLSTATE 02000 in the body of the cursor for other operations not related to the cursor fetch.


Here is an example using option 2:

CREATE PROCEDURE CURSOR_TEST ()
SPECIFIC CURSOR_TEST
LANGUAGE SQL
P1: BEGIN

DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';

DECLARE var1 varchar(20);
DECLARE at_end SMALLINT DEFAULT 0;
DECLARE not_found CONDITION FOR SQLSTATE '02000';

DECLARE c1 CURSOR FOR
SELECT field1
FROM someTable
WHERE field2 = 'X'

DECLARE CONTINUE HANDLER for not_found SET at_end = 1;
OPEN c1;
WHILE at_end = 0 DO
FETCH c1 INTO var1;
-- do something...
END WHILE;

CLOSE c1;

END P1


What is the best approach?

1. if you want the result to be stable for the select you
- lock the table in share (no update possible)
- cursor : select .... for update : the lock level will be increased (no updates
allowed))
2. sqlstate 2000 can only be returned if no rows qualify anymore - all fetched or
no rows qualify

Not if you separate the cursor loop and the handler into its own BEGIN...END block.

So from your suggestion NI, I now have the following, which seems to work. Thanks for the help.

CREATE PROCEDURE CURSOR_TEST ()
SPECIFIC CURSOR_TEST
LANGUAGE SQL
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN

-- declare these for DB2, it sets them implicitely (see Application Development Guide: Programming Server Applications)
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';

DECLARE var1 varchar(20);
DECLARE at_end SMALLINT DEFAULT 0;
DECLARE not_found CONDITION FOR SQLSTATE '02000';

DECLARE c1 CURSOR FOR
SELECT field1
FROM someTable
WHERE field2 = 'X'

OPEN c1;
WHILE at_end = 0 DO
P2: BEGIN
DECLARE CONTINUE HANDLER for not_found SET at_end = 1;
FETCH c1 INTO var1;
END P2;

-- do something...
END WHILE;

CLOSE c1;

END P1