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
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:
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:item-name=character-form-of-the-item-value;
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.NUMBER=1;RETURNED_SQLSTATE=02000;DB2_RETURNED_SQLCODE=+100;
- 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:
where X is the number of the condition. For example:CONDITION_NUMBER=X;item-name=character-form-of-the-item-value;
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:
where X is the number of the condition. For example:CONNECTION_NUMBER=X;item-name=character-form-of-the-item-value;
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 changes 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;