SQLCODE and SQLSTATE concepts
This reference provides a list of SQLCODEs and their associated SQLSTATEs. In this reference, you can find instructions for finding a SQLCODE in the message file along with the text for these messages.
SQLCODEs and SQLSTATEs are returned in the SQLCA structure or through the GET DIAGNOSTICS statement. SQLSTATE is the preferred standard return code. It provides application programs with return codes for common error conditions found among the DB2® products. SQLSTATEs are particularly useful when handling errors in distributed SQL applications.
SQLCODE
An SQLCODE is a return code. The return code is sent by the database manager after completion of each SQL statement.
Each SQLCODE that is recognized by a Db2 for i application server has a corresponding message in the message file QSQLMSG. The message identifier for any SQLCODE is constructed by appending the absolute value (5 digits) of the SQLCODE to SQ and changing the third character to L if the first character of the SQLCODE is 0. For example, if the SQLCODE is 30070, the message identifier is SQ30070. If the SQLCODE is -0204, the message identifier is SQL0204. Lastly, if the SQLCODE is a 3-digit positive number, a zero is added before the first digit. For example, if the SQLCODE is 551, the message identifier is SQL0551.
SQLSTATE
SQLSTATE provides application programs with common return codes for success, warning, and error conditions that are found among the DB2 products. SQLSTATE values are particularly useful when handling errors in distributed SQL applications. SQLSTATE values are consistent with the SQLSTATE specifications that are contained in the SQL 1999 standard.
- The last five bytes of the SQLCA
- A stand-alone SQLSTATE variable
- The GET DIAGNOSTICS statement
SQLSTATE values are designed so that application programs can test for specific conditions or classes of conditions.
SQLSTATE values are comprised of a two-character class code value, followed by a three-character subclass code value. Class code values represent classes of successful and unsuccessful completion conditions. If you want to use SQLSTATE as the basis of your application's return codes, you can define your own SQLSTATE classes or subclasses using the following guidelines:
- SQLSTATE classes that begin with the characters 7 through 9 or I through Z can be defined. Within these classes, any subclass can be defined.
- SQLSTATE classes that begin with the characters 0 through 6 or A through H are reserved for the database manager. Within these classes, subclasses that begin with the characters 0 through H are reserved for the database manager. Subclasses that begin with the characters I through Z can be defined.
The class code of an SQLSTATE value indicates whether the SQL statement was executed successfully (class codes 00 and 01) or unsuccessfully (all other class codes).
SQLSTATE is related to SQLCODE. Every SQLSTATE has one or more SQLCODEs associated with it. An SQLSTATE can refer to more than one SQLCODE.
SQLSTATEs returned from application servers that are not Db2 for i
When an SQLSTATE other than 00000 is returned from an application server that is not Db2 for i, Db2 for i attempts to map the SQLSTATE to a Db2 for i SQLCODE and message with the following results:
- If the SQLSTATE is not recognized by Db2 for i, the common message for the class is issued.
- If the SQLSTATE and SQLCODE correspond to
a single Db2 for
i SQLCODE, Db2 for
i attempts to convert
the returned tokens into the replacement data that the SQL message
expects. If an error occurs while converting the tokens, the following
results occur:
- The tokens are returned without conversion in the SQLERRM field of the SQLCA or in the SQL diagnostics area.
- A common message for the class code of the SQLSTATE is issued.