SQL0501 on IBM i: Cursor Not Open and How to Investigate It

Who this is for: RPG and SQL developers who write embedded SQL with explicit cursors (DECLARE / OPEN / FETCH / CLOSE), and the people who support those programs in batch and interactive jobs. If you have hit SQLCODE -501 or message ID SQL0501 (“Cursor &1 not open”) in a job log or an SQLRPGLE program, this article explains why the cursor was closed, how to find the statement that closed it, and how to structure the code so it does not happen again.

Prerequisites: you should already know how to declare and loop through a cursor. This article does not teach cursor basics. For cursor loops in SQL procedures, see DB2 for i SQL Stored Procedures and User-Defined Functions in 2026. It also helps to know the basics of commitment control and activation groups.

What SQL0501 means

Confirmed against IBM’s Listing of SQL messages for IBM i 7.5 and the IBM i 7.6 SQL messages and codes reference (see Sources Consulted):

The cause text says the cursor named in &1 “was specified in a FETCH or CLOSE statement, but the cursor is not open.” In other words, by the time your FETCH or CLOSE runs, Db2 for i has no open cursor by that name for the current program or module call. The cursor may never have been opened, or something closed it before you got there.

Two closely related messages are worth knowing because they come from the same mistakes. Both are confirmed in the same IBM listing:

• SQL0507 (SQLCODE -507, also SQLSTATE 24501), also with the text “Cursor &1 not open.” It fires when the closed cursor is named in a positioned UPDATE ... WHERE CURRENT OF or DELETE ... WHERE CURRENT OF instead of a FETCH or CLOSE. Because both messages share SQLSTATE 24501, code that tests only SQLSTATE = '24501' catches both.
• SQL0502 (SQLCODE -502, SQLSTATE 24502), “Cursor &1 already open or allocated.” This is the opposite problem: an OPEN on a cursor that is still open. Programs that hit SQL0501 on one call often hit SQL0502 on the next, because the open/close bookkeeping is wrong in both directions.

What causes SQL0501

IBM’s cause text for SQL0501 lists seven conditions: the cursor was never opened; four variants of “opened in another program, module, or call that has since ended” (one each for CLOSQLCSR(*ENDPGM), *ENDMOD, *ENDSQL, and *ENDACTGRP); closed by CLOSE, COMMIT, or ROLLBACK; and opened under a different transaction. They fall into four groups. Here they are in plain terms, with what each looks like in real code.

1. The cursor was never opened

The OPEN never ran. Common reasons:

• the OPEN sits inside an IF or a subroutine that was skipped on this path;
• the OPEN ran but failed (for example on a lock or authority error), the program did not check SQLCODE, and it went straight into the FETCH loop;
• the FETCH is in a different module from the DECLARE/OPEN. In IBM’s words the cursor must be opened “in the same program or module call” before it is used.

The second reason is the most common in practice. A failed OPEN leaves no open cursor. The real error is the message just before SQL0501 in the job log, not SQL0501 itself.

2. The cursor was closed by CLOSE, COMMIT, or ROLLBACK

This is the condition people forget. IBM’s SQL0501 cause text names all three statements explicitly, and the IBM i SQL reference pages for each statement spell out the rules (all confirmed, see Sources Consulted):

• CLOSE: obvious, but easy to do twice. For example, a cleanup subroutine closes the cursor and then the main line closes it again, or an error path closes it and the normal exit path closes it a second time.
• COMMIT (without HOLD): closes every open cursor that was not declared WITH HOLD. A cursor declared WITH HOLD keeps its position across the commit. WITHOUT HOLD is the default when you specify neither.
• COMMIT HOLD: per IBM, “currently open cursors are not closed whether they are declared with a HOLD option or not.”
• ROLLBACK (without HOLD): closes every cursor opened during the unit of work, including cursors declared WITH HOLD. IBM: “All cursors that were opened during the unit of work are closed whether they are declared with a HOLD option or not.” WITH HOLD protects you from a commit, not from a rollback.
• ROLLBACK HOLD: keeps open cursors open. IBM notes that the cursor position after a ROLLBACK HOLD is usually back where it was at the start of the unit of work, depending on certain ALWBLK/ALWCPYDTA settings, so do not assume the next FETCH carries on from the last row you read.

