10.11.7 Using SQL trace information to determine the cause of errors in SQL statements

Organization of this subsection

(1) How to determine the cause of errors in SQL statements
(2) How to identify how far the SQL statement was executed

(1) How to determine the cause of errors in SQL statements

The following procedure explains how to use SQL trace information to determine the cause of errors in SQL statements.

Procedure

Check SQLCODE that has been output to the SQL statement execution information.

SQLCODE is displayed in the sqlcode column. Check for an SQLCODE that is a negative value.

Example:
Check the message corresponding to the output SQLCODE.

For details about how SQLCODEs correspond to messages, see Interpreting SQLCODEs in the manual HADB Messages.

In this example, SQLCODE is -559, which means that the corresponding message is KFAA30559-E.
Make a note of the message log information that is displayed under message_log_info.

Example:
Retrieve the message that has been output to the message log file.

Retrieve from the message log file the message that was checked in step 2. In this example, retrieve the KFAA30559-E message from the message log file.

Then check if the message log information obtained in step 3 matches the message log information contained in the message. If they match, the KFAA30559-E message is the target message.

Example:
Check the corrective action given in the message.

See the corrective action given in the message and identify the cause of the error in the SQL statement.

In this example, take the corrective action given in the Action column for the KFAA30559-E message.

To Page Top

(2) How to identify how far the SQL statement was executed

The method depends on the unit of SQL trace information output.

(a) If SQL trace information was set to be output for each call

An error occurred in the call whose SQLCODE in the sqlcode column is a negative value.

Example:

[Figure]

(b) If SQL trace information was set to be output for each SQL statement

Identify the items that have been output and the items that have not been output to determine the call resulting in the error. The following shows an example.

Example 1: The executed SQL statement has been output, but access path information has not been output.

An error occurred during processing of PREP (preprocessing of SQL statement) or EXDI (preprocessing and execution of SQL statement).
Example 2: The executed SQL statement, access path information, and dynamic parameter information have been output, but the SQL statement statistical information and access path statistical information have not been output.

An error occurred during processing of OPEN (cursor open processing).

To Page Top