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.
.-CURRENT-. >>-+--------+--GET--+---------+--DIAGNOSTICS--+-statement-information-+->< '-label:-' '-STACKED-' +-condition-information-+ '-combined-information--' statement-information .-,---------------------------------------------------------. V | |----+-SQL-variable-name-1--+-- = --statement-information-item-+--| '-SQL-parameter-name-1-' condition-information |--CONDITION--+-+-SQL-variable-name-2--+-+----------------------> | '-SQL-parameter-name-2-' | '-integer------------------' .-,--------------------------------------------------------------. V | >----+-SQL-variable-name-3--+-- = --+-connection-information-item-+-+--| '-SQL-parameter-name-3-' '-condition-information-item--' combined-information |--+-SQL-variable-name-4--+-- = --------------------------------> '-SQL-parameter-name-4-' (1) >--ALL--+------------------------------------------------------+------| | .-,------------------------------------------------. | | V | | '---+-STATEMENT------------------------------------+-+-' '-+-CONDITION--+--+--------------------------+-' '-CONNECTION-' +-+-SQL-variable-name-5--+-+ | '-SQL-parameter-name-5-' | '-integer------------------'
statement-information-item |--+-COMMAND_FUNCTION----------------+--------------------------| +-COMMAND_FUNCTION_CODE-----------+ +-DB2_DIAGNOSTIC_CONVERSION_ERROR-+ +-DB2_LAST_ROW--------------------+ +-DB2_NUMBER_CONNECTIONS----------+ +-DB2_NUMBER_PARAMETER_MARKERS----+ +-DB2_NUMBER_RESULT_SETS----------+ +-DB2_NUMBER_ROWS-----------------+ +-DB2_NUMBER_SUCCESSFUL_SUBSTMTS--+ +-DB2_RELATIVE_COST_ESTIMATE------+ +-DB2_RETURN_STATUS---------------+ +-DB2_ROW_COUNT_SECONDARY---------+ +-DB2_ROW_LENGTH------------------+ +-DB2_SQL_ATTR_CONCURRENCY--------+ +-DB2_SQL_ATTR_CURSOR_CAPABILITY--+ +-DB2_SQL_ATTR_CURSOR_HOLD--------+ +-DB2_SQL_ATTR_CURSOR_ROWSET------+ +-DB2_SQL_ATTR_CURSOR_SCROLLABLE--+ +-DB2_SQL_ATTR_CURSOR_SENSITIVITY-+ +-DB2_SQL_ATTR_CURSOR_TYPE--------+ +-DYNAMIC_FUNCTION----------------+ +-DYNAMIC_FUNCTION_CODE-----------+ +-MORE----------------------------+ +-NUMBER--------------------------+ +-ROW_COUNT-----------------------+ +-TRANSACTION_ACTIVE--------------+ +-TRANSACTIONS_COMMITTED----------+ '-TRANSACTIONS_ROLLED_BACK--------' connection-information-item |--+-CONNECTION_NAME---------+----------------------------------| +-DB2_AUTHENTICATION_TYPE-+ +-DB2_AUTHORIZATION_ID----+ +-DB2_CONNECTION_METHOD---+ +-DB2_CONNECTION_NUMBER---+ +-DB2_CONNECTION_STATE----+ +-DB2_CONNECTION_STATUS---+ +-DB2_CONNECTION_TYPE-----+ +-DB2_DYN_QUERY_MGMT -----+ +-DB2_ENCRYPTION_TYPE-----+ +-DB2_PRODUCT_ID----------+ +-DB2_SERVER_CLASS_NAME---+ '-DB2_SERVER_NAME---------'
condition-information-item |--+-CATALOG_NAME------------------+----------------------------| +-CLASS_ORIGIN------------------+ +-COLUMN_NAME-------------------+ +-CONDITION_IDENTIFIER----------+ +-CONDITION_NUMBER--------------+ +-CONSTRAINT_CATALOG------------+ +-CONSTRAINT_NAME---------------+ +-CONSTRAINT_SCHEMA-------------+ +-CURSOR_NAME-------------------+ +-DB2_ERROR_CODE1---------------+ +-DB2_ERROR_CODE2---------------+ +-DB2_ERROR_CODE3---------------+ +-DB2_ERROR_CODE4---------------+ +-DB2_INTERNAL_ERROR_POINTER----+ +-DB2_LINE_NUMBER---------------+ +-DB2_MESSAGE_ID----------------+ +-DB2_MESSAGE_ID1---------------+ +-DB2_MESSAGE_ID2---------------+ +-DB2_MESSAGE_KEY---------------+ +-DB2_MODULE_DETECTING_ERROR----+ +-DB2_NUMBER_FAILING_STATEMENTS-+ +-DB2_OFFSET--------------------+ +-DB2_ORDINAL_TOKEN_n-----------+ +-DB2_PARTITION_NUMBER----------+ +-DB2_REASON_CODE---------------+ +-DB2_RETURNED_SQLCODE----------+ +-DB2_ROW_NUMBER----------------+ +-DB2_SQLERRD_SET---------------+ +-DB2_SQLERRD1------------------+ +-DB2_SQLERRD2------------------+ +-DB2_SQLERRD3------------------+ +-DB2_SQLERRD4------------------+ +-DB2_SQLERRD5------------------+ +-DB2_SQLERRD6------------------+ +-DB2_TOKEN_COUNT---------------+ +-DB2_TOKEN_STRING--------------+ +-MESSAGE_LENGTH----------------+ +-MESSAGE_OCTET_LENGTH----------+ +-MESSAGE_TEXT------------------+ +-PARAMETER_MODE----------------+ +-PARAMETER_NAME----------------+ +-PARAMETER_ORDINAL_POSITION----+ +-RETURNED_SQLSTATE-------------+ +-ROUTINE_CATALOG---------------+ +-ROUTINE_NAME------------------+ +-ROUTINE_SCHEMA----------------+ +-SCHEMA_NAME-------------------+ +-SERVER_NAME-------------------+ +-SPECIFIC_NAME-----------------+ +-SUBCLASS_ORIGIN---------------+ +-TABLE_NAME--------------------+ +-TRIGGER_CATALOG---------------+ +-TRIGGER_NAME------------------+ '-TRIGGER_SCHEMA----------------'
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.
integer
SQL variables
or SQL parameters. The value specified must not be less than one or
greater than the number of available diagnosticsIf 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.
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,
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.
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_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;
integer
SQL variables or SQL parameters.
The value specified must not be less than one or greater than the
number of available diagnostics.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 does not 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:
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;