TDWM STATISTICS

Teradata® Database Application Programming Reference

brand
Software
prodname
Teradata Database
vrm_release
16.20
category
Programming Reference
featnum
B035-1090-162K

Purpose

Returns statistics from the Teradata dynamic workload management software.

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 6 or later.

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

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

    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

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

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.

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.

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

CLIv2 Response Parcels

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

Sample Input - Teradata JDBC Driver Request

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, available at https://developer.teradata.com/connectivity/reference/jdbc-driver .

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://developer.teradata.com/connectivity/reference/jdbc-driver .

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
  • 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.
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
This field is only valid on monitor version software ID 8 or later.
FlexEligible SMALLINT The delayed request that is in a workload and is tagged as eligible for the Flex Throttle feature. Note, that flagged requests must meet all requirements for Flex Throttles before being released, such as system throttle restrictions.
  • 0 = Flex eligible
  • 1 = Not Flex eligible

Sample Output - Statement 1: Using TDWM STATISTICS with Request Flag Set to 0

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
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).

Since the input requested query information (a Request Flag of zero), the numbers shown reflect the number of active queries for each user and account. 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.

Sample Output - Statement 1: Using TDWM STATISTICS with Tables Restricted (RecordType 11)

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

Sample Output - Statement 1: Using TDWM STATISTICS with Request Flag Set to 3

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
-1 limit means that there is no limit.

Sample Output - Statement 1: Using TDWM STATISTICS with Request Flag Set to 8

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.
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.
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.
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.
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 **
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.
This field is only valid on monitor version software ID 10 or later.
FlexEligible SMALLINT The delayed request that is in a workload and is tagged as eligible for the Flex Throttle feature. Note, that flagged requests must meet all requirements for Flex Throttles before being released, such as system throttle restrictions.
  • 0 = Flex eligible
  • 1 = Not Flex eligible

Sample Output - Statement 2: Using TDWM STATISTICS with Request Flag Set to 6

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.

Sample Output - Statement 2: Using TDWM STATISTICS with Request Flag Set to 7

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.

Sample Output - Statement 3: Using TDWM STATISTICS

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.

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.

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, B035-2206.

FlexReleased SMALLINT An active request running due to the Flex Throttle feature.
  • 0 = not Flex released
  • 1 = Flex released

Sample Output - Statement 4: Using TDWM STATISTICS with Active Query Requests

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

Sample Output - Statement 4: Using TDWM STATISTICS with Flex Throttle Query Requests

The following example shows active query requests. Some of the active query requests are running because the Flex Throttle feature released them to run.

Active Requests: 

HOST  SESSION INT REQ  TYP DEP  USER NAME  RULE TYPE (ID) NAME  ACTIVE ... FLEXRELD
/QUERYID 
----  ------- -------  --- ---  ---------  -------------------  ------ --- --------
  1     22638       2  QRY   1  KEN9          WLC( 99)WD-9         349                   
307193492630017729 
  1     22643       2  QRY   1  KEN8          WLC( 98)WD-8         346                   
307183492630017055 
  1     22648       2  QRY   1  KEN7          WLC( 97)WD-7         345                   
307193492630017748 
  1     22655       2  QRY   1  KEN5          WLC( 95)WD-5         340                   
307183492630017095 
  1     22655       2  QRY   1  KEN5          WLG( 90)WD-GRP45     340                   
307183492630017095 
  1     22663       2  QRY   1  KEN3          WLC( 93)WD-3         337                   
307183492630017124 
  1     22664       2  QRY   1  KEN3          WLC( 93)WD-3         321        YES
307193492630017784 
  1     22647       2  QRY   1  KEN7          WLC( 97)WD-7         261        YES
307183492630017065 
  1     22642       2  QRY   1  KEN8          WLC( 98)WD-8         201        YES
307193492630017738 
  1     22644       2  QRY   1  KEN8          WLC( 98)WD-8         141        YES
307193492630017739 
  1     22637       2  QRY   1  KEN9          WLC( 99)WD-9          81        YES
307183492630017034