About SQLCODEs

The SQL Communication Area (SQLCA) is a data structure that provides information about the success or failure of requested SQL statements. When Db2 processes an SQL statement in a program, it lists return codes in the SQLCODE and SQLSTATE host variables or corresponding fields of the SQLCA.

These return codes that indicate whether or not SQL statements executed successfully or not. You can use these SQLCODE return codes to diagnose potential problems with your SQL:

SQLCODE = 0
SQL execution was successful.
SQLCODE > 0
SQL execution was successful with a warning.
SQLCODE < 0
SQL execution was not successful. SQLCODEs yield information about SQL execution errors. Use Db2 Query Monitor Main Menu option 2. View SQLCODEs to view information about SQLCODEs.
SQLCODE = 100
For SPUFI executions, an SQLCODE of +100 indicates a successful execution of the SQL in the SPUFI statement. For non-SPUFI executions, an SQLCODE of +100 indicates no data rows were found to satisfy the SQL statement.
You can use Db2 Query Monitor to view the expanded text description for an SQLCODE that is supplied by the IBM® utility program DSNTIAR. The amount of data that is captured and displayed for an SQL statement is controlled by the MAX_SQLCODES and MAX_SQLCODE_DETAIL parameters in the CQMPARMS data set. These parameters describe how many SQLCODEs are to be collected and how many occurrences of each unique code are to be retained for viewing.
Notes:
  • The collection of SQLCODES is not controlled by the use of monitoring profiles unless the DISABLE parameter is specified. If the DISABLE parameter is specified for a monitoring profile, then SQLCODE information is not collected for the specified workload.
  • The data provided under the menu options 1. View Activity Summaries and 3. View Current Activity are not directly related to the data provided through main menu option 2. View SQLCODES. Data provided under menu options 1. View Activity Summaries and 3. View Current Activity are influenced by the monitoring profile that is being used. In contrast, the data provided under main menu option 2. View SQLCODES are not controlled by the use of monitoring profiles. Therefore, the number of SQLCODES might differ between option 2. View SQLCODES and options 1. View Activity Summaries and 3. View Current Activity.
  • Db2 Query Monitor does not collect the values of host variable data for FETCH statements.
  • Db2 Query Monitor captures information of SQLCODEs with a few exceptions such as -802 and -820. In some cases, a -905 SQLCODE might not be captured for Call statements executed in a Stored Procedure. Db2 Query Monitor only reports the SQLCODEs that are returned at the completion of the SQL call. If a stored procedure receives an ABEND X'33E' or similar ABEND code, Db2 might not return the -905 SQLCODE at the end of the SQL call, at the time of SQLCODE collection.
Query Monitor operates ‘inline’ with SQL related execution that takes place on the host. Certain database processing activities engage ‘off-host’ components, which are called ‘distributed’ or DRDA applications. Once a transaction moves ‘off-host’, Query Monitor will be unable to correlate the end of a transaction to the start until the transaction returns to the host. For example, when an accelerator takes over the transaction, it appears to Db2 z/OS as being remote or not part of the engine. In such cases,
  • SQLCODE may be returned to the originating transaction, but only after the remote process (and not Db2) has populated the SQLCA of the application.
  • Query Monitor is not posted about the success or failure of the transaction, so it appears to ‘lose’ the SQLCODE and not be inline with the second part of the transaction.
There is no definitive list of the SQLCODE that falls into this category. But users should be aware that any discrepancies between the codes presented on the appliance ‘console’ and in Query Monitor may fall directly into this category.