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™ - Advanced SQL Engine Security Administration, B035-1100
- Teradata JDBC Driver Reference, available at https://teradata-docs.s3.amazonaws.com/doc/connectivity/jdbc/reference/current/frameset.html
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... | Format 5 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. If you are using monitor version 14 or later, Request Flag requests 1 through 7, 9, and 15 through 17 are returned.
|
X | X | X | X | X |
| 9 = All active requests | X | ||||
| 15 = Arrival rate meter counts | X | ||||
| 16 = Arrival rate meter deferred requests | X | ||||
| 17 = Detail arrival rate meter counts | 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 database server, see Teradata JDBC Driver Reference, available at https://teradata-docs.s3.amazonaws.com/doc/connectivity/jdbc/reference/current/frameset.html.
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. If Record Type is 24, Rule ID returns the ID of the arrival rate meter. |
| 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. If Record Type is 24, Active returns the current arrival rate for the time unit specified in the TimeUnit field. |
| 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. If Record Type is 24, Limit returns the current arrival rate limit for the time unit specified in the TimeUnit field. A value of 1 indicates that no limit is defined. |
| Delayed | INTEGER | Number of requests currently delayed (queued) for this item. This field is not applicable if Request Flag is 1.
If Record Type is 24, Delayed returns the number of requests currently in the Defer queue for this arrival rate meter. This value is not meaningful when Limit 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. If Record Type is 24, RuleName returns the name of the arrival rate meter. |
| 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.
|
| TimeUnit | SMALLINT | If Record Type is 24, this field is the time unit of this arrival rate meter:
If Record Type is not 24, this field is not meaningful. |
| Required Qualification Time | INTEGER | If Record Type is 24, Required Qualification Time returns the number of seconds in which the current arrival rate must continuously remain at the limit before new requests are held in the Defer queue. A value of zero indicates the limit will be enforced as soon as it is reached. If Record Type is not 24, this field is not meaningful. |
| Current Qualification Time | INTEGER | If Record Type is 24, Current Qualification Time returns the number of seconds in which the current arrival rate has continuously remained at the limit. A value of zero indicates the limit has not been reached. If Record Type is not 24, this field is not meaningful. |
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 24 117 10 10 5 ARM_1
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.
If Request Flag 16 is requested, the record parcel reports similar information about queries being deferred due to one or more arrival rate meters. If a request is deferred due to multiple arrival rate meters, one row is returned for each rule causing its deferral.
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, workload, or arrival rate meter rule 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. If Request Flag is 16, Rule Name is the name of the arrival rate meter rule. |
| Rule ID | INTEGER | Rule ID for the system query or system session throttle, workload, or arrival rate meter 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. If Request Flag is 16, Rule ID returns the ID of the arrival rate meter rule. |
| Total Time Held | INTEGER | Total number of wall clock seconds that this request or session has been held by the WQM. If Rule Type is 6, returns the total number of wall clock seconds that this request has been held in the Defer queue due to one or more arrival rate meters. |
| 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. *** Arrival Rate Meter is returned in monitor version 14 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.
|
| Arrival Rate Meter Deferred | INTEGER |
|
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.
Sample Output - Statement 2: Using TDWM STATISTICS with Request Flag Set to 16
User Name Host ID Sess Req Nbr Rule Name Rule ID Total TimeHeld Over Block --------- ------- ---- ------- --------- ------- -------------- ---- ----- USER1 1 4734 2 ARM_1 117 17 True 0
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
Statement 5
Statement 5 record format returns detailed information about each arrival rate meter. An arrival rate is represented as a request count per time unit. Each time unit is divided into six fixed intervals. Each row and record contains information for one interval. Therefore, there are six rows for each arrival rate meter.
| Field Name | Data Type | Description |
|---|---|---|
| Rule Type | INTEGER | Rule type for the request:
|
| Rule Id | INTEGER | ID of the arrival rate meter. |
| Rule Name | VARCHAR (30) CHARSET UNICODE | Name of the arrival rate meter. |
| Interval Number | INTEGER | Interval number. |
| Interval Start Date | INTEGER | Start date of this interval. |
| Interval Start Time | FLOAT | Start time of this interval. |
| Request Count | INTEGER | Number of requests admitted in this interval. |
| Host ID | SMALLINT | HOST ID of the first request in this interval. |
| Session Number | INTEGER | Session number of the first request in this interval. |
| Request Number | INTEGER | Request number of the first request in this interval. |
| Internal Request Number | INTEGER | Internal request number of the first request in this interval. |
Sample Output - Statement 5
TDWM Stats - ARM detail count items: 12 TYPE RULE (ID) NAME ITVL START DATE START TIME COUNT HOST SESSION REQ INTREQ ---- ------------------ ---- ---------- ----------- ----- ---- ------- ------ ------ ARM (118)ARM_2 1 2018/08/03 16:10:00.00 0 0 0 0 0 ARM (118)ARM_2 2 2018/08/03 16:30:00.00 2 1 5909 4 4 ARM (118)ARM_2 3 2018/08/03 16:40:00.00 8 1 6184 3 3 ARM (118)ARM_2 4 2018/08/03 16:50:00.00 8 1 6676 3 3 ARM (118)ARM_2 5 2018/08/03 17:00:00.00 6 1 7138 3 3 ARM (118)ARM_2 6 2018/08/03 17:10:00.00 2 1 7366 3 3 ARM (117)ARM_1 1 2018/08/03 17:10:30.00 40 1 7334 4 4 ARM (117)ARM_1 2 2018/08/03 17:10:40.00 17 1 7355 3 3 ARM (117)ARM_1 3 2018/08/03 17:10:50.00 0 0 0 0 0 ARM (117)ARM_1 4 2018/08/03 17:11:00.00 0 0 0 0 0 ARM (117)ARM_1 5 2018/08/03 17:11:10.00 0 0 0 0 0 ARM (117)ARM_1 6 2018/08/03 17:11:20.00 33 1 7362 4 4