GET DIAGNOSTICS statement

The GET DIAGNOSTICS statement obtains information about the previous SQL statement that was executed. The syntax of GET DIAGNOSTICS in an SQL function, SQL procedure, or SQL trigger is a subset of what is supported as a GET DIAGNOSTICS statement in other contexts.

See GET DIAGNOSTICS for details.

Syntax

statement-information

condition-information

combined-information

Notes:

¹ STATEMENT can only be specified once. If SQL-variable-name-5, SQL-parameter-name-5, or integer is not specified, CONDITION and CONNECTION can only be specified once.

statement-information-item

connection-information-item

condition-information-item

Description

label

Specifies the label for the GET DIAGNOSTICS statement. If the ending label is specified, it must be the same as the beginning label. The label name cannot be the same as the routine name or another label within the same scope. For more information, see References to SQL labels.

CURRENT or STACKED

Specifies which diagnostics area to access.

CURRENT: Specifies to access the first diagnostics area. It corresponds to the previous SQL statement that was executed and that was not a GET DIAGNOSTICS statement. This is the default.
STACKED: Specifies to access the second diagnostics area. The second diagnostics area is only available within a handler. It corresponds to the previous SQL statement that was executed before the handler was entered and that was not a GET DIAGNOSTICS statement. If the GET DIAGNOSTICS statement is the first statement within a handler, then the first diagnostics area and the second diagnostics area contain the same diagnostics information.

statement-information

Returns information about the last SQL statement executed.

SQL-variable-name-1 or SQL-parameter-name-1: Identifies a variable described in the program in accordance with the rules for declaring SQL variables and SQL parameters. The data type of the SQL variable or SQL parameter must be compatible with the data type as specified in Table 1 for the specified condition information item. The variable is assigned the value of the specified statement information item according to the retrieval assignment rules described in Retrieval assignment. If the value is truncated when assigning it to the SQL variable or SQL parameter, a warning is returned (SQLSTATE 01004) and the GET_DIAGNOSTICS_DIAGNOSTICS item of the diagnostics area is updated with the details of this condition.
If a specified diagnostic item does not contain diagnostic information , then the SQL variable or SQL parameter is set to a default value, based on its data type: 0 for an exact numeric diagnostic item, an empty string for a VARCHAR diagnostic item and blanks for a CHAR diagnostic item.

condition-information

Returns information about the condition or conditions that occurred when the last SQL statement was executed.

CONDITION SQL-variable-name-2 or SQL-parameter-name-2 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, then the diagnostic information retrieved corresponds to the condition indicated by the SQLSTATE value actually returned by the execution of the previous SQL statement (other than a GET DIAGNOSTICS statement). The SQL variable or SQL parameter specified must be described in the program in accordance with the rules for declaring exact numeric variables with zero scale. The value specified must not be less than one or greater than the number of available diagnostics

SQL-variable-name-3 or SQL-parameter-name-3

Identifies a variable described in the program in accordance with the rules for declaring SQL variables or SQL parameters. The data type of the SQL variable or SQL parameter must be compatible with the data type as specified in Table 1 for the specified condition information item. The SQL variable or SQL parameter is assigned the value of the specified condition information item according to the retrieval assignment rules described in Retrieval assignment. If the value is truncated when assigning it to the SQL variable or SQL parameter, a warning is returned (SQLSTATE 01004) and the GET_DIAGNOSTICS_DIAGNOSTICS item of the diagnostics area is updated with the details of this condition.

If a specified diagnostic item does not contain diagnostic information , then the SQL variable or SQL parameter is set to a default value, based on its data type: 0 for an exact numeric diagnostic item, an empty string for a VARCHAR diagnostic item and blanks for a CHAR diagnostic item.

combined-information

Returns multiple information items combined into one string.

If the GET DIAGNOSTICS statement is specified in an SQL function, SQL procedure, or trigger, the GET DIAGNOSTICS statement must be the first statement specified in the handler that will handle the error.

If information is wanted about a warning,

If a handler will get control for the warning condition, the GET DIAGNOSTICS statement must be the first statement specified in that handler.
If a handler will not get control for the warning condition, the GET DIAGNOSTICS statement must be the next statement executed after that previous statement.

SQL-variable-name-4 or SQL-parameter-name-4

Identifies a variable described in the program in accordance with the rules for declaring SQL variables or SQL parameters. The data type of the SQL variable or SQL parameter must be VARCHAR. The SQL variable or SQL parameter is assigned according to the retrieval assignment rules described in Retrieval assignment. If the length of SQL-variable-name-4 or SQL-parameter-name-4 is not sufficient to hold the full returned diagnostic string, the string is truncated, a warning is returned (SQLSTATE 01004) and the GET_DIAGNOSTICS_DIAGNOSTICS item of the diagnostics area is updated with the details of this condition.

ALL

Indicates that all diagnostic items that are set for the last SQL statement executed should 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=character-form-of-the-item-value;

