SQL Return Codes
Encyclopedia
SQL Return Codes are used on a day to day basis for the diagnosis of programming failures as a result of SQL
calls by DB2
computer programs. An important feature of DB2
programs is the error processing. The error diagnostic containing the SQL Return Code is held in the field SQLCODE within the DB2 SQLCA block.
program to return error information to the application program. This information in the SQLCA and the SQLCODE field is updated after every API
call for the SQL statement...
0 means successful execution.
Negative means unsuccessful with an error.
An example is -911 which means a timeout has occurred with a rollback
.
Positive means successful execution with a warning.
An example is +100 which means no rows found.
Here is a more comprehensive list of the SQLCODEs for DB2:
-010 THE string constant beginning with string is not terminated properly.
-029 INTO Clause required.
-060 INVALID type SPECIFICATION : spec
-084 Unacceptable SQL statement.
-101 The statement is too long or too complex.
-102 String constant is too long.
-117 The number of values in the INSERT does not match the number of columns.
-180 Bad data in Date/Time/Timestamp.
-181 Bad data in Date/Time/Timestamp.
-199 Illegal use of the specified keyword.
-204 Object not defined to DB2.
-205 Column name not in table.
-206 Column does not exist in any table of the SELECT.
-216 Not the same number of expressions on both sides of the comparison in a SELECT .
-224 FETCH cannot make an INSENSITIVE cursor SENSITIVE.
-229 The locale specified in a SET LOCALE statement was not found.
-305 Null indicator needed.
-311 Varchar, insert or update. -LEN field with the right data length not set.
-482 The procedure returned no locators.
-501 Cursor not open on FETCH.
-502 Opening cursor that is already open.
-503 Updating column needs to be specified.
-530 Referential integrity preventing the INSERT/UPDATE
-532 Referential integrity (DELETE RESTRICT rule) preventing the DELETE.
-536 Referential integrity (DELETE RESTRICT rule) preventing the DELETE.
-545 Check constraint preventing the INSERT/UPDATE.
-551 Authorization failure.
-602 Too many columns specified in a create index.
-747 The table is not available.
-803 Duplicate key on insert or update.
-805 DBRM or package not found in plan.
-811 More than one row retrieved in SELECT INTO.
-818 Plan and program: timestamp mismatch.
-904 Unavailable resource. Someone else is locking your data.
-911 Deadlock or timeout. Rollback has been done.
-913 Deadlock or timeout. No rollback.
-922 Authorization needed.
-927 The language interface was called but no connection had been made.
-936
-1741
-20000
-30090 Remote operation invalid for application execution environment.
+222 Trying to fetch a row within a DELETE statement.
+223 Trying to fetch a row within an UPDATE statement.
+231 FETCH after a BEFORE or AFTER but not on a valid row.
+304 Value cannot be assigned to this host variable because it is out of range.
+802 The null indicator was set to -2 as an arithmetic statement didn't work.
SQL
SQL is a programming language designed for managing data in relational database management systems ....
calls by DB2
IBM DB2
The IBM DB2 Enterprise Server Edition is a relational model database server developed by IBM. It primarily runs on Unix , Linux, IBM i , z/OS and Windows servers. DB2 also powers the different IBM InfoSphere Warehouse editions...
computer programs. An important feature of DB2
IBM DB2
The IBM DB2 Enterprise Server Edition is a relational model database server developed by IBM. It primarily runs on Unix , Linux, IBM i , z/OS and Windows servers. DB2 also powers the different IBM InfoSphere Warehouse editions...
programs is the error processing. The error diagnostic containing the SQL Return Code is held in the field SQLCODE within the DB2 SQLCA block.
SQLCA
The SQL communications area (SQLCA) structure is used within the DB2IBM DB2
The IBM DB2 Enterprise Server Edition is a relational model database server developed by IBM. It primarily runs on Unix , Linux, IBM i , z/OS and Windows servers. DB2 also powers the different IBM InfoSphere Warehouse editions...
program to return error information to the application program. This information in the SQLCA and the SQLCODE field is updated after every API
Application programming interface
An application programming interface is a source code based specification intended to be used as an interface by software components to communicate with each other...
call for the SQL statement...
SQLCODE
The SQLCODE field contains the SQL return code. The code can be zero (0), negative or positive.0 means successful execution.
Negative means unsuccessful with an error.
An example is -911 which means a timeout has occurred with a rollback
Rollback
In political science, rollback is the strategy of forcing change in the major policies of a state, usually by replacing its ruling regime. It contrasts with containment, which means preventing the expansion of that state; and with détente, which means a working relationship with that state...
.
Positive means successful execution with a warning.
An example is +100 which means no rows found.
Here is a more comprehensive list of the SQLCODEs for DB2:
Negative values (Errors)
-007 The specified 'character' is not a valid character in SQL statements.-010 THE string constant beginning with string is not terminated properly.
-029 INTO Clause required.
-060 INVALID type SPECIFICATION : spec
-084 Unacceptable SQL statement.
-101 The statement is too long or too complex.
-102 String constant is too long.
-117 The number of values in the INSERT does not match the number of columns.
-180 Bad data in Date/Time/Timestamp.
-181 Bad data in Date/Time/Timestamp.
-199 Illegal use of the specified keyword.
-204 Object not defined to DB2.
-205 Column name not in table.
-206 Column does not exist in any table of the SELECT.
-216 Not the same number of expressions on both sides of the comparison in a SELECT .
-224 FETCH cannot make an INSENSITIVE cursor SENSITIVE.
-229 The locale specified in a SET LOCALE statement was not found.
-305 Null indicator needed.
-311 Varchar, insert or update. -LEN field with the right data length not set.
-482 The procedure returned no locators.
-501 Cursor not open on FETCH.
-502 Opening cursor that is already open.
-503 Updating column needs to be specified.
-530 Referential integrity preventing the INSERT/UPDATE
-532 Referential integrity (DELETE RESTRICT rule) preventing the DELETE.
-536 Referential integrity (DELETE RESTRICT rule) preventing the DELETE.
-545 Check constraint preventing the INSERT/UPDATE.
-551 Authorization failure.
-602 Too many columns specified in a create index.
-747 The table is not available.
-803 Duplicate key on insert or update.
-805 DBRM or package not found in plan.
-811 More than one row retrieved in SELECT INTO.
-818 Plan and program: timestamp mismatch.
-904 Unavailable resource. Someone else is locking your data.
-911 Deadlock or timeout. Rollback has been done.
-913 Deadlock or timeout. No rollback.
-922 Authorization needed.
-927 The language interface was called but no connection had been made.
-936
-1741
-20000
-30090 Remote operation invalid for application execution environment.
Positive Values (Warnings)
+100 Row not found or end of cursor.+222 Trying to fetch a row within a DELETE statement.
+223 Trying to fetch a row within an UPDATE statement.
+231 FETCH after a BEFORE or AFTER but not on a valid row.
+304 Value cannot be assigned to this host variable because it is out of range.
+802 The null indicator was set to -2 as an arithmetic statement didn't work.