Returns a subset of the RSS ResUsageSps data.
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
must be version 9 or later. For a general explanation of monitor version choices, see MONITOR VERSION. |
Monitor Privileges
To use this request, you must have the MONRESOURCE privilege as part of your default role or this privilege must be granted directly to you.
- Using Roles to Manage User Privileges
- Teradata JDBC Driver Reference, available at https://teradata-docs.s3.amazonaws.com/doc/connectivity/jdbc/reference/current/frameset.html
Usage Notes - MONITOR WD
The data returned through MONITOR WD is ResUsageSps data from the RSS memory buffer.
You can use SET RESOURCE RATE to enable RSS collection.
The data in the buffer is summarized to unique service-level-goal-driven Priority Scheduler workload definition ID (pWDid) and VprType field values. For a description of the pWDid field, see resusagespsV.
TASM Workloads rule is enabled.
CLIv2 Response Parcels
| Parcel Sequence | Parcel Number | Length (Bytes) |
Comments/Key Parcel Body Fields |
|---|---|---|---|
| Success | 8 | 18 to 273 | Statement No =1 ActivityCount = 1 ActivityType = PCLMONWDRESRSTMT (202) |
| 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. See Statement 1 for details on the data returned. |
| EndStatement | 11 | 6 | StatementNo = 2-byte integer |
| Success | 8 | 18 to 273 | Statement No =2 ActivityCount = Number of Record parcels in statement 2 ActivityType = PCLMONWDRESRSTMT (202) |
| 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. See Statement 2 for details on the data returned. |
| EndStatement | 11 | 6 | StatementNo = 2-byte integer |
| End Request | 12 | 4 | None |
Response
Statement 1
The response to the first statement returns a Record parcel containing the following fields.
| Field/Column Name | Data Type | Description |
|---|---|---|
| SampleSec | SMALLINT NOT NULL |
Duration of the collection period in seconds. |
| AMPNodes | SMALLINT NOT NULL |
Number of nodes with at least one online AMP. |
| PENodes | SMALLINT NOT NULL |
Number of nodes with at least one online PE. |
| CollectionDate | DATE, NOT NULL |
Date the WD resource cache was last refreshed. |
| CollectionTime | FLOAT NOT NULL |
Time the WD resource cache was last refreshed. |
Statement 2
The second statement of the response returns multiple Record parcels. For more information, see resusagespsV.
| Field/Column Name | Data Type | Description |
|---|---|---|
| PPId | SMALLINT NOT NULL* The Workload Share Percent Management Method workload is assigned a proportion of the resources that are available after allocations have been made for tactical workloads. The percentage of resources is divided equally between all requests running in the WD. For example, if the Workload Share Percent is 5% and there are five SQL requests, each SQL request gets 1% of the share resources. |
This field is deprecated and returns a value of zero. |
| PGId | SMALLINT NOT NULL |
* The Workload Share Percent Management Method workload is assigned a proportion of the resources that are available after allocations have been made for tactical workloads. The percentage of resources is divided equally between all requests running in the WD. For example, if the Workload Share Percent is 5% and tThis field returns the pWDid value. |
| VprType | VARCHAR (4) NOT NULL |
Type of vproc:
|
| WDId | INTEGER NOT NULL |
WD ID. On SLES 11 or later systems, TASM Workloads rule is enabled. |
| AGId | SMALLINT NOT NULL |
This field is deprecated and returns a value of zero. |
| RelWgt | SMALLINT NOT NULL |
This field is deprecated and returns a value of zero. |
| NumTasks | INTEGER NOT NULL |
Average number of tasks of online
nodes. The field is the result of: NumTasks = SUM of (NumTasks-i) / N where: NumTasks-i is the number of tasks assigned to the WD at the end of the reporting period. i varies from 1 to N, where N is the number of online nodes. |
| QWaitTime | FLOAT NOT NULL |
Total wait time in milliseconds that work requests waited on an input queue before being serviced. |
| QWaitTimeMax | FLOAT NOT NULL |
Maximum time in milliseconds that
work requests waited on an input queue before being serviced. The field is the result of: QWaitTimeMax = MAX (QWaitTimeMax-i) where:
|
| CPUUserPct | FLOAT NOT NULL |
Weighted average of CPUUserPct of each node. This field is the result of: CPUUserPct = SUM of (CPUUserPct-i* ScalingFactor-i) / SUM of (ScalingFactor-i) where:
The CPU times are in milliseconds. The Parser CPU times are included in the Dispatcher CPU times. |
| WorkMsgMaxDelay | FLOAT NOT NULL |
General indicator only. This field
is result of the following calculation: WorkMsgMaxDelay = MAX (WorkMsgMaxDelay-i) where:
WorkMsgMaxDelay does not represent
the subtotal of the same message on the send and receive
side.
|
| WorkTypeInuseMax | INTEGER NOT NULL |
Total of the AMP Worker Task (AWT)
columns: WorkTypeInuseMax = MAX (WorkTypeInuseMax-i) where:
|
| WorkTimeInuseAvg | FLOAT NOT NULL |
Average number of AWTs used. This
field is result of: WorkTimeInuseAvg = SUM of (WorkTimeInuse-i) / N where:
This value is available in the
ResSpsView view as AwtUsedAvg.
|
| IODelay | FLOAT NOT NULL |
Number of I/Os that are delayed.
This field is result of: ProcBlksFsgRead + ProcBlksFsgWrite + ProcBlksFsgNIOs |
| IODelayTime | FLOAT NOT NULL |
Total time the I/O is delayed for.
This field is the result of: ProcWaitFsgRead + ProcWaitFsgWrite + ProcWaitFsgNIOs |
| PhysicalRead | FLOAT NOT NULL |
Number of physical reads performed
for this period. This field is the result of: FilePDbAcqReads + FilePDbPreReads + FilePCiAcqReads + FileSDbAcqReads + FileSCiAcqReads + FileTJtAcqReads + FileAPtAcqReads + FilePCiPreReads + FileSDbPreReads + FileSCiPreReads + FileTJtPreReads + FileAPtPreReads |
| PhysicalReadKB | FLOAT NOT NULL |
Number of physical reads in KB
performed for this period. This field is result of: FilePDbAcqReadKB + FilePDbPreReadKB + FilePCiAcqReadKB + FileSDbAcqReadKB + FileSCiAcqReadKB + FileTJtAcqReadKB + FileAPtAcqReadKB + FilePCiPreReadKB + FileSDbPreReadKB + FileSCiPreReadKB + FileTJtPreReadKB + FileAPtPreReadKB |
| PhysicalWrite | FLOAT NOT NULL |
Number of physical writes performed
for this period. This field is result of: FilePDbFWrites + FilePCiFWrites + FileSDbFWrites + FileSCiFWrites + FileTJtFWrites + FileAPtFWrites |
| PhysicalWriteKB | FLOAT NOT NULL |
Number of physical writers in KB
performed for this period. This field is result of: FilePDbFWriteKB + FilePCiFWriteKB + FileSDbFWriteKB + FileSCiFWriteKB + FileTJtFWriteKB + FileAPtFWriteKB |
| LogicalRead | FLOAT NOT NULL |
Number of logical reads performed
for this period. This field is result of: FilePDbAcqs + FilePDbPres + FilePCiAcqs + FileSDbAcqs + FileSCiAcqs + FileTJtAcqs + FileAPtAcqs + FilePCiPres + FileSDbPres + FileSCiPres + FileTJtPres + FileAPtPres |
| LogicalReadKB | FLOAT NOT NULL |
Number of logical reads in KB
performed for this period. This field is result of: FilePDbAcqKB + FilePDbPresKB + FilePCiAcqKB + FileSDbAcqKB + FileSCiAcqKB + FileTJtAcqKB + FileAPtAcqKB + FilePCiPresKB + FileSDbPresKB + FileSCiPresKB + FileTJtPresKB + FileAPtPresKB |
| LogicalWrite | FLOAT NOT NULL |
Number of logical writes performed
for this period. This field is result of: FilePDbDyRRels + FilePCiDyRRels + FileSDbDyRRels + FileSCiDyRRels + FileTJtDyRRels + FileAPtDyRRels |
| LogicalWriteKB | FLOAT NOT NULL |
Number of logical writes in KB
performed for this period. This field is result of: FilePDbDyRRelKB + FilePCiDyRRelKB + FileSDbDyRRelKB + FileSCiDyRRelKB + FileTJtDyRRelKB + FileAPtDyRRelKB |
| VPId | FLOAT NOT NULL |
Virtual partition ID. |
| WaitIO | FLOAT NOT NULL |
Number of milliseconds tasks in WD
waited for I/O over the reporting period. WaitIO is updated when the wait for I/O is completed. |
| WaitOther | FLOAT NOT NULL |
Number of milliseconds tasks in WD
waited for reasons other than I/O over the reporting period (for
example, a task waiting for a message). WaitOther is updated when wait is completed. |
| CPURunDelay | FLOAT NOT NULL |
Number of milliseconds tasks in the WD sat in the CPU runqueue waiting to run over the reporting period. This data can be used in determining demand for the virtual partition and Workload Share Percent. The Workload Share Percent is a workload management method. *If the CPU and I/O percentages for a virtual partition or WD are below their relative share values and the CPURunDelay values are low, there was insufficient demand to meet the share percentage. If the CPURunDelay values are high, higher tier SQL requests were allocated more resources so that there were insufficient resources remaining to allocate to SQL requests in this WD to meet its relative share. A virtual partition divides a system so that a percentage of resources are allocated to a collection of workloads. A virtual partition can consist of WDs from all management methods.
|
| IOSubmitted | FLOAT NOT NULL |
Number of I/Os submitted on behalf of this WD. |
| IOSubmittedKB | FLOAT NOT NULL |
KB of I/O submitted on behalf of this WD. |
| IOCompleted | FLOAT NOT NULL |
Number of AgeOut Now data blocks not to keep in memory (fsgcache) and to be written to disk. |
| IOCompletedKB | FLOAT NOT NULL |
KB of AgeOut Now data blocks not to keep in memory (fsgcache) and to be written to disk. |
| IOCriticalSubmitted | FLOAT NOT NULL |
Number of I/Os submitted with critical status. These I/Os run at top priority instead of being based on the I/O priority of the SQL request. |
| IOCriticalSubmittedKB | FLOAT NOT NULL |
KB of I/O submitted with critical status. These I/Os run at top priority instead of being based on the I/O priority of the SQL request. |
| DecayLevel1IO | FLOAT NOT NULL |
Number of times SQL requests in the WD reach decay level 1 due to I/O. DecayLevel1IO is used for Timeshare WDs** only.
|
| DecayLevel2IO | FLOAT NOT NULL |
Number of times SQL requests in the
WD decay level 2 due to I/O. DecayLevel2IO is used for Timeshare
WDs** only.
|
| DecayLevel1CPU | FLOAT NOT NULL |
Number of times SQL requests in the WD reach decay level 1 due to CPU. DecayLevel1CPU is used for Timeshare WDs** only.
|
| DecayLevel2CPU | FLOAT NOT NULL |
Number of times SQL requests in the WD reach decay level 2 due to CPU. DecayLevel2CPU is used for Timeshare WDs** only.
|
| TacticalExceptionIO | FLOAT NOT NULL |
Number of times SQL requests in the WD reach a tactical per-node exception due to I/O. An exception, used only for Tactical WDs, is created for each Tactical WD***. |
| TacticalExceptionCPU | FLOAT NOT NULL |
Number of times SQL requests in the WD reach a tactical per-node exception due to CPU. TacticalExceptionCPU is used for Tactical WDs***.
|
** The Timeshare Workload Management Method workload can be assigned to one of four stepped access levels, Top, High, Medium, or Low. The higher access levels are given larger access rates than the lower levels. For example, an SQL request assigned to a Timeshare WD with a Top access level, which has an access rate of 8, gets eight times the amount of resources than an SQL request assigned to a Low access level.
Timeshare workloads are assigned resources remaining after all allocations have been made for tactical and Workload Share Percent workloads.
*** The Tactical Workload Management Method workload yields the fastest available response time and runs at the highest tier, preempting all resource needs of other tiers. This method is well suited for critical, short-running queries that require fast response times.
Sample Input - CLIv2 Request
The following example shows how the parcels for a MONITOR WD request, built by CLIv2, appear when sent to the database server.
| Number | Length | Body | ||
|---|---|---|---|---|
| Num | Name | Bytes | Field | Value |
| 0001 | Req | 14 | Request | MONITOR WD |
| 0003 | Data | 6 | MonVerID | 9 |
| 0004 | Resp | 6 | Buffer Size | 64000 |
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.
Sample Output
The MONITOR WD request returns the following approximate values when TASM Workloads are enabled:
ResRate: 30; AMP Nodes: 1; PE Nodes: 1 Collection Date/Time: 06/15/2011 18:34:01.00 PGId VT PP CPUUsrPct QWaitTime QWTimeMax WkMsgMaxD WTypeMax WTimeAvg WDId AGId RWgt IODelay IODelayTi PhyRead PhyReadMB PhyWrite PhyWriteMB NPrc Reserved1 Reserved2 LogRead LogReadMB LogWrite LogWriteMB VPId WaitIO WaitOther CPURunDly IOsubmit IOSubmKB IOComplet IOComplKB IOCriticl IOCritKB Decay1IO Decay2IO Decay1CPU Decay2CPU TacExcpIO TacExcpCPU ==== ==== ==== ========= ========= ========= ========= ========= ========= SUCCESS parcel: StatementNo=2, ActivityCount=10, ActivityType=202, FieldCount=39 0 AMP 0 98.56 0.00 0.00 0.00 4 3.99 12 0 0 0.00 0.00 0.00 0.00 0.00 0.00 132 0.00 0.00 0.00 0.00 0.00 0.00 1 300.00 7558.00 82602.00 151.00 9664.00 151.00 9664.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 -------------------------------------------------------------------------- 0 PE 0 0.00 0.00 0.00 0.00 0 0.00 12 0 0 0.00 0.00 0.00 0.00 0.00 0.00 33 0.00 0.00 0.00 0.00 0.00 0.00 1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 -------------------------------------------------------------------------- 250 AMP 0 0.00 0.00 0.00 0.00 1 1.00 0 0 0 0.00 0.00 0.00 0.00 0.00 0.00 2288 0.00 0.00 0.00 0.00 0.00 0.00 100 0.00 119692.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 -------------------------------------------------------------------------- 251 MISC 0 0.00 0.00 0.00 0.00 0 0.00 0 0 0 0.00 0.00 0.00 0.00 0.00 0.00 160 0.00 0.00 0.00 0.00 0.00 0.00 100 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 -------------------------------------------------------------------------- 251 AMP 0 0.00 0.00 0.00 0.00 0 0.00 0 0 0 0.00 0.00 0.00 0.00 0.00 0.00 892 0.00 0.00 0.00 0.00 0.00 0.00 100 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 -------------------------------------------------------------------------- 254 MISC 0 0.32 0.00 0.00 0.00 0 0.00 0 0 0 0.00 0.00 0.00 0.00 0.00 0.00 4400 0.00 0.00 0.00 0.00 0.00 0.00 102 0.00 218950.00 318.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 -------------------------------------------------------------------------- 254 AMP 0 0.00 0.00 0.00 0.00 0 0.01 0 0 0 0.00 0.00 0.00 0.00 0.00 0.00 79844 0.00 0.00 0.00 0.00 0.00 0.00 102 359.00 3511485.00 1701.00 38.00 228.00 38.00 228.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 -------------------------------------------------------------------------- 254 PE 0 0.00 0.00 0.00 0.00 0 0.00 0 0 0 0.00 0.00 0.00 0.00 0.00 0.00 58034 0.00 0.00 0.00 0.00 0.00 0.00 102 272.00 2644347.0 399.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 -------------------------------------------------------------------------- 255 MISC 0 0.09 0.00 0.00 0.00 0 0.00 0 0 0 0.00 0.00 0.00 0.00 0.00 0.00 2992 0.00 0.00 0.00 0.00 0.00 0.00 101 0.00 548164.00 2922.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 -------------------------------------------------------------------------- 255 AMP 0 0.03 0.00 0.00 0.00 0 0.00 0 0 0 0.00 0.00 0.00 0.00 0.00 0.00 3520 0.00 0.00 0.00 0.00 0.00 0.00 101 0.00 315555.00 45137.00 13567.00 1722001.00 13569.00 1722255.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 . . .