Structured Query Language (SQL) return codes, and the tokens referred to in the explanations, are returned in the SQL communication area (SQLCA).
The SQLCA is an area in the application program (defined by the application program) for the use of DB2. It is described in the Appendices of SQL Reference. Additional information about the SQLCA can be found in Section 5 of Diagnosis Guide and Reference.
The SQL return code is returned in the SQLCODE field of the SQLCA. The tokens are returned in the SQLERRM field. If there are several tokens, they appear sequentially in SQLERRM in the order in which they appear in the message text. The tokens are separated by the character X'FF'.
A token appears in the message text in lowercase letters. When a SQL return code is returned through SPUFI, the tokens have been substituted into the message text and the text is displayed. The substitution of tokens into message text is performed by a DB2 module named DSNTIAR. This module can also be used by application programs. Refer to Section 3 of Application Programming and SQL Guide for more information about DSNTIAR.
An application program containing executable SQL statements must either provide a structure named SQLCA or a stand-alone integer variable named SQLCODE (SQLCOD in FORTRAN). An SQLCA may be provided by using the INCLUDE SQLCA statement. INCLUDE SQLCA must not be used if a stand-alone SQLCODE is provided.
The SQLCA includes an integer variable named SQLCODE. The option of providing a stand-alone SQLCODE instead of a SQLCA allows for conformance with the ISO/ANSI SQL standard. The use of this option must be indicated to the precompiler.
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:
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. SQLSTATE (SQLSTT in FORTRAN) is a character string variable in the SQLCA.
SQLSTATE provides application programs with common codes for common error conditions (the values of SQLSTATE are product-specific only if the error or warning is product-specific). Furthermore, SQLSTATE is designed so that application programs can test for specific errors or classes of errors. The coding scheme is the same for all database managers and is consistent with the proposed ISO/ANSI SQL2 standard.
Explanation: Unqualified successful execution or successful execution with one or more warnings. If SQLWARN0 is blank, there are no warnings. If SQLWARN0 = 'W', at least one of the other warning indicators in the SQLCA has been set to indicate a warning condition. For example, SQLWARN1 is used to indicate that a value of a string column was truncated when assigned to a host variable.
SQLSTATE: 00000 for unqualified successful execution
SQLSTATE: 01ddd for successful execution with warning 'ddd'. ;.