469,588 Members | 2,688 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,588 developers. It's quick & easy.

CURSOR Keeps Closing

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!

Nov 12 '05 #1
2 9290
"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.
Nov 12 '05 #2
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 )
)

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by dinesh prasad | last post: by
1 post views Thread by ilPostino | last post: by
2 posts views Thread by Nathan Sokalski | last post: by
5 posts views Thread by Paul M | last post: by
2 posts views Thread by Florian Lindner | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.