GET DIAGNOSTICS

The GET DIAGNOSTICS statement provides diagnostic information about the last SQL statement (other than a GET DIAGNOSTICS statement) that was executed. This diagnostic information is gathered as the previous SQL statement is executed. Some of the information available through the GET DIAGNOSTICS statement is also available in the SQLCA.

Invocation

This statement can only be embedded in an application program. It is an executable statement that cannot be dynamically prepared.

Authorization

None required.

Syntax

Bold text indicates clauses or options that are supported only syntactically.

GET DIAGNOSTICS {statement-information | condition-information | combined-information}

statement-information:

{host-variable1 = statement-information-item-name | host-variable1 = DB2_GET_DIAGNOSTICS_DIAGNOSTICS} [,...]

statement-information-item-name:

{DB2_LAST_ROW | DB2_NUMBER_PARAMETER_MARKERS | DB2_NUMBER_RESULT_SETS | 
        DB2_RETURN_STATUS | DB2_SQL_ATTR_CURSOR_HOLD |
        DB2_SQL_ATTR_CURSOR_ROWSET | DB2_SQL_ATTR_CURSOR_SCROLLABLE |
        DB2_SQL_ATTR_CURSOR_SENSITIVITY | DB2_SQL_ATTR_CURSOR_TYPE |
        MORE | NUMBER | ROW_COUNT} [,...]

condition-information:

CONDITION {host-variable2 | integer} 
       {host-variable3 = {condition-information-item-name | connection-information-item-name}} [,...]

condition-information-item-name:

{CATALOG_NAME | CONDITION_NUMBER | CURSOR_NAME | DB2_ERROR_CODE1 |
DB2_ERROR_CODE2 | DB2_ERROR_CODE3 | DB2_ERROR_CODE4 | DB2_INTERNAL_ERROR_POINTER | 
DB2_MESSAGE_ID | DB2_MODULE_DETECTING_ERROR | DB2_ORDINAL_TOKEN_n | DB2_REASON_CODE | 
DB2_RETURNED_SQLCODE | DB2_ROW_NUMBER | DB2_SQLERRD_SET | DB2_SQLERRD1 | DB2_SQLERRD2 | 
DB2_SQLERRD3 | DB2_SQLERRD4 | DB2_SQLERRD5 | DB2_SQLERRD6 | DB2_TOKEN_COUNT | MESSAGE_TEXT | 
RETURNED_SQLSTATE | SERVER_NAME}

connection-information-item-name:

{DB2_AUTHENTICATION_TYPE | DB2_AUTHORIZATION_ID | DB2_CONNECTION_STATE | 
DB2_CONNECTION_STATUS | DB2_ENCRYPTION_TYPE | DB2_SERVER_CLASS_NAME | DB2_PRODUCT_ID}

combined-information:

host-variable4 = ALL {STATEMENT | {CONDITION | CONNECTION} [host-variable5 | integer]} [,...]
Note:
  • STATEMENT can only be specified once.
  • CONDITION and CONNECTION can only be specified once if host-variable5 or integer is not also specified.

Description

Diagnostic information is provided in three main areas: statement information, condition information, and combined information. After the execution of an SQL statement, information about the execution of the statement is provided as statement information, and at least one instance of condition information is provided. The number of instances of the condition information is indicated by the NUMBER item that is available in the statement information. Combined information contains a text representation of all the information gathered about the execution of the SQL statement.

The diagnostic information that is provided is specific to the server. If you are connected to a server other than DB2 UDB for z/OS, see that product's documentation for the diagnostic information that is returned.

statement-information

Provides information about the last SQL statement executed.

host-variable1

Identifies a variable described in the program in accordance with the rules for declaring host variables. The data type of the host variable must be the data type as specified in Data types for GET DIAGNOSTICS items.

The host variable is assigned the value of the specified statement information item. If the value is truncated when assigning it to the host variable, a warning is returned and the GET_DIAGNOSTICS_DIAGNOSTICS item of the diagnostics area is updated with the details of this condition. If a DIAGNOSTICS item is not set, then the host variable is set to a default value, based on its data type: 0 for an exact numeric field, an empty string for a VARCHAR field, and blanks for a CHAR field.

