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.
- 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.
- BEGIN END
- DECLARE
- GET DIAGNOSTICS
- ITERATE
- LEAVE
- LOOP
- CASE
- FOR
- IF
- REPEAT
- SET
- WHILE
Rules
- 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:
|
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:
|
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:
|
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:
|
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:
|
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
- GET DIAGNOSTICS statement, see GET DIAGNOSTICS.
- RESIGNAL statement, see RESIGNAL.
- Command functions and their associated codes, see Command Function Code Values.