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


        .-CURRENT-----.                
>>-GET -+-+---------+-+--DIAGNOSTICS---------------------------->
          '-STACKED-'                  

>--+-| statement-information |-+-------------------------------><
   +-| condition-information |-+   
   '-| combined-information |--'

statement-information:


statement-information

     .-,-------------------------------------------------.     
     V                                                   |     
|--+---variable1--=--| statement-information-item-name |-+-+----|
   +-variable1--=--DB2_GET_DIAGNOSTICS_DIAGNOSTICS---------+   
   '-variable1--=--DB2_SQL_NESTING_LEVEL-------------------'   

statement-information-item-name

   .-,-----------------------------------.   
   V                                     |   
|----+-DB2_LAST_ROW--------------------+-+----------------------|
     +-DB2_NUMBER_PARAMETER_MARKERS----+     
     +-DB2_NUMBER_RESULT_SETS----------+     
     +-DB2_NUMBER_ROWS-----------------+     
     +-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-information

|--CONDITION--+-variable2-+------------------------------------->
              '-integer---'   

   .-,-------------------------------------------------------.   
   V                                                         |   
>----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_LINE_NUMBER------------+   
   +-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:

combined-information

                      .-,-------------------------------------.   
                      V             (1)                       |   
|--variable4--=--ALL----+-STATEMENT-------------------------+-+--|
                        |                (2)                |     
                        '-+-CONDITION--+------+-----------+-'     
                          '-CONNECTION-'      +-variable5-+       
                                              '-integer---'

Notes:

STATEMENT can only be specified once.
CONDITION and CONNECTION can only be specified once if 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® for z/OS®, see that product's documentation for the diagnostic information that is returned.

CURRENT

Specifies that information is to be returned from the first diagnostics area. It corresponds to the previous SQL statement that was executed that was not a GET DIAGNOSTICS or compound statement. CURRENT is the default.

STACKED

Specifies that information is to be returned from the stacked diagnostics area. The stacked diagnostics area is only available within a handler in a native SQL procedure and compiled SQL functions. The stacked diagnostics area corresponds to the previous SQL statement (that was not a GET DIAGNOSTICS or compound statement) that was executed before the handler was entered. If the GET DIAGNOSTICS statement is the first statement within a handler, the current diagnostics area and the stacked diagnostics area contain the same diagnostics information. End of change

statement-information

Provides information about the last SQL statement executed.

variable1

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

The variable is assigned the value of the specified statement information item. If the value is truncated when assigning it to the 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, the 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 might 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.

DB2_SQL_NESTING_LEVEL

Identifies the current level of nesting or recursion that is in effect when the GET DIAGNOSTICS statement was executed. Each level of nesting corresponds to a nested or recursive invocation of a compiled SQL function, native SQL procedure, or trigger. If the GET DIAGNOSTICS statement is executed outside of a level of nesting, the value of zero is returned. End of change

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.

An end of data warning might not occur and DB2_LAST_ROW might not contain +100 when the number of rows returned is equal to the number of rows requested and the last row of data returned is the last row of data.

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 known, 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.