DB2 SQLCODEs -501 to -504: cursor lifecycle errors

How to read this range

Class 24 SQLSTATE values talk about cursor state. Class 34 is “invalid cursor name.” 42912 is a syntax/semantics restriction on which columns a positioned UPDATE may touch. None of these mean “table missing” or “deadlock”—though a prior -911 / -913 can leave you with a closed cursor so thenext FETCH shows -501.

-501 cursor not open on FETCH or CLOSE

IBM: THE CURSOR IDENTIFIED IN A FETCH OR CLOSE STATEMENT IS NOT OPEN. The program fetched or closed when the cursor was not open. After a cursor is closed, any later FETCH or CLOSE for that cursor gets -501.

IBM lists typical ways the cursor became closed:

• A previous CLOSE statement
• A commit or rollback operation (unless WITH HOLD kept it open across COMMIT)
• Error accessing an unavailable object (−679, −901, −904, −909, −910, −911, −913, −952, …)
• Error updating an object (−404, −652, …)
• Arithmetic error on an aggregate (−802)
• Error during predicate processing

System action: cannot process. Programmer response: ensure the cursor is open when FETCH/CLOSE runs. SQLSTATE 24501.

1
2
3
4
5
           EXEC SQL OPEN C1 END-EXEC.
           EXEC SQL FETCH C1 INTO :HV-EMPNO END-EXEC.
           EXEC SQL COMMIT END-EXEC.
      * Without WITH HOLD, C1 is closed — next FETCH → -501
           EXEC SQL FETCH C1 INTO :HV-EMPNO END-EXEC.

Fix: OPEN again after COMMIT, or declare DECLARE C1 CURSOR WITH HOLD FOR … when your design needs hold across commits. Also check for silent CLOSE in error handlers that always CLOSE “just in case.”

-502 OPEN when already open

IBM: THE CURSOR IDENTIFIED IN AN OPEN STATEMENT IS ALREADY OPEN. System action: statement fails; the cursor is unchanged (not reopened, not reset to first row). Programmer response: do not OPEN an open cursor. SQLSTATE 24502.

Loops that OPEN every iteration without CLOSE are the usual offender. Another pattern: two paths both OPEN “to be safe.” Db2 will not treat the second OPEN as rewind—you must CLOSE then OPEN, or use a design that opens once.

1
2
3
           EXEC SQL OPEN C1 END-EXEC.
           EXEC SQL OPEN C1 END-EXEC.
      * Second OPEN → SQLCODE -502; cursor still as after first OPEN

-503 column not in FOR UPDATE clause

IBM: A COLUMN CANNOT BE UPDATED BECAUSE IT IS NOT IDENTIFIED IN THE UPDATE CLAUSE OF THE SELECT STATEMENT OF THE CURSOR. Positioned UPDATE tried to change a column that was not listed in FOR UPDATE of the cursor declaration. Every updatable column must appear there.

System action: cannot process; no data updated. Programmer response: add the column name to FOR UPDATE. SQLSTATE 42912.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
           EXEC SQL
                DECLARE C1 CURSOR FOR
                     SELECT EMPNO, SALARY
                       FROM EMP
                      WHERE WORKDEPT = :HV-DEPT
                        FOR UPDATE OF SALARY
           END-EXEC.
      *
           EXEC SQL
                UPDATE EMP
                   SET BONUS = :HV-BONUS
                 WHERE CURRENT OF C1
           END-EXEC.
      * BONUS not in FOR UPDATE OF → -503

Either add BONUS to FOR UPDATE OF SALARY, BONUS, or stop updating BONUS through this cursor. Ambiguous or read-only cursors need FOR FETCH ONLY or isolation fixes—that is a different family of codes than -503.

-504 cursor name not declared (or no longer valid)

IBM: CURSOR NAME cursor-name IS NOT DECLARED. One of several situations:

• Cursor was not DECLARED or ALLOCATEd before reference
• Positioned UPDATE/DELETE on an allocated cursor that does not support that operation
• Allocated cursor was CLOSEd (deallocated) before this reference
• ROLLBACK deallocated an allocated cursor
• Allocated cursor’s stored-procedure cursor was not WITH HOLD and COMMIT (explicit or COMMIT_ON_RETURN) deallocated it
• Associated stored procedure was called again; new result sets deallocated the old cursor name
• Declaration not in scope for this reference

System action: cannot process. Programmer response: check completeness and spelling; DECLARE/ALLOCATE before use. If precompile used SQL(Db2) and warned on DECLARE, fix those warnings. For allocated cursors after COMMIT/ROLLBACK/CLOSE: call the procedure again and reissue ASSOCIATE LOCATORS / ALLOCATE CURSOR, or declare WITH HOLD in the procedure so COMMIT keeps the result set. SQLSTATE 34000.

1
2
3
4
5
6
      * Typo: declared C_EMP but FETCHed C1 → -504
           EXEC SQL DECLARE C_EMP CURSOR FOR
                SELECT EMPNO FROM EMP
           END-EXEC.
           EXEC SQL OPEN C_EMP END-EXEC.
           EXEC SQL FETCH C1 INTO :HV-EMPNO END-EXEC.

Related codes you will hit next

-507 / -508

Positioned UPDATE/DELETE when the cursor is not open (−507) or not positioned on a row (−508). Same lifecycle discipline as -501. If your error handler only checks for -501 after FETCH, add the positioned-update cousins so batch updaters fail clearly.

-518 / prepare issues

Dynamic cursors need a successful PREPARE before OPEN. Wrong order surfaces different codes—always read the Codes book for the number you actually received. A -518-style “SQL statement not yet prepared” problem is not fixed by CLOSE/OPEN alone; fix the prepare name and then OPEN.

COBOL patterns that prevent the whole family

Keep one clear state machine per cursor: closed → open → positioned → closed. After COMMIT, either treat the cursor as closed (re-OPEN) or document WITH HOLD and still handle -501 if an earlier error closed it. Never OPEN “just in case” inside a loop. For updatable cursors, list every column you might SET in FOR UPDATE OF at declare time—adding columns later without changing the DECLARE is how -503 appears in production months after the first version shipped.

When you allocate result-set cursors from stored procedures, treat ASSOCIATE LOCATORS + ALLOCATE CURSOR as part of the open path. After COMMIT without WITH HOLD on the procedure cursor, or after calling the procedure again, re-associate and re-allocate before FETCH—otherwise -504 looks like a mysterious “name vanished” bug.

Explain It Like I'm Five

A cursor is a bookmark in a book. -501 is “you tried to read with the book closed.” -502 is “you tried to open a book that is already open.” -503 is “you tried to erase a word that was not on the erasable list.” -504 is “you asked for bookmark Blue but you never made a bookmark named Blue” (or someone threw it away after COMMIT).

Exercises

1. Write a small program that OPENs twice without CLOSE and capture -502.
2. OPEN, FETCH, COMMIT (no WITH HOLD), FETCH again—show -501. Then add WITH HOLD and retest.
3. Cause -503 with WHERE CURRENT OF, then fix FOR UPDATE OF.
4. List three IBM-documented reasons a cursor might already be closed before your FETCH (from the -501 explanation).
5. For result-set allocation, explain when COMMIT causes -504 and how WITH HOLD helps.