MONITOR AWT RESOURCE Request | Application Programming Reference | Vantage - MONITOR AWT RESOURCE - Advanced SQL Engine - Teradata Database

Application Programming Reference

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
cpn1571792172880.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1090
lifecycle
previous
Product Category
Teradata Vantage™

Purpose

Collects statistics on AMPs based on the in-use AMP Worker Tasks (AWTs).

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 can be version 2 or later.

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

Threshold 1 SMALLINT

NOT NULL

Minimum value for in-use AWTs to qualify an AMP for inclusion into this interval.
Threshold 2 SMALLINT Start value for in-use AWTs to qualify an AMP for inclusion into this interval.
Threshold 3 SMALLINT Start value for in-use AWTs to qualify an AMP for inclusion into this interval.
Threshold 4 SMALLINT Start value for in-use AWTs to qualify an AMP for inclusion into this interval.
Summary SMALLINT

NOT NULL

0 = Return no record parcels in statement 2.

1 = Return the record parcels in statement 2, which are the data fields returned in Groups I through V only.

Detail SMALLINT

NOT NULL

0 = Return no detail information.

1 = Return AMP worker task usage information by each AMP.

If Detail is enabled, a third statement is returned containing AMP worker task usage information by each AMP. If Detail is not enabled, a statement is returned with no record parcels. For information on the record parcels returned, see Statement 3 - MONITOR AWT RESOURCE.

You can define up to four thresholds for categorizing AMPs.

Monitor Privileges

To use this request, you must have the MONRESOURCE privilege as part of your default role or this privilege must be granted directly to you.

For more information on roles and privileges, see:

Usage Notes - MONITOR AWT RESOURCE

This interface provides a snapshot of AMP utilization based on the number of AWTs in use within the MSGWORKNEW and MSGWORKONE message work types. These two message work types are a barometer for AMP activity/health. The MONITOR AWT RESOURCE request allows the specification of four thresholds which are used to categorize AMPs. AMPs are counted into categories defined by these thresholds and based on the AWT usage of the AMP.

This request also indicates the number of AMPs currently in some form of Flow Control. Flow Control can be defined at a node level and, therefore, may not have a system-wide consistent definition. This indication is applicable only to those messages to which Flow Control is applicable.

MONITOR AWT RESOURCE provides information on the AMPs having the highest and lowest in-use counts within the system. When duplicate in-use counts exist, the AMP information returned is for the AMPs with the largest VProc ID.

Thresholds must be defined in ascending order and cannot contain gaps. If a gap is detected, an error message is returned.

Thresholds that are not used must be set to -1 (or NOT NULL). The minimum valid threshold value is zero.

CLIv2 Response Parcels

The MONITOR AWT RESOURCE request is treated internally as a one statement request that generates two responses. The statement response returned from the database contains the following sequence of parcel types.

Parcel Sequence Parcel Flavor Length

(Bytes)

Comments and Key Parcel Body Fields
Success 8 18 to 273 StatementNo = 1

ActivityCount = 1

ActivityType = 175 (PCLMONAWTRESSTMT)

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 record contains information in StatementNo-1. For an example of this record, see Statement 1.
EndStatement 11 6 StatementNo = 2-byte integer=1
Success 8 18 to 273 StatementNo = 2

ActivityCount = 1

ActivityType = 175 (PCLMONAWTRESSTMT)

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 record contains information in StatementNo-2. For an example of this record, see Statement 2.
EndStatement 11 6 StatementNo = 2-byte integer=1
EndRequest 12 4 None
Success 8 18 to 273 StatementNo = 3

ActivityCount = 1

ActivityType = 175 (PCLMONAWTRESSTMT)

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 record contains information in StatementNo-3. For an example of this record, see Statement 3 - MONITOR AWT RESOURCE.
EndStatement 11 6 StatementNo = 3-byte integer=1
EndRequest 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 .

The MONITOR AWT RESOURCE request returns the following information: the number of AWTs in use within the MSGWORKNEW and MSGWORKONE message work types; the number of AMPs currently in some form of Flow Control; and the number of AMPs having the highest and lowest in-use counts within the system.

The output returns three statements:
  • The first statement provides information about the collection rate.
  • The second statement provides information about AMPs and the AMPs AWT distribution across the defined thresholds.
  • The third statement provides AMP worker task usage information by each AMP.

Statement 1

The first statement is a Record parcel format containing:

Field/Column Name Data Type Description
SampleSec INTEGER

NOT NULL

Duration of the collection period, in seconds. This field contains the Monitor resource collection rate (ResMonitor).
CollectionDate DATE

NOT NULL

Date the Monitor AWT cache was last refreshed.
CollectionTime FLOAT

NOT NULL

Time the Monitor AWT cache was last refreshed.

To avoid excessive AWT data collection, Monitor AWT Resource checks if the data in the buffer is still valid in relation to the value set for SampleSec (ResMonitor rate) and only collects new data if it has expired.

Statement 2

The second statement is a Record parcel format containing information about AMPs and the AMPs AWT distribution across the defined thresholds.

Field/Column Name Data Type Description
Interval 1 Count INTEGER Number of AMPs in the system whose in-use AWT counts fall at, or above, the Threshold 1 value and do not qualify for the higher thresholds.
Interval 2 Count INTEGER Number of AMPs in the system whose in-use AWT counts fall at, or above, the Threshold 2 value and do not qualify for the higher thresholds.
Interval 3 Count INTEGER Number of AMPs in the system whose in-use AWT counts fall at, or above, the Threshold 3 value and do not qualify for the higher thresholds.
Interval 4 Count INTEGER Number of AMPs in the system whose in-use AWT counts fall at, or above, the Threshold 4 value and do not qualify for the higher thresholds.
Flow Control INTEGER Number of AMPs currently in some form of Flow Control.
High AMP 1 VprocId INTEGER Vproc ID of the AMP with the highest in-use count in the system. A value of -1 (or NULL) indicates this field is not defined.
High AMP 1 In-Use Count INTEGER In-use count associated with the AMP 1 Vproc ID. This is only applicable if AMP 1 is defined.
High AMP 2 VprocId INTEGER Vproc ID of the AMP with the next highest in-use count in the system. A value of -1 (or NULL) indicates this field is not defined.
High AMP 2 In-Use Count INTEGER In-use count associated with the AMP 2 Vproc ID. This is only applicable if AMP 2 is defined.
High AMP 3 VprocId INTEGER Vproc ID of the AMP with the next highest in-use count in the system. A value of -1 (or NULL) indicates this field is not defined.
High AMP 3 In-Use Count INTEGER In-use count associated with the AMP 3 Vproc ID. This is only applicable if AMP 3 is defined.
High AMP 4 VprocId INTEGER Vproc ID of the AMP with the next highest in-use count in the system. A value of -1 (or NULL) indicates this field is not defined.
High AMP 4 In-Use Count INTEGER In-use count associated with the AMP 4 Vproc ID. This is only applicable if AMP 4 is defined.
High AMP 5 VprocId INTEGER Vproc ID of the AMP with the next highest in-use count in the system. A value of -1 (or NULL) indicates this field is not defined.
High AMP 5 In-Use Count INTEGER In-use count associated with the AMP 5 Vproc ID. This is only applicable if AMP 5 is defined.
Low AMP 1 VprocId INTEGER Vproc ID of the AMP with the lowest in-use count in the system. A value of -1 (or NULL) indicates this field is not defined.
Low AMP 1 In-Use Count INTEGER In-use count associated with the AMP 1 Vproc ID. This is only applicable if AMP 1 is defined.
Low AMP 2 VprocId INTEGER Vproc ID of the AMP with the lowest in-use count in the system. A value of -1 (or NULL) indicates this field is not defined.
Low AMP 2 In-Use Count INTEGER In-use count associated with the AMP 2 Vproc ID. This is only applicable if AMP 2 is defined.
Low AMP 3 VprocId INTEGER Vproc ID of the AMP with the lowest in-use count in the system. A value of -1 (or NULL) indicates this field is not defined.
Low AMP 3 In-Use Count INTEGER In-use count associated with the AMP 3 Vproc ID. This is only applicable if AMP 3 is defined.
Low AMP 4 VprocId INTEGER Vproc ID of the AMP with the lowest in-use count in the system. A value of -1 (or NULL) indicates this field is not defined.
Low AMP 4 In-Use Count INTEGER In-use count associated with the AMP 4 Vproc ID. This is only applicable if AMP 4 is defined.
Low AMP 5 VprocId INTEGER Vproc ID of the AMP with the lowest in-use count in the system. A value of -1 (or NULL) indicates this field is not defined.
Low AMP 5 In-Use Count INTEGER In-use count associated with the AMP 5 Vproc ID. This is only applicable if AMP 5 is defined.
  • Flow Control 1 VprocId,
  • Flow Control 2 VprocId,
  • Flow Control 3 VprocId,
  • Flow Control 4 VprocId,
  • Flow Control 5 VprocId