DB2_GET_DIAGNOSTICS_DIAGNOSTICS

Contains textual information about errors or warnings that may have occurred in the execution of the GET DIAGNOSTICS statement. The format of the information is similar to what would be returned by a GET DIAGNOSTICS :hv = ALL statement.

statement-information-item-name:

DB2_LAST_ROW

For a multiple-row FETCH statement, contains a value of +100 if the last row currently in the table is in the set of rows that have been fetched. For cursors that are not sensitive to updates, there would be no need to do a subsequent FETCH, because the result would be an end-of-data indication. For cursors that are sensitive to updates, a subsequent FETCH may return more data if a row had been inserted before the FETCH was executed. For statements other than multiple-row FETCH statements, or for multiple-row FETCH statements that do not contain the last row, this variable contains the value 0.

DB2_NUMBER_PARAMETER_MARKERS

For a PREPARE statement, contains the number of parameter markers in the prepared statement. Otherwise, or if the server only returns an SQLCA, the value zero is returned.

DB2_NUMBER_RESULT_SETS

For a CALL statement, contains the actual number of result sets returned by the procedure. Otherwise, or if the server only returns an SQLCA, the value zero is returned.

DB2_NUMBER_ROWS

If the previous SQL statement was an OPEN or a FETCH that caused the size of the result table to be unknown, returns the number of rows in the result table. For SENSITIVE DYNAMIC cursors, this value can be thought of as an approximation because rows that are inserted and deleted will affect the next retrieval of this value. If the previous SQL statement was a PREPARE statement, returns the estimated number of rows in the result table for the prepared statement. Otherwise, or if the server only returns an SQLCA, the value zero is returned.

DB2_RETURN_STATUS

Identifies the status value returned from the stored procedure associated with the previously executed SQL statement, provided that the statement was a CALL statement that invoked a procedure that returns a status. . Otherwise, or if the server only returns an SQLCA, the value zero is returned.

DB2_SQL_ATTR_CURSOR_HOLD

For an ALLOCATE or OPEN statement, indicates whether a cursor can be held open across multiple units of work.

  • N indicates that this cursor does not remain open across multiple units of work.
  • Y indicates that this cursor remains open across multiple units of work.

Otherwise, a blank is returned.

DB2_SQL_ATTR_CURSOR_ROWSET

For an ALLOCATE or OPEN statement, indicates whether or not a cursor can be accesses using rowset positioning.

  • N indicates that this cursor supports only row positioned operations.
  • Y indicates that this cursor supports rowset positioned operations.

Otherwise, a blank is returned.

DB2_SQL_ATTR_CURSOR_SCROLLABLE

For an ALLOCATE or OPEN statement, indicates whether or not a cursor can be scrolled forward and backward.

  • N indicates that this cursor is not scrollable.
  • Y indicates that this cursor is scrollable.

Otherwise, a blank is returned.

DB2_SQL_ATTR_CURSOR_SENSITIVITY

For an ALLOCATE or OPEN statement, indicates whether or not a cursor does or does not show updates to cursor rows made by other connections.

  • I indicates insensitive.
  • S indicates sensitive.

Otherwise, a blank is returned.

DB2_SQL_ATTR_CURSOR_TYPE

For an ALLOCATE or OPEN statement, indicates the type of cursor, whether a cursor type is forward-only, static, or dynamic.

  • F indicates a forward cursor.
  • D indicates a dynamic cursor.
  • S indicates a static cursor.

Otherwise, a blank is returned.

MORE

Indicates whether some of the warning and errors from the previous SQL statement were stored or discarded.

  • N indicates that all the warnings and errors from the previous SQL statement are stored in the diagnostic area.
  • Y indicates that some of the warnings and errors from the previous SQL statement were discarded because the amount of storage needed to record warnings and errors exceeded 65535 bytes.

NUMBER

Returns the number of errors and warnings detected by the execution of the previous SQL statement, other than a GET DIAGNOSTICS statement, that have been stored in the diagnostics area. If the previous SQL statement returned an SQLSTATE of 00000 or no previous SQL statement has been executed, the number returned is one.

The GET DIAGNOSTICS statement itself may return information via the SQLSTATE parameter, but does not modify the previous contents of the diagnostics area, except for the DB2_GET_DIAGNOSTICS_DIAGNOSTICS item.

