TDWM EXCEPTIONS Request | Application Programming Reference | Vantage - 17.10 - TDWM EXCEPTIONS - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Application Programming Reference

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1090-171K
Language
English (United States)

Collects Teradata dynamic workload management software exception data from the database.

Input Data

Element Data Type Description
IndByte BYTE Indicator bits that specify which fields to treat as NULL if you are using indicator mode.

Each bit in the byte corresponds to one field in the input data.

If data is supplied for that field, set the bit to zero.

If the data for that field is NULL (that is, there is no data supplied for that field), set the bit to 1.

The IndByte field is only required if the CLIv2 request is submitted in indicator mode.
mon_ver_id SMALLINT

NOT NULL

MONITOR software version ID. This must be version 8 or later.

For a general explanation of monitor version choices, see MONITOR VERSION.

Monitor Privileges

To use this request, you must have the ABORTSESSION and MONSESSION privileges as part of your default role or both privileges must be granted directly to you.

For more information on roles and privileges, see:

Usage Notes

Before using this request, see Impact of Object Name Length on PM/API Requests.

This request allows an application to receive the Teradata dynamic workload management software exception information from the database. There is one record per exception.

CLIv2 Response Parcels

Parcel Sequence Parcel Flavor Length (Bytes) Comments/Key Parcel Body Fields
Success 8 18 to 273 StatementNo = 1

ActivityCount = 1

ActivityType = PCLTWMEXPSTMT 199

DataInfo 71 6 to 64100 Optional: this parcel is present if request was IndicData parcel.
Record 10
  • 5 to 64100(record mode)
  • 6 to 64100(indicator mode)
Depending on the request (Data or IndicData) data is returned in record or indicator mode. This is the only record returned
EndStatement 11 6 StatementNo = 2-byte integer
Success 8 18 to 273 Statement 2

ActivityCount = number of record parcels returned

ActivityType = PCLTDWMEXPSTMT 199

DataInfo 71 6 to 64100 Optional: this parcel is present if request was IndicData parcel.
Record 10
  • 5 to 64100 (record mode)
  • 8 to 64100 (indicator mode)
Depending on the request (Data or IndicData) data is returned in record or indicator mode. There is one Exception record returned for each Exceptions.
EndStatement 11 6 StatementNo = 2-byte integer
End Request 12 4 None

Response

Each of the statement types described below correspond to a ResultSet returned by the Teradata JDBC Driver, and each statement type field corresponds to a ResultSet column. For more information on ResultSets, see Teradata JDBC Driver Reference, available at https://teradata-docs.s3.amazonaws.com/doc/connectivity/jdbc/reference/current/frameset.html.

Statement 1

The first statement is a Record parcel format containing the collection duration data that is generated once for the whole system. The following table describes this Record format.

Field/Column Name Data Type Description
SampleSec SMALLINT Duration of the collection period, in seconds. This value represents the collection rate as entered in Teradata Viewpoint.

Statement 2

The second statement is a Record parcel format containing exception information for queries collected (and stored in a buffer) for the last collection period. There is one record for each exception. The following table describes this Record format.

