SQLCODE -084: Unacceptable SQL statement
SQLCODE -084 (SQLSTATE 42612) means Db2 won't accept the statement in the way it was issued. Most often a program or tool tried to PREPARE or EXECUTE IMMEDIATE a statement that can only be embedded in a program, such as SELECT INTO or FETCH.
Other names for this code
Also written as SQLCODE -84, SQL -084, SQLCODE=-84, SQLCODE = -084, db2 -084, sqlstate 42612, SQL0084N, unacceptable SQL statement.
What it means
Db2 has two ways to run SQL. Static SQL is written into the program, precompiled and bound. Dynamic SQL is built as a string at run time and passed to PREPARE or EXECUTE IMMEDIATE. Most statements work either way, but some don't. Statements that deal with host variables or with the dynamic SQL process itself, such as SELECT INTO, DECLARE CURSOR, OPEN, FETCH, CLOSE, PREPARE, INCLUDE and WHENEVER, can only be embedded. Passing one of them to PREPARE or EXECUTE IMMEDIATE gets -084.
IBM lists a few other cases under the same code. The program contains an embedded statement that Db2 for z/OS doesn't support, such as one written for another database product. A statement refers to a cursor that was never declared. Or a PREPARE of an ALLOCATE CURSOR statement reuses a statement name that is already tied to a declared cursor.
SPUFI, DSNTEP2, QMF and JDBC or ODBC tools all run SQL dynamically. That is why a statement copied out of a COBOL program often fails with -084 when someone tries it in one of those tools.
Common causes
- A SELECT ... INTO :host-variable copied from a program into SPUFI or another query tool. Query tools need a plain SELECT.
- A program that builds a statement string containing FETCH, OPEN, CLOSE or DECLARE CURSOR and passes it to PREPARE or EXECUTE IMMEDIATE.
- An embedded statement Db2 for z/OS doesn't support, often in code moved from another platform.
- A statement that names a cursor the program never declared, for example after a DECLARE was removed or renamed.
- An ALLOCATE CURSOR statement prepared under a statement name that a declared cursor already uses.
How to find the problem
- Find the statement that failed. In SPUFI output it is printed just above the
DSNT408Imessage. In a program, display the string passed to PREPARE or EXECUTE IMMEDIATE. - Check the first keyword of that statement. If it is SELECT with an INTO clause, or one of the cursor statements, it can't be prepared.
- For an embedded statement, look at the precompile listing for warnings on that statement number.
- Search the program for the DECLARE CURSOR of any cursor the failing statement names.
How to fix it
- In a query tool, remove the INTO clause and run the SELECT on its own.
- In a program, prepare only the SELECT text. Then use DECLARE CURSOR for the prepared statement name, followed by OPEN, FETCH and CLOSE as embedded statements.
- For a single-row result in dynamic SQL, prepare the SELECT and fetch one row through a cursor.
- Replace statements from other products with the Db2 for z/OS equivalent, and precompile again.
- Add or correct the DECLARE CURSOR so every cursor the program uses is declared before it's referenced.
What it looks like
DSNT408I SQLCODE = -084, ERROR: UNACCEPTABLE SQL STATEMENT DSNT418I SQLSTATE = 42612 SQLSTATE RETURN CODE
IBM documentation
IBM Db2 for z/OS documentation. Use the documentation for your installed release.
This link opens the relevant IBM reference library or lookup instructions. Match the complete code and your installed product release; it is not a code-specific review.
Related codes
Questions
Why does SELECT INTO work in my program but not in SPUFI?
SELECT INTO can only be embedded, because it needs host variables to put the result in. SPUFI runs SQL dynamically, so drop the INTO clause there.
Which statements can't be prepared?
Statements that work with host variables or drive dynamic SQL, including SELECT INTO, DECLARE CURSOR, OPEN, FETCH, CLOSE, PREPARE, EXECUTE, INCLUDE and WHENEVER. The SQL Reference lists which statements can be prepared.
Does Db2 for LUW use the same code?
SQL0084N has the same SQLSTATE, 42612, but on LUW the message is narrower. It describes an EXECUTE IMMEDIATE that contains a SELECT or VALUES statement.