ROW_COUNT

Identifies the number of rows associated with the previous SQL statement that was executed.

If the previous SQL statement is a DELETE, INSERT, or UPDATE statement, ROW_COUNT identifies the number of rows deleted, inserted, or updated by that statement, excluding rows affected by either triggers or referential integrity constraints.

If the previous SQL statement is a multiple-row FETCH, ROW_COUNT identifies the number of rows fetched.

A value of -1 indicates a mass delete from a table in a segmented table space.

Otherwise, or if the server only returns an SQLCA, the value zero is returned.

condition-information

Assigns the values of the specified condition information to the associated host variables. The host variable specified must be of the data type that is compatible with the data type of the specified diagnostic-ID or an error occurs. If the value of the condition is truncated when assigning it to the host variable, an error occurs. If an indicator variable was provided, the length of the value is returned in the indicator variable.

If a DIAGNOSTICS item is not set, then the host variable is set to a default value, based on the data type of the item. The specific value will be 0 for a numeric field, an empty string for a VARCHAR field, and blanks for a CHAR field.

host-variable2 or integer

Identifies the diagnostic for which information is requested. Each diagnostic that occurs while executing an SQL statement is assigned an integer. The value 1 indicates the first diagnostic, 2 indicates the second diagnostic, and so on. If the value is 1, the diagnostic information that is retrieved corresponds to the condition that is indicated by the SQLSTATE value actually returned by the execution of the previous SQL statement (other than a GET DIAGNOSTICS statement). The host variable specified must be a numeric data type or an error occurs. An indicator variable is not allowed for this host variable. If a value is specified that is less than or equal to zero or greater than the number of available diagnostics, an error occurs.

host-variable3

Identifies a variable described in the program in accordance with the rules for declaring host variables. The data type of the host variable must be the data type as specified in Data types for GET DIAGNOSTICS items for the indicated condition-information item.

condition-information-item-name:

CATALOG_NAME

If the returned SQLSTATE is any one of the following values, the constraint that caused the error is a referential, check, or unique constraint. The location (RDB) name of the server that generated the condition is returned.

  • Class 09 (Triggered Action Exception)
  • Class 23 (Integrity Constraint Violation)
  • Class 27 (Triggered Data Change Violation)
  • 40002 (Transaction Rollback - Integrity Constraint Violation)
  • 40004 (Transaction Rollback - Triggered Action Exception)

If the returned SQLSTATE is class 42 (Syntax Error or Access Rule Violation), the server name of the table that caused the error is returned.

If the returned SQLSTATE is class 44 (WITH CHECK OPTION Violation), the server name of the view that caused the error is returned.

Otherwise, the empty string is returned.

The actual server name may be different than the server name specified, either implicitly or explicitly, on the CONNECT statement because of the use of aliases or synonyms.

CONDITION_NUMBER

Returns the number of the diagnostic returned.

CURSOR_NAME

If the returned SQLSTATE is class 24 (Invalid Cursor State), the name of the cursor is returned. Otherwise, the empty string is returned.

DB2_ERROR_CODE1

Returns an internal error code (SQLERRD1). Otherwise, or if the server only returns an SQLCA, the value 0 is returned.

DB2_ERROR_CODE2

Returns an internal error code (SQLERRD2). Otherwise, or if the server only returns an SQLCA, the value 0 is returned.

DB2_ERROR_CODE3

Returns an internal error code (SQLERRD3). Otherwise, or if the server only returns an SQLCA, the value 0 is returned.

DB2_ERROR_CODE4

Returns an internal error code (SQLERRD4). Otherwise, or if the server only returns an SQLCA, the value 0 is returned.

DB2_INTERNAL_ERROR_POINTER

For some errors, this is a negative value that is an internal error pointer. Otherwise, the value 0 is returned.

DB2_MESSAGE_ID

Corresponds to the message that is contained in the MESSAGE_TEXT diagnostic item (for example, DSNT102I or DSNU180I).

DB2_MODULE_DETECTING_ERROR

Returns an identifier indicating which module detected the error. For a SIGNAL statement that is issued from a routine, the value 'ROUTINE' is returned. Otherwise, the string 'DSN ' is returned.

