Diagnostics Area | Teradata Vantage - Diagnostics Area - Advanced SQL Engine - Teradata Database

SQL Stored Procedures and Embedded SQL
Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-28
dita:mapPath
vqj1592443206677.ditamap
dita:ditavalPath
vqj1592443206677.ditaval
dita:id
B035-1148
lifecycle
previous
Product Category
Teradata Vantage™

The Diagnostics Area is a system-managed data structure that contains information about the execution status of the statements within an SQL stored procedure. You can use the GET DIAGNOSTICS statement to extract the information from the Diagnostics Area.

The Diagnostics Area is divided into two components:
  • One Statement Area
  • Zero, one, or as many as 16 Condition Areas

The Statement Area, sometimes referred to as the Header, contains information about the last statement within the stored procedure.

The Condition Area, sometimes referred to as the Detail Area, contains information about each error, warning, or success code that resulted from the execution of the statement documented in the Statement Area.

The Diagnostics Area is not affected by the following statements:
  • BEGIN END
  • DECLARE
  • GET DIAGNOSTICS
  • ITERATE
  • LEAVE
  • LOOP
The Diagnostics Area is affected by the following statements only for error and warning conditions. These statements first clear the Diagnostics Area and then insert information into it about the error or warning condition raised during the execution of the statement.
  • CASE
  • FOR
  • IF
  • REPEAT
  • SET
  • WHILE

Rules

The following rules apply to the Diagnostics Area:
  • Only one Diagnostics Area is associated with each session.
  • The Diagnostics Area is emptied (cleared) before the execution of a client-invoked stored procedure.
  • The maximum number of conditions that can be stored in the Condition Area of the Diagnostics Area is 16.
  • Vantage empties the Diagnostics Area before the execution of all SQL statements except CALL.

    After a statement executes, Vantage populates the Statement Area (and, if any conditions are raised, the first Condition Area) with data about the statement and any conditions raised during its execution, respectively.

  • Vantage does not empty the Diagnostics Area before the execution of a CALL statement within an SQL stored procedure, nor does it modify the Diagnostics Area after the execution of a CALL statement.

    The content of the Diagnostics Area remains as it was at the end of the execution of the invoked SQL stored procedure.

  • Vantage empties the Diagnostics Area after the execution of any CASE, FOR, IF, REPEAT, SET, or WHILE statement if an exception or completion condition is raised during the execution of that statement.

    The system populates the Statement Area and the first Condition Area with data about that statement and the raised condition. The successful completion of any of these statements does not affect the Diagnostics Area.

  • If a statement within a handler other than GET DIAGNOSTICS returns an exception or a user-defined condition, and the condition is not dealt with by the handler, then Vantage implicitly submits a RESIGNAL statement.

    This action empties the Diagnostics Area, and Vantage restores the original condition with which the handler was invoked to the Diagnostics Area. The system then propagates the condition in Condition Area 1 outside the compound statement containing the handler.

    A RESIGNAL statement can add a maximum of 16 Condition Areas to the Diagnostics Area.

    The NUMBER option in a RESIGNAL statement area indicates the number of conditions stored in the Diagnostics Area.

    If you attempt to store more than 16 conditions in the Diagnostics Area, the value for NUMBER does not increment, and the MORE field in the Statement Area is set to Y.

Structure of the Diagnostics Area

The following table lists the statement information items.

Field Description Data Type Default Attribute
COMMAND_FUNCTION An identifying text string for the executed SQL statement. VARCHAR(128) CHARACTER SET LATIN null READ_ONLY
COMMAND_FUNCTION_CODE A number identifying the SQL statement executed.

Positive values are reserved for SQL statements defined by the ISO/IEC 9075 SQL standard, while negative values are reserved for Teradata-defined SQL statements.

 INTEGER 0 READ_ONLY
MORE A code to indicate whether all the conditions raised during the execution of the SQL statement are stored in the Diagnostics Area or not.
Codes are:
  • N, all the conditions that were raised during execution of the SQL statement have been stored in the Diagnostics Area.
  • Y, more conditions were raised during execution of the SQL statement than there are Condition Areas in the Diagnostics Area.
 CHARACTER(1) CHARACTER SET LATIN N READ_ONLY
NUMBER The number of exception or completion conditions that has been stored in the Diagnostics Area as a result of executing the previous SQL statement (with the exception of a preceding GET DIAGNOSTICS statement). INTEGER 0 READ_ONLY
ROW_COUNT The number of rows affected by executing a searched DELETE request, INSERT request, a MERGE request, a searched UPDATE request, or as a direct result of executing the previous SQL statement. INTEGER 0 READ_ONLY
TRANSACTION_ACTIVE A code indicating whether the transaction is currently active or not.
Codes are:
  • 0, transaction is not currently active.
  • 1, transaction is currently active.
 INTEGER 0 READ_ONLY

The following table lists the condition information items.

Field Description Data Type Default Attribute
CLASS_ORIGIN An identifier for the naming authority that defined the class value of RETURNED_SQLSTATE.
Codes are:
  • ISO-9075 (ANSI-defined)
  • Teradata (Teradata-defined)
 VARCHAR(128) CHARACTER SET UNICODE null Modifiable for condition names that are not associated with an SQLSTATE value.

For these cases, the value can be any string except ISO-9075 or Teradata.
CONDITION_IDENTIFIER The condition name specified in a SIGNAL or RESIGNAL statement. VARCHAR(128) CHARACTER SET UNICODE null READ_ONLY
CONDITION_NUMBER A sequence number to identify each Condition Information Item (detail) area in the Diagnostics Area. INTEGER 0 READ_ONLY
MESSAGE_LENGTH The length in characters of the character string value in MESSAGE_TEXT. INTEGER 0 READ_ONLY
MESSAGE_TEXT If the value of RETURNED_SQLSTATE corresponds to any of the bulleted items in the following list, then the value for MESSAGE_TEXT is the message text item of the SQL-invoked routine that raised the exception:
  • External routine invocation exception
  • External routine exception
  • SQL routine exception
  • Warning
 VARCHAR(128) CHARACTER SET UNICODE null Modifiable
RETURNED_SQLSTATE The SQLSTATE parameter that would have been returned if this were the only completion or exception condition possible. CHARACTER(5) CHARACTER SET LATIN null READ_ONLY
SUBCLASS_ORIGIN An identifier for the naming authority that defined the subclass value of RETURNED_SQLSTATE.
Codes are:
  • ISO-9075, (ANSI-defined)
  • Teradata (Teradata-defined)
 VARCHAR(128) CHARACTER SET UNICODE null Modifiable for condition names that are not associated with an SQLSTATE value.

For these cases, the value can be any string except ISO-9075 or Teradata.

Related Information