DB2 SQL Errors Codes and Error Messages and Warnings from Error -503 to -507

SQLServerF1

Error: DB2 SQL Error: SQLCODE=-503, SQLSTATE=42912, SQLERRMC=TBSPACEID=, TABLEID=, COLNO=, DRIVER=
A COLUMN CANNOT BE UPDATED
BECAUSE IT IS NOT IDENTIFIED IN
THE UPDATE CLAUSE OF THE
SELECT STATEMENT OF THE
CURSOR
Explanation: The application program attempted to
update (using a cursor) a value in a column of the
object table that was not identified in the FOR UPDATE
clause in the cursor declaration.
Any column that is to be updated must be identified in
the FOR UPDATE clause of the cursor declaration.
System action: The statement cannot be executed. No
data was updated in the object table.
Programmer response: Correct the application
program. If the column is to be updated, its name must
be added to the FOR UPDATE clause of the cursor
declaration.
SQLSTATE: 42912

Error: DB2 SQL Error: SQLCODE=-504, SQLSTATE=34000, SQLERRMC=TBSPACEID=, TABLEID=, COLNO=, DRIVER=
CURSOR NAME cursor-name IS NOT
DECLARED
Explanation: Cursor cursor-name was referenced in an
SQL statement, and one of the following is true:
v Cursor cursor-name was not declared (using the
DECLARE CURSOR statement) or allocated (using
the ALLOCATE CURSOR statement) in the
application program or SQL routine before it was
referenced.
v Cursor cursor-name was referenced in a positioned
UPDATE or DELETE statement which is not a
supported operation for an allocated cursor.
v Cursor cursor-name was allocated, but a CLOSE
cursor statement naming cursor-name was issued and
deallocated the cursor before this cursor reference.
v Cursor cursor-name was allocated, but a ROLLBACK
operation occurred and deallocated the cursor before
this cursor reference.
v Cursor cursor-name was allocated, but its associated
cursor declared in a stored procedure was not
declared WITH HOLD, and a COMMIT operation
occurred and deallocated the cursor before this
cursor reference. The COMMIT operation can be
either explicit (the COMMIT statement) or implicit
(that is, a stored procedure defined as
COMMIT_ON_RETURN = ‘Y’ was called before this
cursor reference).
v Cursor cursor-name was allocated, but its associated
stored procedure was called again since the cursor
was allocated, new result sets were returned, and
cursor cursor-name was deallocated.
v The declaration of a cursor cursor-name was not in
scope for the reference to a cursor named
cursor-name.
System action: The statement cannot be executed.
Programmer response: Check the application program
or SQL routine for completeness and for a possible
spelling error in the cursor declaration or allocation.
The declaration for or allocation of a cursor must
appear in an application program or SQL routine before
SQL statements that reference the cursor.
If the cursor-name was <UNKNOWN>, the cursor was
not successfully declared or allocated. This error can
occur if SQL(DB2) was used, and a warning message
was issued during precompilation. Check the
precompile output for warning messages on the
DECLARE CURSOR or ALLOCATE CURSOR
statement, and correct the statement.
For an allocated cursor, if an implicit or explicit
COMMIT, ROLLBACK, or CLOSE occurred since the
cursor was successfully allocated, modify the
application program logic to do one of the following
actions:
v After the COMMIT, ROLLBACK, or CLOSE
operation, call the associated stored procedure again,
and reissue the ASSOCIATE LOCATORS and
ALLOCATE CURSOR statements.
v For COMMIT, declare the associated cursor in the
stored procedure WITH HOLD so the COMMIT
operation does not deallocate the cursor.
For an allocated cursor, if the associated stored
procedure was called again and new result sets were
returned since the cursor was allocated, reissue the
ASSOCIATE LOCATORS and ALLOCATE CURSOR
statements.
SQLSTATE: 34000

Error: DB2 SQL Error: SQLCODE=-507, SQLSTATE=24501, SQLERRMC=TBSPACEID=, TABLEID=, COLNO=, DRIVER=
THE CURSOR IDENTIFIED IN THE
UPDATE OR DELETE STATEMENT IS
NOT OPEN
Explanation: The application program attempted to
execute an UPDATE or DELETE WHERE CURRENT
OF cursor statement at a time when the specified
cursor was not open.
System action: The statement cannot be executed. No
update or delete was performed.
Programmer response: Check for a previous SQL
return code that might have closed the cursor.
SQLCODES -404, -652, -679,-802, -901, -904, -909, -910,
-911, -913, and -952 may force the cursor to close. After
the cursor is closed, any fetches or close cursor
statements receive SQLCODE -501. Any updates or
deletes receive this SQLCODE -507. Correct the logic of
the application program to ensure that the specified
cursor is open at the time the UPDATE or DELETE
statement is executed.
SQLSTATE: 24501

Above are list of DB2 SQL Errors and Warnings from Error -503 to -507 received while performing certain operation against DB2 Database or related products.

SQLCODE – Regardless of whether the application program provides an SQLCA or a stand-alone variable, SQLCODE is set by DB2 after each SQL statement is
executed. DB2 conforms to the ISO/ANSI SQL standard as follows:
If SQLCODE = 0, execution was successful.
If SQLCODE &gt; 0, execution was successful with a warning.
If SQLCODE &lt; 0, execution was not successful.
SQLCODE = 100, “no data” was found. For example, a FETCH statement returned no data because the cursor was positioned after the last row of the result table.

SQLSTATE – SQLSTATE is also set by DB2 after the execution of each SQL statement. Thus, application programs can check the execution of SQL statements by testing SQLSTATE instead of SQLCODE.

Hope this was helpful.

Thanks,
SQLServerF1 Team
Information about DB2 SQL Error Codes and Error Messages on Windows, Linux and Z/OS Operating Systems.

 

Leave a Reply

Your email address will not be published. Required fields are marked *