The classic SQL0501 program reads a cursor in a loop, updates other rows, and issues COMMIT every N rows to keep its locks short. That is good practice. But if the cursor was declared without WITH HOLD, the first commit closes it and the next FETCH fails with SQL0501.

3. The cursor was closed when its program, module, or activation group ended (CLOSQLCSR)

IBM’s cause text also covers a cursor opened in another program or module call that has since ended under the applicable CLOSQLCSR setting. For example, the 7.6 text reads: “The cursor &1 was opened in another call of this module and the activation group ended between calls. The module was created with CLOSQLCSR(ENDACTGRP).” CLOSQLCSR is the Close SQL cursor* parameter on CRTSQLRPGI (and the other CRTSQLxxx commands), and it can also be set in source with SET OPTION CLOSQLCSR = .... Confirmed values from IBM’s CRTSQLRPGI and SET OPTION documentation:

For ILE SQLRPGLE the realistic choices are *ENDACTGRP and *ENDMOD. A program built with *ENDMOD that opens a cursor on one call and expects to FETCH from it on the next call gets SQL0501, because the cursor was closed when the module was exited. The same symptom can come from *ENDACTGRP combined with ACTGRP(*NEW): every call gets a fresh activation group, and the old one (with its cursors) is destroyed on return. RPGLE Activation Groups Reference confirms that local and remote SQL cursors are among the resources an activation group holds and that they are closed when the group is deleted.

IBM’s recovery text for SQL0501 includes precompiling with CLOSQLCSR(*ENDSQL), *ENDJOB, or *ENDACTGRP. Per the SET OPTION table above, the first two apply to non-ILE programs, so for ILE code the relevant option is *ENDACTGRP combined with an activation group that outlives the call (a named group or *CALLER).

4. The cursor was opened under a different transaction

The last condition in IBM’s cause text is worded: “The cursor &1 was opened under a transaction which is different than the current transaction.” This mostly affects code that switches commitment definitions or connections partway through processing. This article does not go further into that case. UNVERIFIED_SOURCE: none of the IBM i sources consulted gives a worked example of this condition, and the “switches commitment definitions or connections” description is an interpretation, not IBM wording. Check the full SQL0501 cause text on your own system (DSPMSGD RANGE(SQL0501) MSGF(QSQLMSG)) if you suspect it.

Stored procedures returning result sets (ODBC and other clients)

A related IBM i Access ODBC troubleshooting page titled “SQL0501 – Cursor CRSR000x not open” (an IBM i 7.1-era page, see Sources Consulted) describes clients that call an external stored procedure written in ILE embedded SQL and get SQL0501 when they try to read the result set. IBM’s advice there is:

• compile the embedded-SQL program with ACTGRP(*CALLER) rather than *NEW, so the result-set cursor is not destroyed with a private activation group when the program returns;
• end the program with a return rather than an exit;
• if it must exit, use CLOSQLCSR(*ENDACTGRP), because with *ENDMOD the cursor closes before the client reads it;
• make sure CREATE PROCEDURE declares the correct number of result sets.

Release caveat: that page was found under the IBM i 7.1 Knowledge Center path. The behavior follows directly from the CLOSQLCSR and activation group rules above, which are confirmed for 7.5, but no 7.4, 7.5, or 7.6 edition of the ODBC page itself was found.

Cursors closed as a side effect of an earlier error

UNVERIFIED_SOURCE for IBM i: the Db2 for z/OS and Db2 for LUW message references for SQL0501 say that certain earlier severe errors (they list SQLCODEs such as -911, -913, and -952, among others) can force a cursor to close, so the next FETCH reports SQL0501 even though your code never closed it. The IBM i 7.5 and 7.6 SQL0501 entries do not list those SQLCODEs or any “closed by an earlier error” condition. Treat this as plausible but unconfirmed on IBM i. The practical point still holds either way: always read the job log messages before the SQL0501.

How to investigate SQL0501

Step 1: read the job log upward from SQL0501

