What it means
A cursor has to be opened before the program can fetch from it, and it stays open until the program closes it or the unit of work ends. Db2 tracks that state for every cursor. If a FETCH or CLOSE names a cursor that is closed at that moment, Db2 rejects it with -501.
The error shows up at the FETCH, but the cause is almost always earlier in the program. Something closed the cursor, or kept it from opening, and the program carried on without noticing.
Two rules catch most people out. A COMMIT closes every cursor not declared WITH HOLD. A ROLLBACK closes every cursor, WITH HOLD or not, and that includes the rollback Db2 does for you on a -911.
Common causes
- The OPEN failed with a negative SQLCODE, such as -904 or -911, and the program went on to FETCH without checking it.
- The program commits inside the fetch loop, and the cursor wasn't declared WITH HOLD.
- A ROLLBACK, or a -911 deadlock or timeout, rolled back the unit of work and closed all cursors.
- The loop closed the cursor when it got +100, then the logic fell through to another FETCH or a second CLOSE.
- In CICS, the cursor was opened in one task and fetched in a later one. Cursors don't survive the end of a task, so a pseudo-conversational program has to open the cursor again each time.
How to find the problem
- Look at the SQLCODE of the OPEN for the same cursor. If the program logs its SQLCODEs, the OPEN's result is usually a few lines above the -501.
- Search the program for COMMIT and ROLLBACK statements, and for calls to routines that commit, between the OPEN and the failing FETCH.
- Check the DECLARE CURSOR for WITH HOLD if the program commits inside the loop.
- Follow the loop's exit path. Make sure the CLOSE runs once and no FETCH can run after it.
- If a -911 happened earlier in the run, the -501 is a knock-on error. Fix the -911 first.
How to fix it
- Check SQLCODE after every OPEN, and stop or retry if it's negative.
- Declare cursors WITH HOLD when the program commits during the fetch loop, as in
DECLARE CSR1 CURSOR WITH HOLD FOR .... - After a ROLLBACK or -911, open the cursor again and reposition it from the last committed key, for example with
WHERE KEY > :LAST-KEY. Save that key with each commit in a restart table or checkpoint record. - In CICS, open the cursor in the same task that fetches from it, and carry the position between screens as a saved key.
- In cleanup code a -501 on CLOSE is harmless, because the cursor may already be closed. On FETCH, treat it as a real error.
What it looks like
CLMBAT1 - OPEN CSR-CLAIMS SQLCODE -911
CLMBAT1 - FETCH CSR-CLAIMS FAILED
DSNT408I SQLCODE = -501, ERROR: THE CURSOR IDENTIFIED IN A FETCH OR CLOSE STATEMENT IS NOT
OPEN
DSNT418I SQLSTATE = 24501 SQLSTATE RETURN CODE