GET DIAGNOSTICS statement

The GET DIAGNOSTICS statement is used to obtain current execution environment information including information about the previous SQL statement (other than a GET DIAGNOSTICS statement) that was executed. Some of the information available through the GET DIAGNOSTICS statement is also available in the SQLCA.

Invocation

This statement can be embedded in an:

SQL procedure definition
Compound SQL (compiled) statement
Compound SQL (inlined) statement

The compound statements can be embedded in an SQL procedure definition, SQL function definition, or SQL trigger definition. It is not an executable statement and cannot be dynamically prepared.

Authorization

None required.

Syntax

statement-information

condition-information

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)

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