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.
- 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 - MONITOR WD
The data returned through MONITOR WD is ResUsageSps data from the RSS memory buffer.
You can use Teradata Viewpoint, CNS commands via Database Window, or 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 Teradata Vantage™ - Resource Usage Macros and Tables, B035-1099. For details on the service-level-goal-driven Priority Scheduler WD, see Teradata® Viewpoint User Guide, B035-2206.
TASM Workloads rule is always enabled.
For information on TASM rules, see Teradata® Viewpoint User Guide, B035-2206.
CLIv2 Response Parcels
Parcel Sequence | Parcel Flavor | 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 results in a Record parcel containing the fields below.
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 results in multiple Record parcels.
For more information on the following columns, see the ResUsageSps Table and ResSpsView in Teradata Vantage™ - Resource Usage Macros and Tables, B035-1099.
Field/Column Name | Data Type | Description |
---|---|---|
PPId | SMALLINT NOT NULL |
This field is obsolete and returns a value of zero. |
PGId | SMALLINT NOT NULL |
This 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 always enabled. For information on TASM rules, see Teradata® Viewpoint User Guide, B035-2206. |
AGId | SMALLINT NOT NULL |
This field is obsolete and returns a value of zero. |
RelWgt | SMALLINT NOT NULL |
This field is obsolete 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 execute 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 execute 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 hit 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 hit 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 hit 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 hit 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 hit a tactical per-node exception due to CPU. TacticalExceptionCPU is used for
Tactical WDs***.
|
* 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 will get 1% of the share resources. For more information, see Teradata® Viewpoint User Guide, B035-2206.
** 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, would receive 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. For more information, see Teradata® Viewpoint User Guide, B035-2206.
*** The Tactical Workload Management Method workload yields the fastest available response time and executes 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. For more information, see Teradata® Viewpoint User Guide, B035-2206.
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.
Flavor | 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 values approximately as shown below when TASM Workloads are enabled (see Teradata® Viewpoint User Guide, B035-2206 for information on this rule):
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 . . .