MONITOR WD Request | Application Programming Reference | Teradata Vantage - MONITOR WD - Advanced SQL Engine - Teradata Database

Application Programming Reference

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
hvk1593628831140.ditamap
dita:ditavalPath
hvk1593628831140.ditaval
dita:id
B035-1090
lifecycle
previous
Product Category
Teradata Vantage™

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.

For more information on roles and privileges, see:

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.

The WDId and pWDid fields return valid ID values.
Zero is a valid value for the pWDid field.

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
  • 5 to 64100 (record mode)
  • 11 to 64100 (indicator mode)
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
  • 5 to 64100 (record mode)
  • 11 to 64100 (indicator mode)
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

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://teradata-docs.s3.amazonaws.com/doc/connectivity/jdbc/reference/current/frameset.html.

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:
  • AMP
  • PE
  • MISC
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:
  • QWaitTimeMax-iis QWaitTimeMax in each online node.
  • i varies from 1 to N, where N is the number of online nodes.
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:
  • CPUUserPct-i is calculated as:

    (CPUUServAwt + CPUUServDisp + CPUUServMisc + CPUUExecAwt + CPUUExecDisp + CPUUExecMisc) * 100 /(NCPUs*Centisecs*10)

    NCPUs is the number of CPUs in the node.

  • i varies from 1 to N, where N is the number of online nodes.
  • ScalingFactor-i is the node CPU normalization factor in each node.

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-iis calculated in each online node as:

    WorkMsgsendDelayMax + WorkMsgReceiveDelayMax

  • i varies from 1 to N, where N is the number of online nodes.
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:
  • WorkTypeInuseMax-i is the sum of WorkTypeMax00 through WorkTypeMax15 in each node.
  • i varies from 1 to N, where N is the number of online nodes.
WorkTimeInuseAvg FLOAT

NOT NULL

Average number of AWTs used. This field is result of:

WorkTimeInuseAvg = SUM of (WorkTimeInuse-i) / N

where:
  • WorkTimeInuse-i is calculated in each online node as:

    WorkTimeInuse/(Centisecs * 10 * NCPUs)

    NCPUs is the number of CPUs in the node.

  • i varies from 1 to N, where N is the number of online nodes.
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.

In this example, the size of the response buffer in the example is set at the maximum (64,000 bytes), although you can set it to any size. However, a minimum response size is 32,000 bytes.
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):

The Monitor WD request commonly returns values in text character format. Your application program may return the values in a different format or display.
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
                                       .
                                       .
                                       .