Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253

    Question Unanswered: 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!
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Yes, the commit is closing the cursor. Add "WITH HOLD" clause to you DECLARE Cursor statement.

    HTH

    Andy

  3. #3
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    Works like a charm!!

    Thanks!
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    But, please make sure to close the cursor before committing the 'final' time

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    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;
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Just ignore my last message ... I guess, all open curosrs in a SP will be closed automatically when you end it

    Cheers
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Mar 2004
    Posts
    4

    Similar issue only I'm using WITH HOLD

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •