SQLCODE -514: Cursor not in a prepared state

What it means

A cursor for dynamic SQL is declared against a statement name, as in DECLARE C1 CURSOR FOR STMT1. The SELECT itself arrives at run time, when the program issues PREPARE STMT1 FROM :SQL-TEXT. Db2 can only open C1 while STMT1 holds a successfully prepared SELECT. If it holds nothing, OPEN fails with -514 and the cursor stays closed. The token in the message is the cursor name.

Prepared statements don't last for the whole run. By default a COMMIT or ROLLBACK destroys them. A cursor declared WITH HOLD keeps its prepared statement across a COMMIT, and a package bound with KEEPDYNAMIC(YES) keeps prepared statements across commits in general. Without either, a statement prepared before a commit has to be prepared again after it.

A prepared statement also belongs to the package that prepared it. If one program prepares STMT1 and a different program, with its own package, opens a cursor on STMT1, the second program finds nothing prepared under that name.

Common causes

• The PREPARE failed, for example with a -104 syntax error or a -204 undefined table, and the program went on to OPEN without checking SQLCODE. This is the most common case by far.
• The program commits between the PREPARE and the OPEN, or closes the cursor, commits, and reopens it later expecting the old prepared statement to still exist.
• A ROLLBACK discarded the statement. That includes the rollback Db2 does for a -911 deadlock or timeout.
• The code path that runs PREPARE was skipped, for example logic that only prepares on the first pass through a loop.
• The PREPARE ran in a different program, and so a different package, from the one that declares and opens the cursor.

How to find the problem

1. Find the PREPARE for the statement named in the DECLARE CURSOR and look at the SQLCODE it returned. If the program doesn't log it, add a display of SQLCODE and SQLERRMC straight after the PREPARE. A negative code there is your real error.
2. Follow the order of statements in the run, and look for any COMMIT or ROLLBACK between the PREPARE and the OPEN. Check called subprograms too. In CICS an EXEC CICS SYNCPOINT commits Db2 work, and in IMS a checkpoint call does the same.
3. Check whether the cursor is declared WITH HOLD in the source.
4. Check how the package was bound with SELECT COLLID, NAME, KEEPDYNAMIC FROM SYSIBM.SYSPACKAGE WHERE NAME = 'PAYRPT01'.
5. If the PREPARE and the OPEN sit in different modules, check that both are precompiled into the same DBRM.

How to fix it

• Test SQLCODE after every PREPARE and stop or report on a negative value. The underlying error then shows up instead of -514 further down.
• Prepare the statement again after each COMMIT or ROLLBACK, before you open the cursor.
• Declare the cursor WITH HOLD if it must stay open across commits.
• If the program reuses prepared statements across commits on purpose, bind the package with KEEPDYNAMIC(YES). Agree it with the DBA, because kept statements use storage in the subsystem.
• Keep the PREPARE, the DECLARE CURSOR and the OPEN in the same program.

What it looks like

PAYRPT01 - OPEN CURSOR C1 FAILED
DSNT408I SQLCODE = -514, ERROR:  THE CURSOR C1 IS NOT IN A PREPARED STATE
DSNT418I SQLSTATE   = 26501 SQLSTATE RETURN CODE

Technical reviewer: Albert Joseph, WND Software.

Last reviewed 2026-10-18 by the WND Software team. Written in our own words. For the full reference, check IBM’s Db2 for z/OS documentation for your release.