DB2_ORDINAL_TOKEN_n

Returns the nth token. n must be a value from 1 to 100. For example, DB2_ORDINAL_TOKEN_1 would return the value of the first token, DB2_ORDINAL_TOKEN_2 the second token, and so on. A numeric value for a token is converted to characters before being returned. If there is no value for the token, or if the server only returns an SQLCA, an empty string is returned.

DB2_REASON_CODE

Contains the reason code for errors that have a reason code token in the message text. Otherwise, the value zero is returned.

DB2_RETURNED_SQLCODE

Returns the SQLCODE for the specified diagnostic.

DB2_ROW_NUMBER

Returns the number of the row where the condition was encountered, when such information is available and applicable.

DB2_SQLERRD_SET

A value of Y indicates that the DB2_SQLERRD1 through DB2_SQLERRD items might be set. These items are set only when communicating with a server that returns the SQLCA SQL communications area and not the new diagnositics area. Otherwise, a blank is returned.

DB2_SQLERRD1

Returns the value of sqlerrd(1) from the SQLCA that is returned by the server. Otherwise, the value zero is returned.

DB2_SQLERRD2

Returns the value of sqlerrd(2) from the SQLCA that is returned by the server. Otherwise, the value zero is returned.

DB2_SQLERRD3

Returns the value of sqlerrd(3) from the SQLCA that is returned by the server. Otherwise, the value zero is returned.

DB2_SQLERRD4

Returns the value of sqlerrd(4) from the SQLCA that is returned by the server. Otherwise, the value zero is returned.

DB2_SQLERRD5

Returns the value of sqlerrd(5) from the SQLCA that is returned by the server. Otherwise, the value zero is returned.

DB2_SQLERRD6

Returns the value of sqlerrd(6) from the SQLCA that is returned by the server. Otherwise, the value zero is returned.

DB2_TOKEN_COUNT

Returns the number of tokens available for the specified diagnostic ID.

MESSAGE_TEXT

Returns the message text that is associated with the SQLCODE. This is the short text, including substituted tokens. The message text does not contain the message number.

RETURNED_SQLSTATE

Returns the SQLSTATE for the specified diagnostic.

SERVER_NAME

If the previous SQL statement is a CONNECT, DISCONNECT, or SET CONNECTION statement, returns the name of the server specified in the previous statement is returned. Otherwise, the name of the server where the statement executes is returned.

connection-information-item-name

Provides information about the last SQL statement executed if it was a CONNECT statement.

DB2_AUTHENTICATION_TYPE

Contains an authentication type value of:

  • 'S' for a server authentication
  • 'C' for client authentication
  • Otherwise, or if the server only returns an SQLCA, a blank is returned

DB2_AUTHORIZATION_ID

Authorization ID used by connected server. Because of user ID translation and authorization exits, the local user ID may not be the authorized ID used by the server.

DB2_CONNECTION_STATE

Contains the connection state:

  • -1 if the connection is unconnected
  • 1 if the connection is connected

Otherwise, or if the server only returns an SQLCA, the value zero is returned.

DB2_CONNECTION_STATUS

Contains a value of:

  • 1 if committable updates can be performed on the connection for this unit of work
  • 2 if no committable updates can be performed on the connection for this unit of work

Otherwise, or if the server only returns an SQLCA, the value zero is returned.

DB2_SERVER_CLASS_NAME

For a CONNECT or SET CONNECTION statement, contains one of the following values:

  • QAS for DB2 UDB for iSeries(TM)
  • QDB2 for DB2 UDB for OS/390 and z/OS
  • QDB2/2 for DB2 UDB for OS/2
  • QDB2/6000 for DB2 UDB for AIX
  • QDB2/6000 PE for DB2 UDB for AIX Parallel Edition
  • QDB2/AIX64 for DB2 UDB for AIX 64-bit
  • QDB2/HPUX for DB2 UDB for HP-UX
  • QDB2/HP64 for DB2 UDB for HP-UX 64-bit
  • QDB2/LINUX for DB2 UDB for Linux
  • QDB2/LINUX390 for DB2 UDB for Linux
  • QDB2/LINUXIA64 for DB2 UDB for Linux
  • QDB2/LINUXPPC for DB2 UDB for Linux
  • QDB2/LINUXPPC64 for DB2 UDB for Linux
  • QDB2/LINUXZ64 for DB2 UDB for Linux
  • QDB2/PTX for DB2 UDB for NUMA-Q
  • QDB2/SCO for DB2 UDB for SCO UnixWare
  • QDB2/SGI for DB2 UDB for Silicon Graphics
  • QDB2/SNI for DB2 UDB for Siemens Nixdorf
  • QDB2/SUN for DB2 UDB for SUN Solaris
  • QDB2/SUN64 for DB2 UDB for SUN Solaris 64-bit
  • QSQLDS/VM for DB2 for VM and VSE
  • QSQLDS/VSE for DB2 for VM and VSE

