SQLCODEs and SQLSTATEs

 

Program interfaces using SQL return error information to the application program when an error occurs. SQLSTATEs and their corresponding SQLCODEs are returned to the application program in either the SQL communication area (SQLCA) or the SQL diagnostic area.

An SQLCA is a collection of variables in a control block in space provided by the application that is updated by the database management system with information about the SQL statement most recently run. An SQL diagnostic area is a more complex storage area in space provided by the database manager that is designed to communicate more extensive information about the SQL statement most recently run.

When an SQL error is detected, a five-character global variable called the SQLSTATE identifies the nature of the error. In addition to the SQLSTATE, an integer SQLCODE is also available. However, the SQLCODE does not return the same return code for the same error condition among the current four IBM^® relational database products. SQLSTATE has been designed so that application programs can test for specific error conditions or classes of errors regardless of which DB2^® product the application is connected to.

If SQL encounters a hard error while processing a statement, the SQLCODE is a negative number (for example, SQLCODE -204). If SQL encounters an exceptional but valid condition (warning) while processing a statement, the SQLCODE is a positive number (for example, SQLCODE +100). If SQL encounters no error or exceptional condition while processing a statement, the SQLCODE is 0. Every DB2 Universal Database™ for iSeries™ SQLCODE has a corresponding message in message file QSQLMSG in library QSYS. For example, SQLCODE -204 is logged as message ID SQL0204.

Because the returned error information is a valuable problem-diagnosis tool, it is a good idea to include in your application programs the instructions necessary to display some of the information contained in either the returned SQLCA or SQL diagnostic area. The message tokens discussed here are also very valuable for problem diagnosis:

• SQLSTATE

  Return code.

• SQLCODE (SQLCA) or DB2_RETURNED_SQLCODE (SQL diagnostic area)

  Return code.

• SQLERRD(3) (SQLCA) or ROW_COUNT (SQL diagnostic area)

  The number of rows updated, inserted, or deleted by SQL.

The complete message can be viewed online by using the Display Message Description (DSPMSGD) command.

• Distributed relational database SQLCODEs and SQLSTATEs
  The list provides some of the common SQLCODEs and SQLSTATEs associated with distributed relational database processing.

 

Parent topic:

Application problems

 

Related concepts

SQL messages and codes

 

Related reference

SQL reference
Display Message Description (DSPMSGD) command