Use DSPJOBLOG (or WRKJOB OPTION(*JOBLOG); see IBM i Job Log and CPF Messages Reference). Find the SQL0501 entry, press F1 to confirm the cursor name in &1, then read the messages above it. You are looking for:

• an error on the OPEN for that cursor (case 1, the open failed);
• a commit or rollback, or messages from a called program that commits (case 2);
• an earlier SQL error on the same cursor (the side-effect case).

If the job log is at a low logging level, you may not see enough. Rerun with CHGJOB LOG(4 00 *SECLVL) to capture the full sequence.

Step 2: confirm the exact message text on your release

The message text and cause/recovery details above come from IBM’s 7.5 and 7.6 references. To read the authoritative text for your own release:

DSPMSGD RANGE(SQL0501) MSGF(QSYS/QSQLMSG)

IBM’s SQL messages and codes reference confirms that every Db2 for i SQLCODE has a message in message file QSQLMSG, and uses the same DSPMSGD ... MSGF(QSYS/QSQLMSG) form in its own example.

Step 3: map every OPEN, FETCH, CLOSE, COMMIT, and ROLLBACK for that cursor

In the source, list every statement that touches the cursor and every COMMIT/ROLLBACK that can run while it is open, including commits issued inside subprocedures or called programs that share the same commitment definition. For each path through the code, ask:

1. Is the OPEN guaranteed to run, and is its SQLCODE checked, before the first FETCH?
2. Is there a COMMIT inside the loop? If so, is the cursor declared WITH HOLD, or is the commit coded as COMMIT HOLD?
3. Is there any ROLLBACK inside the loop? If so, the cursor will be closed unless it is ROLLBACK HOLD, whatever the DECLARE says.
4. Can CLOSE run twice on any path, for example from an error handler and then from normal cleanup?
5. Are DECLARE, OPEN, and FETCH in the same module?

Step 4: check how the program was compiled

Look at the program’s CLOSQLCSR and activation group settings. DSPPGM shows the activation group attribute. PRTSQLINF prints the SQL information stored in the program, including the precompile options. Also look for a SET OPTION CLOSQLCSR = ... near the top of the source, because it overrides the command default. If the cursor is opened on one call and fetched on a later call, *ENDMOD or ACTGRP(*NEW) is very likely the cause.

Step 5: check SQLCODE after every cursor statement while debugging

If you cannot see the problem from the source, add an SQLCODE/SQLSTATE check (or a temporary log line) after each OPEN, FETCH, CLOSE, COMMIT, and ROLLBACK. The first statement that returns something unexpected is usually the real fault. SQL0501 is often just where the program finally noticed.

A conservative SQLRPGLE example

STATIC CHECKED: this example has not been compiled or run on a live IBM i or PUB400 system. It has been reviewed for syntactic plausibility against IBM’s documented DECLARE CURSOR, COMMIT, and SET OPTION syntax and normal free-form SQLRPGLE conventions, but it is not confirmed to compile or run correctly. Treat it as an illustrative starting point and test it before use.

The pattern does three things that prevent most SQL0501s:

1. declares the cursor WITH HOLD because the loop commits periodically;
2. keeps an explicit cursorOpen flag, so CLOSE never runs twice and FETCH never runs on a cursor that failed to open;
3. treats a ROLLBACK as something that closes the cursor, because it does, WITH HOLD or not.

It deliberately does not “fix” SQL0501 by catching it and reopening the cursor. Reopening silently restarts the result set from the first row and can process rows twice.

**FREE
ctl-opt dftactgrp(*no) actgrp(*caller);

// Must come before any other embedded SQL statement in the source.
exec sql SET OPTION COMMIT = *CHG, CLOSQLCSR = *ENDACTGRP;

dcl-s custNo     packed(7:0);
dcl-s balance    packed(11:2);
dcl-s cursorOpen ind inz(*off);
dcl-s rowsSince  int(10) inz(0);
dcl-c COMMIT_EVERY 500;

// WITH HOLD: a plain COMMIT inside the loop will not close this cursor.
// It does NOT protect against ROLLBACK (see the error path below).
exec sql
  DECLARE C_CUST CURSOR WITH HOLD FOR
    SELECT CUSTNO, BALANCE
      FROM CUSTMAST
     WHERE STATUS = 'A'
     ORDER BY CUSTNO;

