15.10 - TDWM STATISTICS - Teradata Database

Teradata Database Application Programming Reference

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

Returns statistics from the Teradata dynamic workload management software.

 

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

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.

Request Flag

SMALLINT

Type of statistics returned:

  • 0 = System query counts
  • 1 = System session counts
  • 2 = System delayed queries
  • 3 = Workload query counts
  • 4 = Workload delayed queries
  • Note: This request includes requests that are delayed due to WD throttle limits but no longer returns system delay queue information.

  • 5= (Load and archive) utility counts
  • 6 = Delayed utility session information
  • 7 = All delay statistics (requests 2, 4 and 6)
  • 8 = All information
  • Note: If you are using monitor software version 9 or earlier, Request Flag requests 1 through 7 are returned. If you are using monitor software version 10 or later, Request Flag requests 1 through 7 and 9 are returned.

  • 9 = All active requests
  • Note: Request Flag:

  • 7 is valid for monitor version software ID 8 or later.
  • 8 is valid for monitor version software ID 9 or later.
  • 9 is valid for monitor version software ID 10 or later.
  • 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
  • Before using this request, see “Impact of Object Name Length on PM/API Requests” on page 332.

    The Workload Query Manager (WQM) enforces TASM rules on the number of active system sessions, system queries, workloads, workload groups, and load or archive utilities for specified conditions.

    The TDWM STATISTICS request returns information in the various record formats depending on the Request Flag supplied as described in the following table.

    The Request Flag in the input record determines the format of the data in the response records, and in some cases the type of information contained within each statement (for example, a query or session).

    Use Teradata Viewpoint to learn more about the WQM process.

    The following table specifies which format types are returned.

    Note: These formats are defined later in this section.

     

    If a Request Flag is...

    Format1

    Records Are Returned...

    Format2

    Records Are Returned...

    Format3

    Records Are Returned...

    Format 4 Records Are Returned...

    0 = System query counts

    X

     

     

     

    1 = System session counts

    X

     

     

     

    2 = System delayed requests

     

    X

     

     

    3 = Workload query counts

    X

     

     

     

    4 = Workload delayed requests

     

    X

     

     

    5 = Utility counts

     

     

    X

     

    6 = Utility delayed sessions

     

    X

     

     

    7 = All delay information

     

    X

     

     

    8 = All information

    Note: If you are using monitor software version 9 or earlier, Request Flag requests 1 through 7 are returned. If you are using monitor software version 10 or later, Request Flag requests 1 through 7 and 9 are returned.

    X

    X

    X

    X

    9 = All active requests

     

     

     

    X

    The following is the Parcel Sequence returned if Request Flag 0 through 7 is requested. Only one parcel is returned.

    Statement 1, Statement 2, Statement 3, or Statement 4 is returned depending on the Request Flag. Since a system query, system session, or utility throttle can be affected by multiple rules, multiple rows can be returned for requests 2, 4, 6, 7, 8, and 9.

     

    Parcel Sequence

    Parcel Flavor

    Length (Bytes)

    Comments/Key Parcel Body Fields

    Success

    8

    18 to 273

    StatementNo = 1, 2, or 3

    ActivityCount = Not applicable

    ActivityType = 132 (PCLTWMSTATSSTMT)

    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, StatementNo-2, or StatementNo-3 data format, depending on the Request Flag in the input data. There is one Record parcel for each item being tracked by the WQM. If there are no qualifying records, no Record parcel is sent.

    EndStatement

    11

    6

    StatementNo = 2-byte integer=1, 2, or 3

    EndRequest

    12

    4

    None

    The following Parcel Sequence is returned if Request Flag is 8 or 9 and the monitor version software ID is 10 or greater.

     

    Parcel Sequence

    Parcel Flavor

    Length (Bytes)

    Comments/Key Parcel Body Fields

    Success

    8

    18 to 273

    StatementNo = 1

    ActivityCount = number of queries with applicable statistics

    ActivityType = 132 = PCLTWMSTATSSTMT

    DataInfo

    71

    6 to 64100

    Optional: this parcel is present if request was IndicData parcel.

    Record

    10

  • 5 to 64100 (record mode)
  • 7 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. There is one record parcel for each item being tracked by the WQM. If there are no qualifying records, no record parcel is sent.

    EndStatement

    11

    6

    StatementNo = 2-byte integer = 1

    Success

    8

    18 to 273

    StatementNo = 2

    ActivityCount = number of queries with applicable statistics

    ActivityType = 132 = PCLTWMSTATSSTMT

    DataInfo

    71

    6 to 64100

    Optional: this parcel is present if request was IndicData parcel.

    Record

    10

  • 5 to 64100(record mode)
  • 7 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. There is one record parcel for each item being tracked by the WQM. If there are no qualifying records, no record parcel is sent.

    EndStatement

    11

    6

    StatementNo = 2-byte integer = 2

    Success

    8

    18 to 273

    StatementNo = 3

    ActivityCount = number of queries with applicable statistics

    ActivityType = 132 = PCLTWMSTATSSTMT

    DataInfo

    71

    6 to 64100

    Optional: this parcel is present if request was IndicData parcel.

    Record

    10

  • 5 to 64100 (record mode)
  • 7 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. There is one record parcel for each item being tracked by the WQM. If there are no qualifying records, no record parcel is sent.

    EndStatement

    11

    6

    StatementNo = 2-byte integer = 3

    Success

    8

    18 to 273

    StatementNo = 4-byte

    ActivityCount = number of queries with applicable statistics

    ActivityType = 132 = PCLTWMSTATSSTMT

    Record

    10

  • 5 to 64100 (record mode)
  • 7 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 4. There is one record parcel for each item being tracked by the WQM. If there are no qualifying records, no record parcel is sent.

    EndStatement

    11

    6

    StatementNo = 2-byte integer = 4

    EndRequest

    12

    4

    None

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

    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 first statement is a Record parcel format containing statistical information for one system query, system session, or workload item. There is one record for each item that is being tracked by the Teradata dynamic workload management software. The following table describes this Record format.

     

    Field/Column Name

    Data Type

    Description

    Record Type

    SMALLINT

    Type of information contained in this record:

  • 1 = User
  • 2 = Account String
  • 3 = Performance Group (PG)
  • 4 = Account String
  • 5 = Profile
  • 6 = Client ID
  • 7 = Network Address
  • 8 = Application
  • 9 = Collective Rule
  • 10 = Database
  • 11 = Table
  • 12 = WD
  • 13 = Utility
  • 14 = Macro
  • 15 = Stored Procedure
  • 16 = View
  • 17 = QueryBand
  • 18 = Function
  • 19 = Method
  • 20 = Virtual partition
  • 21 = Reserved
  • 22 = Server
  • 23 = AWT Resource Limit
  • Rule ID

    INTEGER

    Rule ID for the system query, system session, or workload for this statistic.

    If Request Flag is 0 or 1, Rule ID returns the ID of the system query or system session throttle rule.

    If the Request Flag is 3, Rule ID returns the ID of the workload.

    Object Name

    VARCHAR (128) CHARACTER SET UNICODE

    For Request Flags 0 and 1, this is the name of the system query or system session throttle rule based on its Record Type. When Record Type is set to 11, 14, 15, 16, 18 or 19, this field is the qualifying database name for the Table Name field.

    For Request flag 3, this field is always empty.

    Qualified Name

    VARCHAR (128) CHARACTER SET UNICODE

    For Request Flags 0 and 1, this is the name of the table, macro, stored procedure, function or method object when Record Type is set to 11, 14, 15, 16, 18, or 19.

    For Request Flag 3, this field is always empty.

    Active

    INTEGER

    Number of requests currently active. This is the number of active queries or sessions depending on the information requested in the input record.

    Limit

    INTEGER

    Current minimum limit on this item. This is the limit on the number of queries or sessions depending on the information requested in the input record.

    Delayed

    INTEGER

    Number of requests currently delayed (queued) for this item.

    Note: This field is not applicable if Request Flag is 1.

    Rule Name

    VARCHAR (30) CHARACTER SET UNICODE

    Rule name for the system query or system session throttle rule, or workload for this statistic.

    If Request Flag is 0 or 1, Rule Name returns the name of the system query or system session throttle rule is returned.

    If the Request Flag is 3, Rule Name returns the name of the workload.

    Statistic Type

    INTEGER

    Type of statistics returned:

  • 0 = Query data
  • 1 = Logon data
  • 2 = Workloads
  • Note: This field is only valid on monitor version software ID 8 or later.

    Example

    If a Request Flag of zero (system query counts) is requested, the following is an example of the records that are returned as part of the first statement.

    RecordType   Rule ID  Obj Name    Qual Name  Active  Limit  Delayed
    ----------   -------  --------    ---------  ------  -----  -------
    1          0       Ken                4      5     0
    1          0       Dina               2      4     0
    2          0       Sales              3      10    0
    2          0       PreSales           2      10    0
    2          0       Training           3      3     10
    3          0       H                  6      6     2
    3          0       H2                 1      1     0

    This example shows the following important information:

  • Two users running (Record Type 1) also have rules defined on the number of queries that they are allowed to have running simultaneously (Limit).
  • Three accounts running (Record Type 2) and also have rules defined on the number of queries that they are allowed to have running simultaneously (Limit).
  • Two Performance Groups (Record Type 3) that have rules defined on the number of queries that they are allowed to have running simultaneously (Limit)
  • Since the input requested query information (a Request Flag of zero), the numbers shown reflect the number of active queries for each user, account, and performance group. If logon information were requested (a Request Flag of 1) in the input, the records would look the same. However, the numbers shown would reflect the number of active sessions within the restricted area.

    Items that are restricted, but are not currently active on the system, or are bypassed, are not be returned.

    Note: If there are currently no active elements within the system, that is, if no queries are currently active within the Performance Groups (for example, H and H2 in the example), no parcels with a Record Type of 3 are present.

    The above example also shows that the number of allowed queries for the Object Name Training and the Performance Group H is less than the number of queries submitted; therefore, the Delayed field displays the requests over and above the defined limits.

    Example  

    If Tables are restricted, the following records are returned:

    RecordType Rule ID  Obj Name    Qual Name    Active Name   Limit  Delayed
    ---------  -------  --------    ---------    -----------   -----  -------
    11         0        Employee    PayrollDB    2             10     0
    11         0        Leads       MaketingDB   1             1      1
    Example  

    If WDs (Request Flag 3) are requested, the following records are returned:

    RecordType  Rule ID  Obj Name    Qual Name    Active Name   Limit  Delayed
    ----------  -------  --------    ---------    -----------   -----  -------
    12          03       TacticalWD               4             100    0
    12          05       StrategicWD              2             2      1
    12          05       AdHocWD                  0            -1      0

    Note: -1 limit means that there is no limit.

    Example  

    If Request Flag 8 is requested, the following records are returned:

    RecordType  Rule ID  Obj Name    Qual Name    Active   Limit  Delayed  Rule Name    Stat Type
    ----------  -------  --------    ---------    ------   -----  -------  ---------    --------
    0           0        Ken                      4        5      0
    0           0        Dina                     2        4      0
    1           0        Sales                    3        10     0
    1           0        Presales                 2        10     0
    1           0        Training                 3        3      10      LimitTraining
    2           0        H                        6        6      2
    2           0        H2                       1        1      0
    11          0        Employee    PayrollDB    2        10     0
    11          0        Leads       MaketingDB   1        1      1
    12          3        TacticalWD               44       100    0
    12          5        StrategicWD              2        2      1
    12          5        AdHocWD                  0        -1     0

    Statement 2

    The second statement is a Record parcel format containing information about queries or sessions being delayed by the Teradata dynamic workload management software due to a system query, system session, workload or utility throttle limit. If Request Flag 7 is requested, one record for each request or session that is being delayed is returned with one Rule ID. Multiple rows are returned if the request has more than one rule causing its delay.

    The following table describes this Record format.

     

    Field/Column Name

    Data Type

    Description

    User name

    VARCHAR (128) CHARACTER SET UNICODE

    Logon user name of session.

    HostId

    SMALLINT

    Host ID of the session number for the delayed request or session.

    Session Number

    INTEGER

    Number for the held request or session.

    Request Number

    INTEGER

    Request number for the delayed request or session.

    This field is zero if Request Flag is 6.

    Rule Name

    VARCHAR (30) CHARACTER SET UNICODE

    Rule name for the system query or session throttle rule, or workload for this statistic.

    If Request Flag is 2 or 6, Rule Name is the name of the system query or system session throttle rule.

    If Request Flag is 4, Rule Name returns the name of the workload.

    If Request Flag is 9, Rule Name and all remaining fields are empty, except for Rule Type.

    Rule ID

    INTEGER

    Rule ID for the system query or system session throttle, or workload for this statistic.

    If Request Flag is 2 or 6, Rule ID returns the ID of the system query or system session throttle rule.

    If Request Flag is 4, Rule ID returns the ID of the workload.

    This Rule ID must be supplied in the Abort/Release interface (when used). If the Request Flag is 6, Rule ID is zero.

    Total Time Held

    INTEGER

    Total number of wall clock seconds that this request or session has been held by the WQM.

    Overridable

    Boolean

    Indicator that the database administrator is allowed to abort or release the request.

    Note: The queue table requests are controlled internally by the database and cannot be altered by the administrator.

    If Request Flag is 6, this field indicates if the delayed session can be released. A session cannot be released if it exceeds the internal AMP worker task limit or an internal utility limit.

    A delayed session can always be aborted.

    Blocking Count

    INTEGER

    Number of consecutive times that this session has been identified as blocking at least one other session.

    If Request Flag is 6, Blocking Count is zero.

    IFP ID

    SMALLINT

    ID of the PE vproc that initiated this request.

    WD Delayed

    INTEGER

  • 1 = Query is delayed for a workload throttle rule.
  • 0 = Query is not delayed for a workload throttle rule.
  • Note: This field is only valid on monitor version software ID 8 or later.

    System Delayed

    INTEGER

  • 1 = Query is delayed for a system query or system session throttle rule.
  • 0 = Query is not delayed for a system query or system session throttle rule.
  • Note: This field is only valid on monitor version software ID 8 or later.

    Utility Delayed

    INTEGER

  • 1 = Query is delayed for a utility throttle rule.
  • 0 = Query is not delayed for a utility throttle rule.
  • Note: This field is only valid on monitor version software ID 8 or later.

    Rule Type

    INTEGER

    Rule type for the request:

  • 0 = Workload
  • 1 = System
  • 2 = Utility
  • 3 = Workload Group *
  • 4 = Virtual Partition *
  • 5 = AWT Resource Limit **
  • Note: The Rule Type field is only valid on monitor version software ID 8 or later.

    * Workload Group 3 and Virtual Partition 4 are returned in Monitor Version 11 and later.

    ** AWT Resource Limit is returned in Monitor Version 12 and later.

    Group Delayed

    INTEGER

  • 1= Query is delayed for a workload group throttle.
  • 0= Query is not delayed for a workload group throttle.
  • Note: This field is only valid on monitor version software ID 10 or later.

    Example  

    If a Request Flag of 6 is requested, the following Records are returned as part of the second statement:

    User Name  Host ID  Sess  Req Nbr  Rule Name  Rule ID  Total TimeHeld  Over  Block
    ---------  -------  ----  -------  ---------  -------  --------------  ----  -----
    Ken        01       1012  0                   0        45              True  0
    Dina       01       1088  0                   0        110             True  0

    This report indicates that 2 sessions are being delayed due to a system query or system session throttle limits.

    Example

    If a Request Flag of 7 is requested, the following Records are returned as part of the second statement:

    User Name  Host ID  Sess  Req Nbr  Rule Name  Rule ID  Total TimeHeld  Over  Block
    ---------  -------  ----  -------  ---------  -------  --------------  ----  -----
    Ken        01       1013  1                   4        15              True  0
    Ken        01       1013  1                   5        15              True  0
    Dina       01       1089  0                   4        110             True  0
     

    This report indicates that 2 requests are being delayed, but the first request has two rules that are causing its delay.

    Statement 3

    The third statement is a Record parcel format containing information on the load utilities that are active in the system. There is one record for every load utility type.

    This statement is only returned when utility statistics are requested. The following table describes this record format.

     

    Field/Column Name

    Data Type

    Description

    Utility Type

    SMALLINT

    Type of utility information contained in this record:

  • 0 = MultiLoad + FastLoad
  • 1 = MultiLoad
  • 2 = FastLoad
  • 3 = FastExport
  • 4 = ARC
  • 5 = Standalone Mload
  • 6 = Standalone FastLoad
  • 7 = Standalone FastExport
  • 8 = TPT update Op: MultiLoad
  • 9 = TPT load Op: Fastload
  • 10 = TPT export Op: FastExport
  • 11 = JDBC MultiLoad
  • 12 = JDBC FastLoad
  • 13 = JDBC FastExport
  • 14 = CSP Save Dump (FastLoad)
  • 15 = DSA
  • 16 = DSA Backup
  • 17 = DSA Restore
  • 18 = MLOADX
  • Count

    SMALLINT

    Number of utilities of this utility type that are active.

    Limit

    SMALLINT

    Current limit on the number of utilities of this type.

    A value of -1 indicates there is no limit defined.

    Example  

    The following is an example of the records that are returned as part of third statement:

     TYPE  COUNT  LIMIT
     ----  -----  -----
        0      0     30
        1      0     30
        2      0     30
        3      0     60
        4      0    350
        5      0     30
        6      0     30
        7      0     60
        8      0     30
        9      0     30
       10      0     60
       11      0     30
       12      0     30
       13      0     60
       14      0     30

    Since a row is returned for each utility type, the Limit value can be either the system default value for the utility or a TASM rule Throttle value.

    Note: When a TASM rule Throttle value is active, it overrides the default limit.

    Statement 4

    The fourth statement is a Record parcel format containing information about queries, sessions, and so on being counted as active by the Teradata dynamic workload management software due to a system query, system session, workload, workload group, or utility throttle rule.

    Multiple rows in this statement are returned if the request has more than one rule for which it is counted as active.

    Note: This statement is not a count of all active queries in the system.

     

    Field/Column Name

    Data Type

    Description

    User name

    VARCHAR (128) CHARACTER SET UNICODE

    Logon user name of session.

    HostId

    SMALLINT

    Session number ID for the active request.

    Session Number

    INTEGER

    Session number for the active request.

    Request Number

    INTEGER

    Request number for the active request.

    Internal Request Number

    INTEGER

    Internal AMP request number for the active request.

    Rule Name

    VARCHAR(30) CHARACTER SET UNICODE

    Rule name for the active request.

    Rule Id

    INTEGER

    Rule ID for the active request.

    Rule Type

    INTEGER

    Rule type for the request:

  • 0 = Workload class
  • 1 = System query
  • 2 = System session
  • 3 = Utility throttle
  • 4 = Workload group
  • 5 = Virtual Partition *
  • 6 = AWT Resource Limit **
  • * Virtual Partition 5 is returned in Monitor Version 11 and later.

    ** AWT Resource Limit is returned in Monitor Version 12 and later.

    Statement Type

    INTEGER

    Type of active request:

  • 0 = Session
  • 1 = Query
  • 2 = Call
  • 3 = Unit of work
  • Time Active

    INTEGER

    Total number of wall clock seconds that this request has been active.

    ActiveThrottleBypassed

    SMALLINT

    Whether an active request is active solely due to the ThrottleBypass ruleset attribute. This attribute overrides the throttle limits if the session owning the request has an object lock higher than the Access level. Possible values include:

  • 0 = This value indicates one of the following:
  • The ThrottleBypass ruleset attribute is not active.
  • The throttle limit is lower than the Access level.
  • The throttle limit is not specified.
  • 1 = The ThottleBypass ruleset attribute is active.
  • For more information, see Teradata Viewpoint User Guide.

    Example  

    The following example shows two active query requests. One of the active query requests is due to a TASM Workload rule and one is due to a TASM Utility throttle rule.

     
    TDWM Stats - Active items: 2
     
    USERNAME  HOST    SESSION  REQ  INTREQ   RULEID / NAME   TYPE       STMTTYPE     ACTIVETIME
    --------  ----    -------  ---  -------  -------------   -----      ---------    ---------- 
    TEST1     1025     1000     6      6     (15)WD-ALT      WD           1            7
    TEST1     1025     1000     6      6     (13)WD-GRP1     Util/Ses     1            7