GET DIAGNOSTICS | Teradata Vantage - GET DIAGNOSTICS - Advanced SQL Engine - Teradata Database

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.

Data type: VARCHAR(128) CHARACTER SET LATIN

Default: null

COMMAND_FUNCTION_CODE

A number that uniquely identifies each command function.

Data type: INTEGER

Default: 0

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.

Data type: CHARACTER(1) CHARACTER SET LATIN

Default: N

N means that all conditions raised during SQL statement execution are stored in the Diagnostics Area.

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.

Data type: INTEGER

Default: 0

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.

Data type: INTEGER

Default: 0

TRANSACTION_ACTIVE

A code that indicates whether the transaction is currently active or not.

Data type: INTEGER

Default: 0

0 means the transaction is not currently active.

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.

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.

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.

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