exec sql OPEN C_CUST;
if sqlcode <> 0;
  // The OPEN failed. Do not fall through to FETCH, which would report
  // SQL0501 and hide the real error. Log sqlcode/sqlstate here.
  *inlr = *on;
  return;
endif;
cursorOpen = *on;

dow cursorOpen;
  exec sql FETCH NEXT FROM C_CUST INTO :custNo, :balance;

  if sqlcode = 100;            // no more rows (SQLSTATE 02000)
    leave;
  elseif sqlcode < 0;
    // Includes -501 if something closed the cursor unexpectedly.
    // Log sqlcode, sqlstate and sqlerrmc, then stop. Do not reopen
    // and carry on: a reopen restarts at the first row.
    exec sql ROLLBACK;         // closes C_CUST even though WITH HOLD
    cursorOpen = *off;         // record that, so we do not CLOSE it
    leave;
  endif;

  // ... process the row, e.g. UPDATE another table ...

  rowsSince += 1;
  if rowsSince >= COMMIT_EVERY;
    exec sql COMMIT;           // C_CUST stays open because WITH HOLD
    if sqlcode <> 0;
      // Log and stop. Treat the cursor as unusable.
      exec sql ROLLBACK;
      cursorOpen = *off;
      leave;
    endif;
    rowsSince = 0;
  endif;
enddo;

if cursorOpen;
  exec sql CLOSE C_CUST;
  cursorOpen = *off;
  exec sql COMMIT;             // commit the final partial batch
endif;

*inlr = *on;
return;

Points to note about this example:

• COMMIT = *CHG assumes the files are journaled. Without commitment control (COMMIT = *NONE) there is no unit of work to protect, and the COMMIT/ROLLBACK statements serve no purpose. Choose the isolation level to match your application. See Commitment Control with STRCMTCTL.
• CLOSQLCSR = *ENDACTGRP with ACTGRP(*CALLER) matters only if another call needs the cursor to survive. In a single-call program like this one it is harmless, but it shows the combination IBM recommends for result-set procedures.
• An alternative to WITH HOLD is to keep the cursor declared without it and code every in-loop commit as COMMIT HOLD. Both are documented by IBM. WITH HOLD on the DECLARE is easier to review because the intent sits next to the cursor.
• If you add a SAVEPOINT and roll back to it, review the savepoint’s cursor options. The commitment control article above shows ON ROLLBACK RETAIN CURSORS. This article does not cover savepoint cursor behavior in detail.
• Log the raw SQLERRMC and SQLSTATE rather than matching literal message text copied from this or any other article.

Common mistakes

• Committing inside a FETCH loop without WITH HOLD. The most common SQL0501 by far. The first commit closes the cursor and the next FETCH fails.
• Assuming WITH HOLD survives a ROLLBACK. It does not. A plain ROLLBACK closes all cursors opened in the unit of work. Only ROLLBACK HOLD keeps them open, and even then the position may move back.
• Closing a cursor twice. An error handler closes it and the normal cleanup closes it again, and the second CLOSE gets SQL0501. Track the state in a flag, as in the example.
• Not checking SQLCODE after OPEN. The OPEN fails, the program goes into the FETCH loop anyway, and the job log shows SQL0501 as the “error”, even though the real cause is the message just above it.
• Reusing a cursor name across subroutines or subprocedures without reopening. One routine closes C_CUST when it finishes, and another routine later assumes it is still open from earlier. The cursor name is the same, but its state is not. Each routine should either own the cursor’s whole open/fetch/close cycle or check a shared state flag.
• Expecting a cursor to survive between calls under *ENDMOD or ACTGRP(*NEW). A “keep the cursor open between calls” design needs *ENDACTGRP and an activation group that outlives the call.
• Calling a program that commits. A called program sharing your commitment definition can issue a COMMIT or ROLLBACK that closes your non-held cursors. Check what your callees do inside your loop.
• “Fixing” it by catching -501 and reopening. The reopened cursor starts again from the first row. Unless the processing is genuinely idempotent, that creates duplicate updates.

