15.10 - TDWM SUMMARY - Teradata Database

Teradata Database Application Programming Reference

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

Returns the Teradata dynamic workload management software WD summary data fields.

 

Field Name

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

SMALLINT,
NOT NULL

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

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

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:

  • Database Administration
  • Security Administration
  • Teradata JDBC Driver User Guide
  • There is one record per WD active in the system. The record contains counts of the number of queries that were classified into the WD in the collection period and the number of queries that completed in the collection period along with query statistics.

    The TDWM Summary request returns information for all queries completed in the dashboard interval for the WDs.

    If TASM Workloads are not enabled, only the first statement is returned.

    The TDWM SUMMARY request is treated internally as a two statement request, with each statement generating a response. The two statement response returned from Teradata Database contains the following sequence of parcel types:

     

    Parcel Sequence

    Parcel Flavor

    Length (Bytes)

    Comments/Key Parcel Body Fields

    Success

    8

    18 to 273

    StatementNo = 1

    ActivityCount = 1

    ActivityType = 156 (PCLTWMSUMMARYSTMT)

    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

    StatementNo = 2

    ActivityCount = number of record parcels returned

    ActivityType = 134 (PCLTDWMSUMSTMT)

    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. There is one Summary Data record returned for each active WD. The format of this record is described below.

    EndStatement

    11

    6

    StatementNo = 2-byte integer

    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.

    Statement 1

    The response to the first statement results in a Record parcel containing the SampleSec field.

     

    Field/Column Name

    Data Type

    Description

    SampleSec

    SMALLINT

    Duration of the collection period, in seconds. This value represents the collection rate.

    Statement 2

    The response to the second statement results in a Record parcel that consists of summary information for an active WD in the system collected in the last collection period. There is one record for each WD.

    Note: If TASM Workloads are not enabled, this statement is not returned.

    The following table describes the format of the Summary Data Record parcel.

     

    Field/Column Name

    Data Type

    Description

    WD ID

    INTEGER

    WD ID.

    Arrivals

    INTEGER

    Number of queries that were classified into this WD by theTeradata dynamic workload management software.

    Completions

    INTEGER

    Number of queries that completed in this WD in this dashboard or logging interval with:

  • No error code or an error code of 3158
  • No abort flag
  • Either an AMP count of > 0 or a Parser CPU > 0
  • Minimum Response Time

    FLOAT

    Minimum response time in centiseconds of any query.

    Maximum Response Time

    FLOAT

    Maximum response time in centiseconds for any query.

    Average Response Time

    FLOAT

    Average response time in centiseconds for this workload based on the total response time of all completions divided by number of completions.

    Minimum CPU Time

    FLOAT

    Minimum CPU in milliseconds of all the queries that completed in this workload in this dashboard or logging interval, where CPU is the minimum CPU used by any one AMP of the query plus the Parser CPU time used by that same query.

    Maximum CPU Time

    FLOAT

    Maximum CPU in milliseconds of all the queries that completed in this workload in this dashboard or logging interval, where CPU is the maximum CPU used by any one AMP of the query plus the Parser CPU time used by that same query.

    Average CPU Time

    FLOAT

    Running total in milliseconds of Parser CPU time plus the total AMP CPU of each query that completed in this workload divided by the number of completions of queries in this workload in this dashboard or logging interval.

    Delayed Count

    INTEGER

    Number of queries that are delayed in this WD.

    Average Delay Time

    FLOAT

    Average delay time in centiseconds for all queries assigned to this WD.

    Exception Count

    INTEGER

    Number of queries with exceptions in this WD.

    Met SLG Count

    INTEGER

    Number of queries that met WD Response Time SLG requirements.

    Note: If the WD does not have Response Time SLG requirement, the query is still counted as met.

    Active Query Count

    INTEGER

    Number of active queries assigned to this WD.

    Active Delayed Count

    INTEGER

    Number of currently delayed queries in this WD.

    Error Count

    INTEGER

    Number of queries that finished in this WD in this dashboard or logging interval with an error code > 0 but not 3158 or 3156.

    Abort Count

    INTEGER

    Number of queries that finished in this WD in this dashboard or logging interval with an error code of 0 or 3156 and an abort flag.

    Other Count

    INTEGER

    Number of queries that finished in this WD in this dashboard or logging interval with no:

  • Error code
  • Abort flag
  • AMP count
  • Parser CPU
  • CollectionDate

    DATE

    Date of the collection of summary information as pulled from the StartCollectTime field on the control vproc.

    CollectionTime

    FLOAT

    Time of the collection of summary information as pulled from the StartCollectTime field on the control vproc.

    VirtualPartNum

    INTEGER

    Virtual Partition number of the WD.

    AvgIOWaitTime

    FLOAT

    Average duration of sleeps in milliseconds due to the I/O rate handling over the life of all requests completing in this WD during this interval.

    MaxIOWaitTime

    FLOAT

    Maximum duration of sleeps in milliseconds due to the I/O rate handling of a request completing in this WD during this interval.

    AvgOtherWaitTime

    FLOAT

    Note: This field is reserved for future use.

    MaxOtherWaitTime

    FLOAT

    Note: This field is reserved for future use.

    AvgCPURunDelay

    FLOAT

    Average wait time in milliseconds in the run queue of all the requests completing in this WD during this interval.

    MaxCPURunDelay

    FLOAT

    Maximum wait time in milliseconds in the run queue of a request completing in this WD during this interval.

    AvgSeqRespTime

    FLOAT

    Average of the sum of the response times of the steps (as if all steps had been executed sequentially) of all the requests completing in this WD during this interval, in milliseconds.

    MaxSeqRespTime

    FLOAT

    Maximum sum of the response times of the steps (as if all steps had been executed sequentially) of a request completing in this WD during this interval, in milliseconds.

    AvgLogicalIO

    FLOAT

    Average count of logical I/Os of all requests completing in this WD during this interval.

    MaxLogicalIO

    FLOAT

    Maximum count of logical I/Os for a request completing in this WD during this interval.

    AvgLogicalKBs

    FLOAT

    Average logical I/O in KB of all requests completing in this WD during this interval.

    MaxLogicalKBs

    FLOAT

    Maximum logical I/O usage in KB of all requests completing in this WD during this interval.

    AvgPhysicalIO

    FLOAT

    Average count of physical I/Os of all requests completing in this WD during this interval.

    MaxPhysicalIO

    FLOAT

    Maximum physical I/O usage of all requests completing in this WD during this interval.

    AvgPhysicalKBs

    FLOAT

    Average physical I/O usage in KB of all requests completing in this WD during this interval.

    MaxPhysicalKBs

    FLOAT

    Maximum physical I/O usage in KB for a request completing in this WD during this interval.

    ThrottleBypassed

    INTEGER

    The number of queries reported in the Completions field that were allowed to run due to the TASM ThrottleBypass ruleset attribute.

    The following example shows how the parcels for a TDWM SUMMARY 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.

     

    Flavor

    Length

    Body

    Num

    Name

    Bytes

    Field

    Value

    0001

    Req

    16

    Request

    TDWM SUMMARY

    0003

    Data

    96

    MonVerID

    9

    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 following example shows the data returned by the TDWM SUMMARY request. Your application program may return this data in a different format or display.

    Note: You must enable TASM Workloads to submit a TDWM SUMMARY request.

    The TDWM SUMMARY request reports WD statistics over a given period. As shown in this example of WD activity returned from the API call, queries are classified into WDs 1 and 3 and not WDs 2, 4, and 5.

    SUCCESS parcel: 
    StatementNo=1,    ActivityCount=1,
    ActivityType=156, FieldCount=1
     
    TDWM Summary Request successful.
     
             Sample Seconds: 120
     
     
    SUCCESS parcel: 
    StatementNo=2,    ActivityCount=5,
    ActivityType=156, FieldCount=43
     
    TDWM Summary - # of WDIds: 5
     
      WLC   Arrivals  Complete  WereDlyd  Exceptns  Met SLG  CurrActv  CurrDlyd  Abt Cnt   Othr Cnt
       ID   Min Resp  Max Resp  Avg Resp  Min CPU   Max CPU   Avg CPU  AvgDelay  Err Cnt
            Collection Date/Time
    -----   --------  --------  --------  --------  --------  --------  --------  --------  --------
       12          0         0         0         0         0         1         0
        0          0
                 0.0       0.0       0.0       0.0       0.0       0.0       0.0
        0
             2011/06/15 18:33:49.00
     
       13          0         0         0         0         0         0         0
        0          0
                 0.0       0.0       0.0       0.0       0.0       0.0       0.0
        0
             2011/06/15 18:33:49.00
     
       14          0         0         0         0         0         0         0
        0          0
                 0.0       0.0       0.0       0.0       0.0       0.0       0.0
        0
             2011/06/15 18:33:49.00
     
       15          0         0         0         0         0         0         0
        0          0
                 0.0       0.0       0.0       0.0       0.0       0.0       0.0
        0
             2011/06/15 18:33:49.00
     
       16          0         0         0         0         0         0         0
        0          0
                 0.0       0.0       0.0       0.0       0.0       0.0       0.0
        0
             2011/06/15 18:33:49.00