SQL codes

When DB2® executes an SQL statement, it returns information about the statement execution. This information includes the SQL return code (SQLCODE) and the SQLSTATE, which indicate whether statement execution was successful.

An application program that contains executable SQL statements must do one of the following things:

  • Declare SQLCODE and SQLSTATE (SQLCOD and SQLSTA in Fortran) as stand-alone host variables. If you specify the STDSQL(YES) precompiler or SQL statement coprocessor option, these host variables receive the return codes, and you should not include an SQLCA in your program.
  • Provide a structure named SQLCA. The SQLCA includes the SQLCODE and SQLSTATE, in addition to other status information. You can provide an SQLCA by using the INCLUDE SQLCA statement. You can call the DSNTIAR sample subroutine from your application program to format the contents of the SQLCA, or you can retrieve fields from the SQLCA yourself.

In this section, the italicized phrases in the SQL return code message text corresponds to the tokens that are returned in the SQLERRMC field of the SQLCA. If DB2 returns several tokens, they appear sequentially in SQLERRMC in the order that they appear in the message text.

In some cases a token is not applicable or available for a specific instance of an error, and *N is returned instead.

SQLCODE

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:

  • If SQLCODE = 0, execution was successful.
  • If SQLCODE > 0, execution was successful with a warning.
  • If SQLCODE < 0, execution was not successful.
  • SQLCODE = 100, "no data" was found. For example, a FETCH statement returned no data because the cursor was positioned after the last row of the result table.

SQLSTATE

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 5-byte 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 IBM® relational database products.