15.10 - MONITOR AWT RESOURCE - Teradata Database

Teradata Database Application Programming Reference

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
featnum
B035-1090-151K

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

 

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.

Note: The IndByte field is only required if the CLIv2 request is submitted in indicator mode.

mon_ver_id

SMALLIN,
NOT NULL

MONITOR software version ID. This can be version 2 or later.

For a general explanation of monitor version choices, see “MONITOR VERSION” on page 162.

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” on page 81.

Note: You can define up to four thresholds for categorizing AMPs.

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:

  • Database Administration
  • Security Administration
  • Teradata JDBC Driver User Guide
  • 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.

    The MONITOR AWT RESOURCE request is treated internally as a one statement request that generates two responses. The statement response returned from Teradata 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” on page 79.

    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” on page 80.

    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” on page 81.

    EndStatement

    11

    6

    StatementNo = 3‑byte integer=1

    EndRequest

    12

    4

    None

    Note: 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.

    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

    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.

    The following example shows how the parcels for a MONITOR AWT RESOURCE request, built by CLIv2, appear when sent to the Teradata Database server.

    Note: 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

    For an example of how the PM/API request, built in Java, appears when sent to the Teradata Database server, see Teradata JDBC Driver Reference.

    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.

    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.

    Note: 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