SQLCODE & SQLSTATE

The SQLCODE and SQLSTATE variables can be used for error handling. They will be set automatically after each statement. The variable(s) have to be declare in the procedure body. SQLCODE is an integer value – SQLSTATE is of type character:

 DECLARE SQLCODE INTEGER DEFAULT 0;
 DECLARE SQLSTATE CHAR(5) DEFAULT ‘00000’;

SQLCODE can be have different values:

* SQLCODE = 0 ... last statement was successful
* SQLCODE > 0 ... last statement was successful with a warning
* SQLCODE < 0 ... last statement was unsuccessful
* SQLCODE = 100 ...last statement was successful, but no data was found on the SELECT/FETCH, INSERT, UPDATE, or DELETE statement

Similar with SQLSTATE:

* '00xxx' ... last statement was successful
* '01xxx' ... last statement was successful with a warning
* '02xxx' ... last statement was successful, but no data was found on the SELECT/FETCH, INSERT, UPDATE, or DELETE statement
* all other states ... last statement was unsuccessful

Please notify that SQLCODE and SQLSTATE can only be accessed by the first statment after each operation. If you need the values later you’ve to use an other variable to store this value.
It’s recommended to use the SQLSTATE, because the values are standardized across the DB2 family and across a lot of other database management systems.
Example:

 ...coming soon...

Link to the DB2 Information Center: DB2 9.7 DB2 10.1 DB2 10.5