DB2 SQL return codes

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.

SQLCA

The SQL communications area (SQLCA) structure is used within the DB2 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...

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.

  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:

Zero (Successful)

  0    Successful      

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.

References

• "DB2 Version 9.1 for z/OS Codes". June 2010. http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db29.doc.codes/dsnc1k17.pdf?noframes=true. 
• "Tutorial on SQLCODES and Their Causes". July 2011. http://theamericanprogrammer.com/programming/sqlcodes.shtml.