I wrote a stored procedure that contains some COMMIT logic, but keep runnign into an odd problem. As soon as the procedure gets to the COMMIT statement, the CURSOR closes. Here's the procedure:

CREATE PROCEDURE AIM.UPDATEARCHIVERETRIEVALSTATUS ( )
SPECIFIC AIM.UPDATEARCRETSTS
LANGUAGE SQL

P1: BEGIN
DECLARE RECORDCOUNT INTEGER;
DECLARE intLOOPS INTEGER;
DECLARE intImageCount INTEGER;
DECLARE decArchiveRetrievalID DECIMAL (13,0);
DECLARE AT_END int DEFAULT 0;
DECLARE NOT_FOUND CONDITION FOR SQLSTATE '02000';
DECLARE UPDATE_CURSOR CURSOR FOR
SELECT ARCHIVERETRIEVALID FROM AIM.ARCHIVERETRIEVAL WHERE ESTIMATEDRESPONSETIME < CURRENT TIMESTAMP --AND EXPIRATIONDATE < DATE(CURRENT TIMESTAMP)
AND STATUSID = 2
--OR SELECT ARCHIVERETRIEVALID FROM AIM.ARCHIVERETRIEVAL WHERE
OR
ESTIMATEDRESPONSETIME < CURRENT TIMESTAMP AND EXPIRATIONDATE IS NULL
AND STATUSID = 2;

DECLARE CONTINUE HANDLER for NOT_FOUND
SET AT_END = 1;

SET intLOOPS = 0;
SET RECORDCOUNT = 0;

OPEN UPDATE_CURSOR;
FETCH_LOOP:
LOOP
FETCH UPDATE_CURSOR INTO decArchiveRetrievalID;
SET intImageCount = (SELECT COUNT(IMAGEFRONT) FROM AIM.AIMRETRIEVEDITEM WHERE ARCHIVERETRIEVALID = decArchiveRetrievalID
AND LENGTH(IMAGEFRONT) > 0);
IF AT_END = 1 THEN
LEAVE FETCH_LOOP;
ELSEIF intImageCount = (SELECT ITEMCOUNT FROM AIM.ARCHIVERETRIEVAL WHERE ARCHIVERETRIEVALID = decArchiveRetrievalID) THEN
ITERATE FETCH_LOOP;
END IF;
UPDATE AIM.ArchiveRetrieval SET STATUSID = 3 WHERE ARCHIVERETRIEVALID = decArchiveRetrievalID;
SET RECORDCOUNT = RECORDCOUNT + 1;
IF RECORDCOUNT = 10 THEN
COMMIT;
SET intLOOPS = intLOOPS + 1;
SET RECORDCOUNT = 0;
END IF;
END LOOP FETCH_LOOP;
CLOSE UPDATE_CURSOR;

CALL AIM.UPDATEAIMRETRIEVALSTATUS_CRON();

END P1

For instance, I know there should be 45 rows updated. The procedure gets through the first set of 10 updates and then it bombs and gives me the following error:

A database manager error occurred.[IBM][CLI Driver][DB2/6000] SQL0501N The cursor specified in a FETCH or CLOSE statement is not open. SQLSTATE=24501

Can I assume the COMMIT is what's causing it to close the cursor? Is there any way to address this?

THanks for any help!

Yes, the commit is closing the cursor. Add "WITH HOLD" clause to you DECLARE Cursor statement.

HTH

Andy

Works like a charm!!

Thanks!

But, please make sure to close the cursor before committing the 'final' time

Cheers
Sathyaram

So should there be one more COMMIT after closing the cursor? For example:

.......
END CASE;
ITERATE FETCH_LOOP;

END IF;
--UPDATE AIM.ArchiveRetrieval SET STATUSID = 3 WHERE ARCHIVERETRIEVALID = decArchiveRetrievalID;
END LOOP FETCH_LOOP;
CLOSE UPDATE_CURSOR;

COMMIT;

Just ignore my last message ... I guess, all open curosrs in a SP will be closed automatically when you end it

Cheers

I have a stored proc that creates a WITH HOLD cursor.

As I loop through the cursor I do three things

1. use the cursor information to create and execute a dynamic update stmt
set updStmt = "update ....";
prepare sqlStmt from updStmt;
execute sqlStmt;

2 insert into a history table
insert into history (fld1, fld2, fld2) values ('a','b','c');

3. commit

...so the procedure looks something like this

Declare csrABC cursor with hold for
declare continue handler for sqlstate '02000'
set at_end = 1;

open csrABC

cursorLoop:
loop
fetch csrABC

if at_end = 1
then
leave cursorLoop;
end if;

-- if I change the below set to a simple insert stmt it all works fine
set updStmt = 'some update';
prepare sqlStmt from updstmt;
execute sqlStmt;

insert into history
(fld1, fld2, fld3)
values (val1, val2, val3);

commit;

loop;

close csrABC;


The commit closes my cursor so that, on the second fetch, I reach an at-end condition;

I could have sworn this was working fine earlier this week and our DBA applied a fixpack (we're running Version 8 of UDB on an AIX box). However, after the fixpack it seems to be broken.

If I change the dynamic update statement to a simple dynamic insert into a test table it all works fine but the execute of the update statement closes the cursor every time.

Also, the table being updated is not referenced in the cursor select or anywhere else in the stored procedure.

Any help would be welcome... or maybe pointing me to a site where I can search for issues with fixpacks