Otherwise, the empty string is returned.

DB2_ENCRYPTION_TYPE

The level of encryption for the connection:

  • A indicates only the authentication tokens (authid and password) are encrypted.
  • D indicates all data is encrypted for the connection.

Otherwise, a blank is returned.

DB2_PRODUCT_ID

Returns a product signature. If the application server is an IBM relational database product, the form is pppvvrrm, where:

  • ppp identifies the product as follows:
    • ARI for DB2 Server for VSE & VM
    • DSN for DB2 UDB for z/OS
    • QSQ for DB2 UDB for iSeries
    • SQL for all other DB2 UDB products
  • vv is a two-digit version identifier such as '08'
  • rr is a two-digit release identifier such as '01'
  • m is a one-digit maintenance level identifier such as '5' (Values 0, 1, 2, 3, and 4 are for maintenance levels in compatibility and enabling-new-function mode. Values 5, 6, 7, 8, and 9 are for maintenance levels in new-function mode.)

For example, if the application server is Version 8 of DB2 UDB for z/OS in new-function mode with the latest maintenance, the value would be 'DSN08015'.

combined-information

Provides a text representation of all the information gathered about the execution of the SQL statement.

ALL

Indicates that all diagnostic items that are set for the last SQL statement executed are to be combined into one string. The format of the string is a semicolon separated list of all of the available diagnostic information in the form:

item-name[(condition-number)]=value-converted-to-character;... as shown in the following example:

NUMBER=1;RETURNED_SQLSTATE=02000;DB2_RETURNED_SQLCODE=+100;
 

host-variable4

Identifies a variable described in the program in accordance with the rules for declaring host variables. The data type of the host variable must be VARCHAR. If the length of host-variable4 is not sufficient to hold the full returned diagnostic string, the string is truncated, a warning is returned, and the GET_DIAGNOSTICS_DIAGNOSITICS item of the diagnostics area is updated with the details of this condition.

STATEMENT

Indicates that all statement-information-item-name diagnostic items that are set for the last SQL statement executed should be combined into one string. The format is the same as described for the ALL option.

CONDITION

Indicates that all condition-information-item-name diagnostic items that are set for the last SQL statement executed should be combined into one string. If host-variable5 or integer is supplied after CONDITION, the format is the same as described above for the ALL option. If host-variable5 or integer is not supplied, the format includes a condition number entry at the beginning of the information for that condition in the form:

CONDITION_NUMBER=X;item-name=value-converted-to-character;... where X is the number of the condition, as shown in the following example:

CONDITION_NUMBER=1;RETURNED_SQLSTATE=02000;RETURNED_SQLCODE=100;
            
 
  CONDITION_NUMBER=2;RETURNED_SQLSTATE=01004;

CONNECTION

Indicates that all connection-information-item-name diagnostic items that are set for the last SQL statement executed should be combined into one string. If host-variable5 or integer is supplied after CONNECTION, the format is the same as described for the ALL option. If host-variable5 or integer is not supplied, then the format includes a condition number entry at the beginning of the information for that condition in the form:

CONNECTION_NUMBER=X;item-name=value-converted-to-character;... where X is the number of the condition, as shown in the following example:

CONNECTION_NUMBER=1;CONNECTION_NAME=SVL1;DB2_PRODUCT_ID=DSN08010;
 
 
  

host-variable5 or integer