The character form of a positive numeric value will not contain a leading plus sign (+) unless the item is RETURNED_SQLCODE. In this case, a leading plus sign (+) is added. For example:

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

Only items that contain diagnostic information are included in the string. There are also no entries in this string for the DB2_GET_DIAGNOSTICS_DIAGNOSTICS and DB2_SQL_NESTING_LEVEL items.

STATEMENT

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

CONDITION

Indicates that condition-information-item diagnostic items that contain diagnostic information for the last SQL statement executed should be combined into one string. If SQL-variable-name-5 or SQL-parameter-name-5 or integer is specified, then the format is the same as described above for the ALL option. If SQL-variable-name-5 or SQL-parameter-name-5 or integer is not specified, then the format includes a condition number entry at the beginning of the information for that condition in the form:

CONDITION_NUMBER=X;item-name=character-form-of-the-item-value;

where X is the number of the condition. For example:

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

CONNECTION

Indicates that connection-information-item diagnostic items that contain diagnostic information for the last SQL statement executed should be combined into one string. If SQL-variable-name-5 or SQL-parameter-name-5 or integer is specified, then the format is the same as described above for ALL. If SQL-variable-name-5 or SQL-parameter-name-5 or integer is not specified, then the format includes a connection number entry at the beginning of the information for that condition in the form:

CONNECTION_NUMBER=X;item-name=character-form-of-the-item-value;

where X is the number of the condition. For example:

CONNECTION_NUMBER=1;CONNECTION_NAME=SVL1;DB2_PRODUCT_ID=DSN07010;

SQL-variable-name-5 or SQL-parameter-name-5 or integer

Identifies the diagnostic for which ALL CONDITION or ALL CONNECTION information is requested. The SQL variable or SQL parameter specified must be described in the program in accordance with the rules for declaring integer SQL variables or SQL parameters. The value specified must not be less than one or greater than the number of available diagnostics.

statement-information-item

For a description of the statement-information-items, see statement-information-item.

connection-information-item

For a description of the connection-information-items, see connection-information-item.

condition-information-item

For a description of the condition-information-items, see condition-information-item.

Notes

Effect of statement:: The GET DIAGNOSTICS statement does not change the contents of the diagnostics area except for DB2_GET_DIAGNOSTICS_DIAGNOSTICS.

Considerations for the SQLCODE and SQLSTATE SQL variables: The GET DIAGNOSTICS statement Start of change changes End of change the value of the SQLSTATE and SQLCODE SQL variables.

Case of return values: Values for identifiers in returned diagnostic items are not delimited and are case sensitive. For example, a table name of "abc" would be returned, simply as abc.

Data types for items: When a diagnostic item is assigned to a SQL variable or SQL parameter, the SQL variable or SQL parameter must be compatible with the data type of the diagnostic item. For more information, see Table 1.

Keyword Synonym: The following keywords are synonyms supported for compatibility to prior releases. These keywords are non-standard and should not be used:

The keyword EXCEPTION can be used as a synonym for CONDITION.
The keyword RETURN_STATUS can be used as a synonym for DB2_RETURN_STATUS.

Example

Example 1:: In an SQL procedure, execute a GET DIAGNOSTICS statement to determine how many rows were updated.

   CREATE PROCEDURE sqlprocg (IN deptnbr VARCHAR(3))
     LANGUAGE SQL
     BEGIN
       DECLARE SQLSTATE CHAR(5);
       DECLARE rcount INTEGER;
       UPDATE CORPDATA.PROJECT
         SET PRSTAFF = PRSTAFF + 1.5
         WHERE DEPTNO = deptnbr;
       GET DIAGNOSTICS rcount = ROW_COUNT;
       /* At this point, rcount contains the number of rows that were updated. */
     END

Example 2:: Within an SQL procedure, handle the returned status value from the invocation of a stored procedure called TRYIT. TRYIT could use the RETURN statement to explicitly return a status value or a status value could be implicitly returned by the database manager. If the procedure is successful, it returns a value of zero.

   CREATE PROCEDURE TESTIT ()
     LANGUAGE SQL
     A1: BEGIN
         DECLARE RETVAL INTEGER DEFAULT 0;
         ...
         CALL TRYIT
         GET DIAGNOSTICS RETVAL = RETURN_STATUS;
         IF RETVAL <> 0 THEN
            ...
            LEAVE A1;
         ELSE
            ...
         END IF;
     END A1

Example 3:: In an SQL procedure, execute a GET DIAGNOSTICS statement to retrieve the message text for an error.

   CREATE PROCEDURE divide2 ( IN numerator INTEGER,
 	                      IN denominator INTEGER, 
	                      OUT divide_result INTEGER, 
                           OUT divide_error VARCHAR(70) )
  LANGUAGE SQL
    BEGIN
       DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 
          GET DIAGNOSTICS EXCEPTION 1
            divide_error = MESSAGE_TEXT;
       SET divide_result = numerator / denominator;
    END;