Performance and design considerations

• Commit frequency versus cursor scope. Committing every N rows keeps locks short (which also lowers lock contention for other jobs), but it is exactly what closes non-held cursors. WITH HOLD lets you keep both. IBM documents that a held cursor keeps the locks needed to maintain its position across a commit.
• Positioned update after commit. IBM’s DECLARE CURSOR and COMMIT pages note that after a commit with a held cursor, a FETCH is required before a positioned UPDATE or DELETE. Code that commits and then immediately runs UPDATE ... WHERE CURRENT OF can fail even with WITH HOLD. (This article does not confirm which SQLCODE that case returns.)
• Long-lived cursors across calls. Leaving cursors open between calls (*ENDACTGRP with a persistent activation group) avoids reopen costs but holds resources for as long as the activation group lives. Close cursors explicitly when the work is done rather than relying on implicit close.

A realistic practical use case

A nightly SQLRPGLE batch program reads open orders through a cursor and posts each one to a ledger table. It started out committing once at the end of the run. Then another team complained about long-held locks, so someone added COMMIT every 1,000 rows. That night the job ended after exactly 1,000 orders with SQL0501 on FETCH NEXT FROM C_ORDERS. Nothing else had changed. The cursor had been declared without WITH HOLD, so the first periodic commit closed it. The fix was one clause, DECLARE C_ORDERS CURSOR WITH HOLD FOR ..., plus a review of the error path to confirm that its ROLLBACK marked the cursor as closed and did not try to CLOSE it again.

FAQ

What is the exact IBM i message text for SQL0501?
Per IBM’s IBM i 7.5 Listing of SQL messages: “Cursor &1 not open.”, SQLCODE -501, SQLSTATE 24501. &1 is replaced by your cursor name. Confirm it on your own release with DSPMSGD RANGE(SQL0501) MSGF(QSYS/QSQLMSG).

Does WITH HOLD stop COMMIT from closing my cursor?
Yes. IBM documents that WITH HOLD “prevents the cursor from being closed as a consequence of a commit operation.” Without it, a plain COMMIT closes the cursor.

Does WITH HOLD stop ROLLBACK from closing my cursor?
No. A plain ROLLBACK closes all cursors opened during the unit of work, whether they were declared with a hold option or not. Use ROLLBACK HOLD if you really need the cursor open after a rollback, and expect its position to have moved back.

What is the difference between SQL0501 and SQL0507?
Both say “Cursor &1 not open.” and both use SQLSTATE 24501. SQL0501 (-501) is raised on FETCH or CLOSE. SQL0507 (-507) is raised on a positioned UPDATE or DELETE ... WHERE CURRENT OF.

Why does my stored procedure’s result set give SQL0501 to the client?
Most likely the cursor was closed when the procedure’s program ended, before the client read it. IBM’s ODBC troubleshooting guidance (a 7.1-era page) recommends ACTGRP(*CALLER), ending with a return rather than an exit, and CLOSQLCSR(*ENDACTGRP) rather than *ENDMOD. Note that IBM documents *ENDMOD as the default for SQL procedures, functions, and triggers.

Should I just reopen the cursor when I get -501?
Not as a general fix. A reopened cursor starts again from the beginning of the result set, so unless your processing is safe to repeat, you will process rows twice. Find out which statement closed the cursor and fix that.

Sources Consulted

