CURSOR Keeps Closing

[ansonee]

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!

[Mark A]

"ansonee" <an*****@yahoo.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...

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!

Cursor closes at COMMIT unless you use the WITH HOLD option on cursor.

[ak_tiredofspam]

have you tried accomplishing the same task using only SQL, i.e.

UPDATE AIM.ArchiveRetrieval SET STATUSID = 3
WHERE
((ESTIMATEDRESPONSETIME < CURRENT TIMESTAMP --AND EXPIRATIONDATE <
DATE(CURRENT TIMESTAMP)
AND STATUSID = 2)
OR
(ESTIMATEDRESPONSETIME < CURRENT TIMESTAMP AND EXPIRATIONDATE IS NULL
AND STATUSID = 2))
AND
(
(SELECT COUNT(IMAGEFRONT) FROM AIM.AIMRETRIEVEDITEM
WHERE ARCHIVERETRIEVALID = AIM.ArchiveRetrieval.ARCHIVERETRIEVALID
AND LENGTH(IMAGEFRONT) > 0)
<>
(SELECT ITEMCOUNT FROM AIM.ARCHIVERETRIEVAL
WHERE ARCHIVERETRIEVALID = AIM.ArchiveRetrieval.ARCHIVERETRIEVALID )
)