UL/DB2 error codes and messages

This topic describes how to interpret the return codes and messages returned by the User Language/DATABASE 2 Interface.

UL/DB2 error processing

When a UL/DB2 statement executes, UL/DB2 sets status information that indicates whether the statement completed normally. UL/DB2 communicates this status information through the User Language $STATUS and $STATUSD functions. UL/DB2 communicates the error message associated with an abnormal status code through the $ERRMSG function for Call Attach Facility (CAF) calls, and the $DB2EMSG function for SQL statements.

Using tracing

UL/DB2 error processing is handled by CAF. If you allocate a DSNTRACE data set for the job, CAF sends diagnostic messages to the DSNTRACE data set.

Typically, you do not want to turn DSNTRACE on. If you turn DSNTRACE on and have multiple users, you might get a S013E ABEND. This is an IBM problem that is caused by DSNTRACE not being able to work with multiple tasks. There is an APAR -- a suggestion for IBM to fix the problem.

Turn DSNTRACE on only when debugging in a single user environment. To turn DSNTRACE on, add a DD card to the startup JCL.

See the IBM DATABASE 2 Call Attach Facility User's Guide and Reference for more information.

Errors involving Call Attach Facility calls

This section describes the diagnostic information returned by UL/DB2 when there is an error that involves a CAF call. The CAF calls that you can use in a User Language procedure are:

• CONNECT TO
• DISCONNECT FROM

$STATUS and $STATUSD

When there is an error involving a CAF call, CAF places a return code in R15, and a reason code in R0. $STATUS retrieves the return code, the contents of R15, after each CAF call. A value of one ($STATUS=1) or greater indicates that an error occurred and identifies the error as belonging to a particular category.

If the return code does not equal zero ($STATUS NE 0), then $STATUSD retrieves the reason code (the contents of R0). $STATUSD indicates the specific error condition within a particular $STATUS category.

Common return and reason codes lists some of the more common return and reason codes. You can find a complete list of reason and return codes in the IBM DATABASE 2 Call Attach Facility User's Guide and Reference, and the IBM DATABASE 2 Messages and Codes Manual.

If the return code is equal to zero ($STATUS = 0), $STATUSD returns unpredictable codes.

$ERRMSG

Use the User Language $ERRMSG function to retrieve the error message for an error with a CAF call, that is, CONNECT TO or DISCONNECT FROM. The string returned by $ERRMSG can be up to 80 characters long.

Common return and reason codes

Example

The following code fragment shows how you can test the value of $STATUS after a CAF call:

EXEC DB2 CONNECT TO BOSTON END EXEC IF $STATUS NE 0 THEN PRINT ' $STATUS=' AND $STATUS PRINT '$STATUSD=' AND $STATUSD PRINT $ERRMSG JUMP TO FINI: END IF

Errors involving SQL statements

This section describes the diagnostic information returned by UL/DB2 when there is an error involving an SQL statement.

Using the $STATUS and $STATUSD functions

After invoking an SQL statement, $STATUS indicates whether an error occurred processing the statement. If no errors occurred, $STATUS is equal to zero. If an error occurred, $STATUS is not equal to zero and the value of STATUSD contains more specific information about the error.

When DB2 executes an SQL statement, it places the SQL return code in the SQLCODE field of the SQL Communication Area (SQLCA). $STATUSD retrieves the value of the SQLCODE field after an SQL statement. Typically, a negative SQL return code indicates an error, a positive return code indicates an exceptional but valid condition, and zero indicates successful execution.

Note: In one special case, $STATUS has the same value as SQLCODE. A $STATUS of 100 indicates table empty or completed; in this case, $STATUSD, which contains SQLCODE, is also 100.

Understanding the $DB2EMSG function

If the SQL return code does not equal zero (SQLCODE NE 0), then DB2 also returns tokens in the SQLCA. UL/DB2 processes the tokens internally, and returns them using $DB2EMSG. The string returned by $DB2EMSG can be up to 240 characters long.

For example, attempting to access a column that does not exist returns an SQLCA with the SQLCODE set to -205, and SQLERRM contains two tokens: the column name and the table name. Looking up the SQLCODE in the DATABASE 2 Messages and Codes Manual gives the following error message:

-205 column-name IS NOT A COLUMN OF TABLE table-name

Retrieving the value of $DB2EMSG returns:

DSNT408I SQLCODE = -205, ERROR: FOO IS NOT A COLUMN OF TABLE M204.BAR

(This example assumes that DB2 contains table M204.BAR, which does not contain column FOO.)

You can find all the SQL return codes in the IBM DATABASE 2 Messages and Codes Manual.

Example

The following code fragment shows how you can test the value of $STATUS after executing an SQL statement:

EXEC DB2 OPEN BAR END EXEC IF $STATUS NE 0 THEN PRINT ' $STATUS=' AND $STATUS PRINT '$STATUSD=' AND $STATUSD PRINT $DB2EMSG END IF