• WebFetch: https://www.ibm.com/docs/en/i/7.5?topic=codes-listing-sql-messages (IBM Documentation, IBM i 7.5, Listing of SQL messages). Read in four chunks. Confirms: SQL0501 message text “Cursor &1 not open.”, SQLCODE -501, SQLSTATE 24501; the cause conditions (never opened; closed by CLOSE, COMMIT, or ROLLBACK; closed when another program or module call ended under the applicable CLOSQLCSR setting; opened under a different transaction); the recovery options (open in the same program or module call; precompile with CLOSQLCSR *ENDSQL/*ENDJOB/*ENDACTGRP; use HOLD on COMMIT/ROLLBACK). Also confirms SQL0502 (-502, 24502, “Cursor &1 already open or allocated.”) and SQL0507 (-507, 24501, UPDATE/DELETE case). The full cause text was paraphrased by the fetch tool rather than returned word for word, so only the short quoted phrases in this article are verbatim.
• WebFetch (PDF, extracted locally with pdftotext): https://www.ibm.com/docs/en/ssw_ibm_i_76/pdf/rzalapdf.pdf (IBM i 7.6, SQL messages and codes). Confirms verbatim: the full SQL0501 cause text (seven conditions, including the four CLOSQLCSR variants and “opened under a transaction which is different than the current transaction”), recovery text, -501 / 24501; no earlier-error condition listed; every SQLCODE has a message in QSQLMSG. The IBM i 7.3 edition of the same PDF (https://www.ibm.com/docs/en/ssw_ibm_i_74/rzala/rzalapdf.pdf) gives the example DSPMSGD RANGE(SQL0204) MSGF(QSYS/QSQLMSG).
• WebFetch: https://www.ibm.com/docs/en/i/7.5?topic=statements-declare-cursor (IBM i 7.5 SQL reference, DECLARE CURSOR). Confirms: WITHOUT HOLD is the default; WITH HOLD prevents close on commit; all cursors are implicitly closed by a rollback; a FETCH is required before a positioned UPDATE/DELETE after a commit.
• WebFetch: https://www.ibm.com/docs/en/i/7.5?topic=statements-commit (IBM i 7.5, COMMIT). Confirms: COMMIT HOLD keeps all open cursors open; plain COMMIT preserves WITH HOLD cursors and closes the others.
• WebFetch: https://www.ibm.com/docs/en/i/7.5?topic=statements-rollback (IBM i 7.5, ROLLBACK). Confirms: plain ROLLBACK closes all cursors opened in the unit of work, held or not; ROLLBACK HOLD keeps them open, with position usually reset to the start of the unit of work subject to ALWBLK/ALWCPYDTA.
• WebFetch: https://www.ibm.com/docs/en/i/7.5?topic=ssw_ibm_i_75/cl/crtsqlrpgi.html (CRTSQLRPGI command). Confirms: CLOSQLCSR default *ENDACTGRP; meaning of *ENDACTGRP and *ENDMOD.
• WebFetch: https://www.ibm.com/docs/en/i/7.5?topic=statements-set-option (IBM i 7.5, SET OPTION). Confirms: CLOSQLCSR values *ENDACTGRP, *ENDMOD, *ENDPGM, *ENDSQL, *ENDJOB and which apply to non-ILE programs; *ENDMOD default for SQL functions, procedures and triggers; SET OPTION must come before any other embedded SQL statement.
• WebFetch: https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_71/rzaii/rzaiiodbc51.htm (IBM i 7.1, “SQL0501 – Cursor CRSR000x not open”, IBM i Access ODBC). Confirms: ACTGRP(CALLER), return-versus-exit, CLOSQLCSR(ENDACTGRP) and result-set-count guidance for stored procedures. Release caveat: 7.1 page, not re-checked on later releases.
• WebSearch: “IBM i SQL0501 ‘Cursor &1 not open’ SQLSTATE 24501”. Surfaced Db2 for z/OS and Db2 for LUW message pages saying earlier errors (e.g. -911, -913, -952) can force a cursor closed. Does not confirm this for IBM i; marked UNVERIFIED_SOURCE above.

Not verified: IBM i confirmation that earlier severe errors force a cursor closed (the z/OS and LUW claim); a worked IBM i example of the “different transaction” condition; the SQLCODE returned by a positioned UPDATE/DELETE issued after a commit without an intervening FETCH; the exact PRTSQLINF output layout. The SQLRPGLE example is STATIC CHECKED only.

Internal links

• Db2 for i Explained
• DB2 for i SQL Stored Procedures and User-Defined Functions in 2026
• Commitment Control with STRCMTCTL: Commit, Rollback, and Savepoints Across Multiple Files
• RPGLE Activation Groups Reference
• IBM i Activation Groups and Call Stack Explained
• IBM i Job Log and CPF Messages Reference