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:
Request Flag:
|
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.
- Teradata Vantage™ - Database Administration, B035-1093
- Teradata Vantage™ NewSQL Engine Security Administration, B035-1100
- Teradata JDBC Driver Reference, available at https://developer.teradata.com/connectivity/reference/jdbc-driver
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.
| 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 |
|
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 |
|
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 |
|
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 |
|
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 |
|
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
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:
|
| 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:
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.
|
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
- 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
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 |
This field is only valid on monitor version software
ID 8 or later.
|
| System Delayed | INTEGER |
This field is only valid on monitor version software
ID 8 or later.
|
| Utility Delayed | INTEGER |
This field is only valid on monitor version software
ID 8 or later.
|
| Rule Type | INTEGER | Rule type for the request:
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 |
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.
|
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:
|
| 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.
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.
| 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:
* 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:
|
| 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:
For more information, see Teradata® Viewpoint User Guide, B035-2206. |
| FlexReleased | SMALLINT | An active request running due to the Flex Throttle feature.
|
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