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
>>-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 itemsType of information |
Item |
Data type |
Statement
information |
DB2_RETURN_STATUS |
INTEGER |
DB2_SQL_NESTING_LEVEL |
INTEGER |
ROW_COUNT |
DECIMAL(31,0) |
Condition
information |
DB2_TOKEN_STRING |
VARCHAR(1000) |
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
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
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