Purpose
GET DIAGNOSTICS retrieves information about successful, exception, or completion conditions from the Diagnostics Area.
Invocation
Executable.
Stored procedures only.
Syntax
GET DIAGNOSTICS [ EXCEPTION condition_number ] diagnostic_spec [,...] ;
where:
- diagnostic_spec
-
{ parameter_name | variable_name } = statement_information_item
- parameter_name
- A parameter whose value is set to the value contained in statement_information_item.
- variable_name
- A variable whose value is set to the value contained in statement_information_item.
- EXCEPTION
- A language element that indicates to return information from the Condition Area of the Diagnostics Area.
- condition_number
- A number, parameter, or variable that resolves to the number of the Condition Area from which information is to be retrieved.
- parameter_name
- An output parameter to which the condition_information_item retrieved from the specified Condition Area is assigned.
- variable_name
- An output variable to which the condition_information_item retrieved from the specified Condition Area is assigned.
- condition_information_ item
- The name of the Condition Area field from which condition information is to be retrieved.
- statement_information_item
- One of the following field names from the Statement Area of the Diagnostics Area as shown in the following table.
- COMMAND_FUNCTION
- An identifying text string for the executed SQL statement.
- COMMAND_FUNCTION_CODE
- A number that uniquely identifies each command function.
- MORE
- A code that indicates whether all the conditions raised during the execution of the SQL statement are stored in the Diagnostics Area or not.
- NUMBER
- The number of exception of completion conditions that has been stored in the Diagnostics Area as a result of executing the previous SQL statement.
- ROW_COUNT
- The number of rows affected by executing a searched DELETE request, an INSERT request, a MERGE request, or a searched UPDATE request; or as a direct result of executing the previous SQL statement.
- TRANSACTION_ACTIVE
- A code that indicates whether the transaction is currently active or not.
Usage Notes
If you specify a statement information item in a GET DIAGNOSTICS statement, Teradata Database retrieves the requested information from the Statement Area into the simple target specification.
If you specify an EXCEPTION in the GET DIAGNOSTICS statement, Teradata Database retrieves the requested condition information item from the Condition Area corresponding to the condition number from the Diagnostics Area into the simple target specification.
GET DIAGNOSTICS statements do not change the contents of the Diagnostics Area. If GET DIAGNOSTICS raises an exception condition, only the status variables SQLSTATE, SQLCODE, and ACTIVITY_COUNT are set.
Rules
The following rules apply to GET DIAGNOSTICS.
- NULL
- A value < 1
- A value > 16, which is the maximum number of Condition Areas that can be stored in the Diagnostics Area.
- NULL
- A value < 1
- A value > the number of conditions stored in the Diagnostics Area when the GET DIAGNOSTICS statement is executed
If a GET DIAGNOSTICS statement specifies an EXCEPTION and does not violate either of the previous rules, Teradata Database retrieves the information from the Condition Area with the specified condition number.
- COMMAND_FUNCTION
- COMMAND_FUNCTION_CODE
- MORE
- NUMBER
- ROW_COUNT
- TRANSACTION_ACTIVE
- CLASS_ORIGIN
- CONDITION_IDENTIFIER
- CONDITION_NUMBER
- MESSAGE_LENGTH
- MESSAGE_TEXT
- RETURNED_SQLSTATE
- SUBCLASS_ORIGIN
Otherwise, the request aborts during compilation and returns an error to the requestor.
If the Diagnostics Area is empty, as would happen if GET DIAGNOSTICS is the first statement in a client-invoked stored procedure and the statement information is requested, the default values for the requested statement information items are returned.
The declared data type of value specified in a statement information item or condition information item must be compatible with the data type of the corresponding statement or condition information item name.
Otherwise, the requests aborts during compilation and returns an error.
The table on the following page describes the compatibility rules applicable to valid data types of a value specification. The ÷ symbol in a cell indicates that the combination is compatible, and a blank cell indicates that the combination is not compatible.
Data Type | CHAR ACTER | VAR CHAR | INTEGER | BYTEINT | SMALL INT | BIG INT | DECIMAL (n,0) | NUMERIC (n,0) |
---|---|---|---|---|---|---|---|---|
CHAR ACTER | ÷ | ÷ | ||||||
VAR CHAR | ÷ | ÷ | ||||||
INTEGER | ÷ | ÷ | ÷ | ÷ | ÷ | ÷ | ||
BYTEINT | ÷ | ÷ | ÷ | ÷ | ÷ | ÷ | ||
SMALL INT | ÷ | ÷ | ÷ | ÷ | ÷ | ÷ | ||
BIGINT | ÷ | ÷ | ÷ | ÷ | ÷ | ÷ | ||
DECIMAL (n,0) | ÷ | ÷ | ÷ | ÷ | ÷ | ÷ | ||
NUMERIC (n,0) | ÷ | ÷ | ÷ | ÷ | ÷ | ÷ |
Example: Using the Statement Information Item in a GET DIAGNOSTICS Statement
The following example illustrates the usage of the statement information item field ROW_COUNT in a GET DIAGNOSTICS statement. During the execution of the procedure, the GET DIAGNOSTICS statement sets the rowcount parameter to zero. The CALL statement returns OParam = 0 and rowcount = 0.
CREATE PROCEDURE getdiag1 (OUT OParam INTEGER, INOUT rowcount INTEGER) BEGIN SELECT c1 INTO OParam FROM tab1; -- Returns warning NODATA FOUND GET DIAGNOSTICS rowcount = ROW_COUNT; IF (rowcount = 0) THEN SET OParam = 0; END IF; END; BTEQ> CREATE SET TABLE tab1 (c1 INTEGER); BTEQ> CALL getdiag1(OParam, NULL);
Example: Retrieving Information in the Diagnostics Area Using RETURNED_SQLSTATE
The following example illustrates the retrieval of information related to the completion condition in the Diagnostics Area using the statement information item field RETURNED_SQLSTATE. This also illustrates that the condition number, pcondno, is not null in this example.
During the execution of the procedure, the SELECT … INTO statement clears the Diagnostics Area before it executes and raises the completion condition SQLSTATE '02000'.
The Statement Area is updated and a Condition Area is added to the Diagnostics Area with the information related to the completion condition.
SQLSTATE '02000' is propagated to the outer compound statement. The CONDITION HANDLER in compound statement cs1 handles the completion condition with SQLSTATE '02000'.
The GET DIAGNOSTICS statement retrieves Condition Area 1 from the Diagnostics Area and assigns the RETURNED_SQLSTATE value '02000' to sqlstate1.
The CALL statement returns OParam1 = 0, pcondno = 1, and sqlstate1 = '02000'.
CREATE PROCEDURE getdiag5 (OUT OParam1 INTEGER, INOUT pcondno INTEGER, OUT sqlstate1 CHARACTER(5)) cs1 :BEGIN DECLARE nodata CONDITION FOR SQLSTATE '02000'; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' GET DIAGNOSTICS EXCEPTION pcondno sqlstate1 = RETURNED_SQLSTATE; cs2: BEGIN SET OParam1 = 0; SELECT c1 INTO OParam1 FROM tab1; -- Returns warning NO DATA FOUND END cs2; END cs1; BTEQ> CREATE SET TABLE tab1 (c1 INTEGER); BTEQ> CALL getdiag5(OParam1,1,sqlstate1);
Example: Using TRANSACTION_ACTIVE in a GET DIAGNOSTICS Statement in Teradata Session Mode
The following example illustrates the usage of the statement information item field TRANSACTION_ACTIVE in a GET DIAGNOSTICS statement in Teradata session mode.
In this example, the procedure is created in Teradata session mode.
During its execution, a duplicate row exception is raised because of the second INSERT statement, so the system rolls back the transaction.
The CONTINUE HANDLER is invoked and the GET DIAGNOSTICS statement retrieves the TRANSACTION_ACTIVE and COMMAND_FUNCTION statement information item fields from the Statement Area of the Diagnostics Area.
When the procedure finishes executing, OParam has the value 0 because there is no transaction active when the GET DIAGNOSTICS statement is submitted and Stmt has the string 'INSERT'.
CREATE PROCEDURE getdiag3 (OUT OParam INTEGER, OUT Stmt CHARACTER(40)) BEGIN DECLARE CONTINUE HANDLER FOR SQLSTATE '23505' GET DIAGNOSTICS OParam = TRANSACTION_ACTIVE, Stmt = COMMAND_FUNCTION; INSERT INTO Tab1 VALUES(100); INSERT INTO Tab1 VALUES(100); END; BTEQ> CREATE SET TABLE tab1 (c1 INTEGER); BTEQ> CALL getdiag3(OParam, Stmt);
Example: Using TRANSACTION_ACTIVE in a GET DIAGNOSTICS Statement for a Procedure Created in ANSI Session Mode
The following example illustrates the usage of the statement information item field TRANSACTION_ACTIVE in a GET DIAGNOSTICS statement for a procedure created in ANSI session mode.
During the execution of the procedure, the duplicate row exception raised because of the second INSERT statement does not roll back the transaction because duplicate rows are permitted in ANSI session mode.
The CONTINUE HANDLER is invoked and the GET DIAGNOSTICS statement sets Oparam with the value of the TRANSACTION_ACTIVE field from the Statement Area.
When the procedure finishes executing, OParam has the value 1 because the transaction is active when the GET DIAGNOSTICS statement is submitted.
CREATE PROCEDURE getdiag4 (OUT OParam INTEGER) BEGIN DECLARE CONTINUE HANDLER FOR SQLSTATE '23505' GET DIAGNOSTICS OParam = TRANSACTION_ACTIVE; INSERT INTO Tab1 VALUES(100); INSERT INTO Tab1 VALUES(100); END; BTEQ> CALL getdiag4(OParam);
Example: Runtime Behavior of a GET DIAGNOSTICS Statement When the Diagnostics Area is Empty
The following example illustrates the runtime behavior of a GET DIAGNOSTICS statement when the Diagnostics Area is empty and a statement information item, in this case ROW_COUNT, is requested.
During the execution of the procedure, the first statement executed is the GET DIAGNOSTICS statement. Because the Diagnostics Area is empty at the beginning of the client-invoked stored procedure execution, the rowcount parameter is set to the default value of ROW_COUNT, which is zero.
CREATE PROCEDURE getdiag3 (OUT rowcount INTEGER) BEGIN GET DIAGNOSTICS rowcount = ROW_COUNT; … END; BTEQ> CALL getdiag3(rowcount);
Related Topics
- Valid condition information item names, see the condition_name Syntax Element in the Syntax table for SIGNAL.
- GET DIAGNOSTICS statement, see Example: Retrieving Information in the Diagnostics Area Using RETURNED_SQLSTATE.
- The Diagnostics Area, see Example: Runtime Behavior of a GET DIAGNOSTICS Statement When the Diagnostics Area is Empty.