Identifies the diagnostic for which ALL CONDITION or ALL CONNECTION information is requested. The host variable specified must be a numeric data type or an error occurs. An indicator variable is not allowed for this host variable or an error occurs. If a value is specified that is less than or equal to zero or greater than the number of available diagnostics, an error occurs.

Note:

The GET DIAGNOSTICS statement does not change the contents of the diagnostics area (SQLCA). If an SQLSTATE or SQLCODE special variable is declared in the SQL procedure, these are set to the SQLSTATE or SQLCODE returned from issuing the GET DIAGNOSTICS statement. The SQLSTATE and SQLCODE values from before the GET DIAGNOSTICS statement was issued are still available in the diagnostics area by issuing a GET DIAGNOSTICS for RETURNED_SQLSTATE and DB2_RETURNED_SQLCODE.

Data types for GET DIAGNOSTICS items

Item Data Type
Statement Information
DB2_GET_DIAGNOSTICS_DIAGNOSTICS VARCHAR(32672)
DB2_LAST_ROW INTEGER
DB2_NUMBER_PARAMETER_MARKERS INTEGER
DB2_NUMBER_RESULT_SETS INTEGER
DB2_NUMBER_ROWS DECIMAL(31,0)
DB2_RETURN_STATUS INTEGER
DB2_SQL_ATTR_CURSOR_HOLD CHAR(1)
DB2_SQL_ATTR_CURSOR_ROWSET CHAR(1)
DB2_SQL_ATTR_CURSOR_SCROLLABLE CHAR(1)
DB2_SQL_ATTR_CURSOR_SENSITIVITY CHAR(1)
DB2_SQL_ATTR_CURSOR_TYPE CHAR(1)
MORE CHAR(1)
NUMBER INTEGER
ROW_COUNT DECIMAL(31,0)
CATALOG NAME VARCHAR(128)
CONDITION_NUMBER INTEGER
CURSOR_NAME VARCHAR(128)
DB2_ERROR_CODE1 INTEGER
DB2_ERROR_CODE2 INTEGER
DB2_ERROR_CODE3 INTEGER
DB2_ERROR_CODE4 INTEGER
DB2_INTERNAL_ERROR_POINTER INTEGER
DB2_MESSAGE_ID CHAR(10)
DB2_MODULE_DETECTING_ERROR CHAR(8)
DB2_ORDINAL_TOKEN_n VARCHAR(515)
DB2_REASON_CODE INTEGER
DB2_RETURNED_SQLCODE INTEGER
DB2_ROW_NUMBER DECIMAL(31,0)
DB2_TOKEN_COUNT INTEGER
MESSAGE_TEXT VARCHAR(32672)
RETURNED_SQLSTATE CHAR(5)
SERVER_NAME VARCHAR(128)
Connection Information
DB2_AUTHENTICATION_TYPE CHAR(1)
DB2_AUTHORIZATION_ID VARCHAR(128)
DB2_CONNECTION_STATE INTEGER
DB2_CONNECTION_STATUS INTEGER
DB2_ENCRYPTION_TYPE CHAR(1)
DB2_PRODUCT_ID VARCHAR(8)
DB2_SERVER_CLASS_NAMED CHAR(128)
ALL VARCHAR(32672)
Note:
  • The GET DIAGNOSTICS statement is supported from a DB2 UDB for z/OS Version 8 client, regardless of the level of the server (a DB2 UDB for z/OS Version 7 or a DB2 UDB for Windows Version 7, for example). When connected to servers that do not support the Open Group Version 3 DRDA standard, a subset of diagnostic information is generated based on the returned SQLCA. The condition contains the following information:
    • The DB2_RETURNED_SQLCODE is set based on the SQLCODE.
    • The RETURNED_SQLSTATE is set based on the SQLSTATE.
    • The DB2_GET_DIAGNOSTICS_DIAGNOSTICS item contains the information from the SQLCA that came from the server.

    Only if you are using multi-row fetch or insert is there a need to have the extended diagnostic information that is provided by servers that support the Open Group Version 3 DRDA standard.

  • To provide compatibility with previous releases of DB2 or other products in the DB2 UDB family, DB2 supports the following keywords:
    • RETURN_STATUS as a synonym for DB2_RETURN_STATUS
    • EXCEPTION as a synonym for CONDITION