Field/Column Name Data Type Description
VprocID SMALLINT Vproc the request with the exception was running on.
Query ID BIGINT Query ID for the query that encountered an exception.
UserName VARCHAR (128) CHARACTER SET UNICODE User name used in the query with the exception.
SessionID INTEGER Logical host ID of the query with the exception
RequestNum SMALLINT Request number of the query with the exception
LogicalHostID SMALLINT Logical host ID of the query with the exception.
AcctString VARCHAR (128) CHARACTER SET UNICODE Account string of the query with the exception.
WDID INTEGER WD ID the query with the exception was running in.
OpEnvID INTEGER Planned environment ID in force when the query encountered the exception.
SysConID INTEGER Health condition ID in force when the query encountered the exception.
ClassificationTime FLOAT Time the query with the exception was classified.
ClassificationDate INTEGER Date the query with the exception was classified.
ExceptionTime FLOAT Time the query encountered the exception.
ExceptionDate INTEGER Date the query encountered the exception.
ExceptionValue INTEGER Type of exception that occurred:
  • 0x00000001 - Exception time limit exceeded.
  • 0x00000002 - CPU time (AMP and PE) limit exceeded.
  • 0x00000004 - Blocked time limit exceeded.
  • 0x00000008 - Disk to CPU ratio exceeded.
  • 0x00000010 - AMP CPU skew limit exceeded.
  • 0x00000020 - AMP I/O count limit exceeded.
  • 0x00000040 - AMP I/O skew limit exceeded.
  • 0x00000080 - Max row count (for a step) exceeded.
  • 0x00000100 - Max row count (for a query) exceeded.
  • 0x00000200 - Spool space limit exceeded.
  • 0x00000400 - Number of AMPs used in query exceeded.
  • 0x00000800 - Disk CPU ratio value exceeded
  • 0x00001000 - I/O space value exceeded.
A conversion to hex is used to extract the bit values. For example, a value of 1024 converted to hex is 400.
ExceptionAction VARCHAR (10) CHARACTER SET LATIN Exception action taken by the exception handler:
  • A = Abort
  • C = Change WD

    NewWDId contains the new WD.

  • L = Log.
  • E = Execute Program

    ExProgram contains the program name.

  • T = Alert

    ExAlert contains the alert name.

  • N = No action

    This option cannot be combined with other actions. It disables exception detection.

  • S = Abort if the statement is a SELECT and no update has been done in the current transaction
  • Q = Insert a row to DBC.SystemQTbl

For example, CE stands for change WD and execute program.

NewWDID INTEGER WD the query was moved into if the ExceptionAction was change WD.
ExceptionCode INTEGER Database exception code.
ExceptionSubCode INTEGER Code for additional information.
This field is not currently used.
ErrorText VARCHAR (255) CHARACTER SET UNICODE Error text generated for the exception.
ExtraInfo VARCHAR (200) CHARACTER SET UNICODE Exception values noted at the time of the exception.
RuleID INTEGER Teradata dynamic workload management software rule ID of the rule with the exception handling directive.
WarningOnly VARCHAR (1) CHARACTER SET LATIN Indicator that this is s a warning only.
RejectionCat SMALLINT Teradata dynamic workload management software category this query was rejected from.

Sample Input - CLIv2 Request

The following example shows how the parcels for a TDWM EXCEPTIONS request, built by CLIv2, appear when sent to the database server.

In this example, the size of the response buffer in the example is set at the maximum (64,000 bytes), although you can set it to any size.
Flavor Length Body
Num Name Bytes Field Value
0001 Req 20 Request TDWM EXCEPTIONS
0003 Data 6 MonVerID 8
0004 Resp 6 Buffer Size 64000

Sample Input - Teradata JDBC Driver Request

For an example of how the PM/API request, built in Java, appears when sent to the database server, see Teradata JDBC Driver Reference, available at https://teradata-docs.s3.amazonaws.com/doc/connectivity/jdbc/reference/current/frameset.html.

Sample Output

Submitting request TDWM EXCEPTIONS;
TDWM EXCEPTION successful.
        Sample Seconds: 60
TDWM Exceptions - # items: 3
User Name Req  Cat WDID  ExcptDate   ExcptTime     Code   NewWD    QueryID              ErrorText
--------- ---- --- ----  ---------   ----------    ----   -----    ------------------   ---------------
LUMBER     2    0  11    2009/09/28  11:05:43.00   3156            163837185173601208   CPUTime: 687ms.
LUMBER     2    0  11    2009/09/28  11:05:57.00   3156            163837185173601276   CPUTime: 1501ms.
LUMBER     2    0  11    2009/09/28  11:05:57.00   3156            163837185173601276   I/O: 1720.