INTEGER Vproc ID of the AMP in flow control. A value of -1 (or NULL) indicates this field is not defined.

Statement 3 - MONITOR AWT RESOURCE

The third statement is a Record parcel format containing AMP worker task usage information by each AMP.

Field/Column Name Data Type Description
VprocNo SMALLINT

NOT NULL

AMP vproc number.
AvailableAWTs SMALLINT

NOT NULL

Number of available AMP worker tasks in each AMP.
InUseAWTs SMALLINT

NOT NULL

Number of active AMP worker tasks in each AMP.
MsgCount INTEGER

NOT NULL

Number of messages currently queued for delivery to each AMP.
DQMsgCount INTEGER

NOT NULL

Number of messages processed by each AMP.
AvailableAWTsForAll SMALLINT The number of available AMP worker tasks in the unreserved pool in each AMP.

Sample Input - CLIv2 Request

The following example shows how the parcels for a MONITOR AWT RESOURCE 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. However, the minimum size is 32,000 bytes.
Flavor Length Body
Num Name Bytes Field Value
0001 Req 16 Request MONITOR AWT RESOURCE
0003 Data 57
  • MonVerId
  • Threshold 1
  • Threshold 2
  • Threshold 3
  • Threshold 4
  • Summary
  • Detail
  • 6
  • 10
  • 12
  • 14
  • 15
  • 1
  • 0
0004 Resp 6 BufferSize 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

The MONITOR AWT RESOURCE request returns values approximately as shown below when TASM Workloads are enabled and the following input data is specified:

Threshold 1 = 10
Threshold 2 = 12
Threshold 3 = 14
Threshold 4 = 15
    Summary = 1
     Detail = 1

For information on TASM rules, see Teradata® Viewpoint User Guide, B035-2206.

The MONITOR AWT RESOURCE request commonly returns values in text character format. Your application program may return the values in a different format or display.

You can rename the SampleSec field in your application. In the output below, the SampleRate value is the SampleSec value.

Pay attention to SampleRate when interpreting the results of this request.

SampleRate: 30
Collection Date/Time:  06/15/2011 18:32:44.00
SUCCESS parcel: 
StatementNo=2,    ActivityCount=1,
ActivityType=175, FieldCount=30
Intervals:
             1       2       3       4
             4       0       0       0
Flow Control = 0
*** HIGH AMP ***
                    1       2       3       4       5
VprocId:            3       2       1       0      -1
InUseCount:         1       1       1       1       0
*** LOW AMP ***
                    1       2       3       4       5
VprocId:            3       2       1       0      -1
InUseCount:         1       1       1       1       0
*** FLOW CONTROL INFO ***
                    1       2       3       4       5
VprocId            -1      -1      -1      -1      -1
SUCCESS parcel: 
StatementNo=3,    ActivityCount=4,
ActivityType=175, FieldCount=5
VProcNo   AvailableAWTs   InUseAWTs   MsgCount   DQMsgCount
-------   -------------   ---------   --------   ----------
      0              49           2          0       111135
      1              49           1          0       124561
      2              49           1          0       156017
      3              49           1          0       122913