GET DIAGNOSTICS statement

Invocation

Authorization

None required.

Syntax

>>-GET DIAGNOSTICS--+-| statement-information |-+--------------><
                    '-| condition-information |-'   

statement-information

|--SQL-variable-1--=--+-DB2_RETURN_STATUS-----+-----------------|
                      +-DB2_SQL_NESTING_LEVEL-+   
                      '-ROW_COUNT-------------'   

condition-information

                 .-,---------------------------------------.   
                 V                                         |   
|--EXCEPTION--1----SQL-variable-2--=--+-DB2_TOKEN_STRING-+-+----|
                                      '-MESSAGE_TEXT-----'     

Description

statement-information

Returns information about the last SQL statement executed.

SQL-variable-1

Identifies the variable that is the assignment target. The variable must not be a global variable. SQL variables can be defined in a compound statement. The data type of the variable must be compatible with the data type as specified in Table 1.

DB2_RETURN_STATUS

Identifies the status value returned from the procedure associated with the previously executed SQL statement, provided that the statement was a CALL statement invoking a procedure that returns a status. If the previous statement is not such a statement, then the value returned has no meaning and could be any integer.

DB2_SQL_NESTING_LEVEL

Identifies the current level of nesting or recursion 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, compiled SQL procedure, compiled trigger, or dynamically prepared compound SQL (compiled) statement. If the GET DIAGNOSTICS statement is executed outside of a level of nesting, the value zero is returned. This option can be specified only in the context of a compiled SQL function, compiled SQL procedure, compiled trigger, or compound SQL (compiled) statement (SQLSTATE 42601).

ROW_COUNT

Identifies the number of rows associated with the previous SQL statement. If the previous SQL statement is a DELETE, INSERT, or UPDATE statement, ROW_COUNT identifies the number of rows that qualified for the operation. If the previous statement is a PREPARE statement, ROW_COUNT identifies the estimated number of result rows in the prepared statement.

condition-information

Specifies that the error or warning information for the previously executed SQL statement is to be returned. If information about an error is needed, the GET DIAGNOSTICS statement must be the first statement specified in the handler that will handle the error. If information about a warning is needed, and if the handler will get control of the warning condition, the GET DIAGNOSTICS statement must be the first statement specified in that handler. If the handler will not get control of the warning condition, the GET DIAGNOSTICS statement must be the next statement executed. This option can only be specified in the context of an SQL Procedure (SQLSTATE 42601).

SQL-variable-2

Identifies the variable that is the assignment target. The variable must not be a global variable. SQL variables can be defined in a compound statement. The data type of the variable must be compatible with the data type as specified in Table 1.

DB2_TOKEN_STRING

Identifies any error or warning message tokens returned from the previously executed SQL statement. If the statement completed with an SQLCODE of zero, or if the SQLCODE had no tokens, an empty string is returned for a VARCHAR variable or blanks are returned for a CHAR variable.

MESSAGE_TEXT

Identifies any error or warning message text returned from the previously executed SQL statement. The message text is returned in the language of the database server where the statement is processed. If the statement completed with an SQLCODE of zero, an empty string is returned for a VARCHAR variable or blanks are returned for a CHAR variable.

Notes

• 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.
• Data types for items: The following table shows the SQL data type for each diagnostic item. When a diagnostic item is assigned to a variable, the data type of the variable must be compatible with the data type of the requested diagnostic item.

  Table 1. Data types for GET DIAGNOSTICS items

  Type of information	Item	Data type
  Statement information	DB2_RETURN_STATUS	INTEGER
  Statement information	DB2_SQL_NESTING_LEVEL	INTEGER
  Statement information	ROW_COUNT	DECIMAL(31,0)
  Condition information	DB2_TOKEN_STRING	VARCHAR(1000)
  Condition information	MESSAGE_TEXT	VARCHAR(32672)

• Syntax alternatives: The following are supported for compatibility with previous versions of DB2® and with other database products. These alternatives are non-standard and should not be used.
  • RETURN_STATUS can be specified in place of DB2_RETURN_STATUS.

Examples

• 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 procedure called TRYIT that could either explicitly RETURN a positive value indicating a user failure, or encounter SQL errors that would result in a negative return status value. 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 = DB2_RETURN_STATUS;
        IF RETVAL <> 0 THEN
           ...
           LEAVE A1;
        ELSE
           ...
        END IF;
     END A1