MainframeKit

-504

SQLSTATE 34000NEGATIVE SQLCODE

The statement names a cursor Db2 does not know at that point: not declared, not yet allocated, already deallocated, or out of scope.

MOST LIKELY

A typo in the cursor name, or a DECLARE that sits after the first use in the source.

FIRST CHECK

Check the cursor name spelling in every statement against the DECLARE.

TRY IT ▸

MainframeKit assistant

What it means

Every cursor must be declared (or, for a stored procedure result set, allocated with ALLOCATE CURSOR) before any statement uses it. If the cursor named in OPEN, FETCH, CLOSE or a positioned UPDATE or DELETE is not available, Db2 returns -504 with SQLSTATE 34000 and does not run the statement.

Allocated cursors have extra rules. They cannot be used for positioned UPDATE or DELETE, and a COMMIT, ROLLBACK or CLOSE after the ALLOCATE can make them unusable. If the message shows the name as <UNKNOWN>, look for precompiler warnings on the DECLARE or ALLOCATE statement.

Typical causes

  • A typo in the cursor name, or a DECLARE that sits after the first use in the source.
  • The DECLARE CURSOR statement had a precompiler warning and was not processed.
  • A result-set cursor used after a COMMIT, ROLLBACK or CLOSE ended its allocation.
  • A positioned UPDATE or DELETE attempted on an allocated cursor.
  • The cursor is referenced outside the scope where it was declared.

How to fix it

  1. Check the cursor name spelling in every statement against the DECLARE.
  2. Make sure the DECLARE appears in the source before the cursor is first used.
  3. Read the precompiler listing for warnings on the DECLARE or ALLOCATE CURSOR and fix them.
  4. For stored procedure result sets: after a COMMIT, ROLLBACK or CLOSE, call the procedure again and reissue ASSOCIATE LOCATORS and ALLOCATE CURSOR.
  5. To keep a result set across COMMIT, declare the cursor WITH HOLD inside the stored procedure.