How to Use SQL Diagnostics Areas

By Allen G. Taylor

Although SQLSTATE can give you some information about why a particular statement failed, the information is pretty brief. So SQL provides for the capture and retention of additional status information in diagnostics areas.

The additional status information in a diagnostics area can be particularly helpful in cases in which the execution of a single SQL statement generates multiple warnings followed by an error. SQLSTATE reports the occurrence of only one error, but the diagnostics area has the capacity to report on multiple errors.

The diagnostics area is a DBMS-managed data structure that has two components:

  • Header: The header contains general information about the most recent SQL statement that was executed.

  • Detail area: The detail area contains information about each code that the statement generated.

Diagnostics header area

In the SET TRANSACTION statement, you can specify DIAGNOSTICS SIZE. The SIZE that you specify is the number of detail areas allocated for status information. If you don’t include a DIAGNOSTICS SIZE clause in your SET TRANSACTION statement, your DBMS assigns its default number of detail areas.

Fields Data Type
NUMBER Exact numeric with no fractional part
ROW_COUNT Exact numeric with no fractional part
COMMAND_FUNCTION VARCHAR (implementation defined max
COMMAND_FUNCTION_CODE Exact numeric with no fractional part
DYNAMIC_FUNCTION VARCHAR (implementation defined max
DYNAMIC_FUNCTION_CODE Exact numeric with no fractional part
MORE Exact numeric with no fractional part
TRANSACTIONS_COMMITTED Exact numeric with no fractional part
TRANSACTIONS_ROLLED_BACK Exact numeric with no fractional part
TRANSACTION_ACTIVE Exact numeric with no fractional part

The following list describes these items in more detail:

  • The NUMBER field is the number of detail areas that have been filled with diagnostic information about the current exception.

  • The ROW_COUNT field holds the number of rows affected if the previous SQL statement was an INSERT, UPDATE, or DELETE.

  • The COMMAND_FUNCTION field describes the SQL statement that was just executed.

  • The COMMAND_FUNCTION_CODE field gives the code number for the SQL statement that was just executed. Every command function has an associated numeric code.

  • The DYNAMIC_FUNCTION field contains the dynamic SQL statement.

  • The DYNAMIC_FUNCTION_CODE field contains a numeric code corresponding to the dynamic SQL statement.

  • The MORE field may be either a ‘Y’ or an ‘N’. ‘Y’ indicates that there are more status records than the detail area can hold. ‘N’ indicates that all the status records generated are present in the detail area. Depending on your implementation, you may be able to expand the number of records you can handle by using the SET TRANSACTION statement.

  • The TRANSACTIONS_COMMITTED field holds the number of transactions that have been committed.

  • The TRANSACTIONS_ROLLED_BACK field holds the number of transactions that have been rolled back.

  • The TRANSACTION_ACTIVE field holds a ‘1’ if a transaction is currently active and a ‘0’ otherwise. A transaction is deemed to be active if a cursor is open or if the DBMS is waiting for a deferred parameter.

Diagnostics detail area

The detail areas contain data on each individual error, warning, or success condition.

Fields Data Type
CONDITION_NUMBER Exact numeric with no fractional part
MESSAGE_TEXT VARCHAR (implementation defined max
MESSAGE_LENGTH Exact numeric with no fractional part
MESSAGE_OCTET_LENGTH Exact numeric with no fractional part
CLASS_ORIGIN VARCHAR (implementation defined max
SUBCLASS_ORIGIN VARCHAR (implementation defined max
CONNECTION_NAME VARCHAR (implementation defined max
SERVER_NAME VARCHAR (implementation defined max
CONSTRAINT_CATALOG VARCHAR (implementation defined max
CONSTRAINT_SCHEMA VARCHAR (implementation defined max
CONSTRAINT_NAME VARCHAR (implementation defined max
CATALOG_NAME VARCHAR (implementation defined max
SCHEMA_NAME VARCHAR (implementation defined max
TABLE_NAME VARCHAR (implementation defined max
COLUMN_NAME VARCHAR (implementation defined max
CURSOR_NAME VARCHAR (implementation defined max
CONDITION_IDENTIFIER VARCHAR (implementation defined max
PARAMETER_NAME VARCHAR (implementation defined max
PARAMETER_ORDINAL_POSITION Exact numeric with no fractional part
PARAMETER_MODE Exact numeric with no fractional part
ROUTINE_CATALOG VARCHAR (implementation defined max
ROUTINE_SCHEMA VARCHAR (implementation defined max
ROUTINE_NAME VARCHAR (implementation defined max
SPECIFIC_NAME VARCHAR (implementation defined max
TRIGGER_CATALOG VARCHAR (implementation defined max
TRIGGER_SCHEMA VARCHAR (implementation defined max
TRIGGER_NAME VARCHAR (implementation defined max

CONDITION_NUMBER holds the sequence number of the detail area. If a statement generates five status items that fill up five detail areas, the CONDITION_NUMBER for the fifth detail area is 5. To retrieve a specific detail area for examination, use a GET DIAGNOSTICS statement with the desired CONDITION_NUMBER. RETURNED_SQLSTATE holds the SQLSTATE value that caused this detail area to be filled.

CLASS_ORIGIN tells you the source of the class code value returned in SQLSTATE. If the SQL standard defines the value, the CLASS_ORIGIN is ‘ISO 9075’. If your DBMS implementation defines the value, CLASS_ORIGIN holds a string identifying the source of your DBMS. SUBCLASS_ORIGIN tells you the source of the subclass code value returned in SQLSTATE.

CLASS_ORIGIN is important. If you get an SQLSTATE of ‘22012’, the values indicate that it is in the range of standard SQLSTATEs, so you know that it means the same thing in all SQL implementations.

However, if the SQLSTATE is ‘22500’, the first two characters are in the standard range and indicate a data exception, but the last three characters are in the implementation-defined range. And if SQLSTATE is ‘90001’, it’s completely in the implementation-defined range. SQLSTATE values in the implementation-defined range can mean different things in different implementations, even though the code itself may be the same.

So how do you find out the detailed meaning of ‘22500’ or the meaning of ‘90001’? You must look in the implementor’s documentation. Which implementor?

To determine which one produced the error condition, look at CLASS_ORIGIN and SUBCLASS_ORIGIN: They have values that identify each implementation. You can test the CLASS_ORIGIN and SUBCLASS_ORIGIN to see whether they identify implementors for which you have the SQLSTATE listings. The actual values placed in CLASS_ORIGIN and SUBCLASS_ORIGIN are implementor-defined, but they also are expected to be self-explanatory company names.

If the error reported is a constraint violation, the CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, and CONSTRAINT_NAME identify the constraint being violated.