15.10 - MONITOR WD - Teradata Database

Teradata Database Application Programming Reference

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
featnum
B035-1090-151K

Returns a subset of the RSS ResUsageSps 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.

Note: 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” on page 162.

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:

  • Database Administration
  • Security Administration
  • Teradata JDBC Driver User Guide
  • 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.

     

    On SLES 10 or earlier systems ...

    On SLES 11 or later systems ...

    the data in the buffer is summarized to unique VprType, PGId, and PPId field values.

    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 Resource Usage Macros and Tables. For details on the service-level-goal-driven Priority Scheduler WD, see Teradata Viewpoint User Guide.

    any performance group that is not assigned to a workload definition returns a WDId field value of zero.

    the WDId and pWDid fields return valid ID values.

    Note: Zero is a valid value for the pWDid field.

    For information on the pWDid field, see the PGId field in the following table.

    if TASM Workloads are not enabled, the WDId fields in all rows contain zeros.

    TASM Workloads rule is always enabled.

    For information on TASM rules, see Teradata Viewpoint User Guide.

     

    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” on page 196 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” on page 196 for details on the data returned.

    EndStatement

    11

    6

    StatementNo = 2-byte integer

    End Request

    12

    4

    None

    Note: 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.

    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 the “ResSpsView” in Resource Usage Macros and Tables.

     

    Field/Column Name

    Data Type

    Description

    PPId

    SMALLINT,
    NOT NULL

  • On SLES 10 or earlier systems, this field returns the Performance Period ID.
  • On SLES 11 or later systems, this field is obsolete and returns a value of zero.
  • PGId

    SMALLINT,
    NOT NULL

  • On SLES 10 or earlier systems, this field returns the performance group ID.
  • On SLES 11 or later systems, this field returns the pWDid value.
  • VprType

    VARCHAR (4),
    NOT NULL

    Type of vproc:

  • AMP
  • PE
  • MISC
  • WDId

    INTEGER,
    NOT NULL

    WD ID.

    Note:  

  • On SLES 10 or earlier systems, if TASM Workloads are not enabled, the WDId fields in all rows return zeros.
  • On SUSE Linux Enterprise Server 11 or later systems, TASM Workloads rule is always enabled. For information on TASM rules, see Teradata Viewpoint User Guide.
  • AGId

    SMALLINT,
    NOT NULL

  • On SLES 10 or earlier systems, this field returns the Allocation Group ID.
  • On SLES 11 or later systems, this field is obsolete and returns a value of zero.
  • RelWgt

    SMALLINT,
    NOT NULL

  • On SLES 10 or earlier systems, this field returns the Active Relative Weight. That is, the dynamically assigned relative weight that considers, in its calculation, the activity of all other Allocation Groups present on the system. The RelWgt field constantly changes, unlike the relative weight assignment the Database Administrator assigns in the Teradata Viewpoint Workload Designer portlet.
  • RelWgt is the average relative weight of active online nodes (that is, divide the sum of the non-zero RelWgt by the count of online nodes with the non-zero RelWgt).

  • On SLES 11 or later systems, 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:
  • On SLES 10 or earlier systems, assigned to the PG at the end of the reporting period.
  • On SLES 11 or later systems, 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-i is 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)

    Note: 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.
  • Note: 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-i is calculated in each online node as:
  • WorkMsgsendDelayMax + WorkMsgReceiveDelayMax

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

    Note: NCPUs is the number of CPUs in the node.

  • i varies from 1 to N, where N is the number of online nodes.
  • Note: 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 (see “Glossary” for a description). 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.

    Note: 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 I/Os completed on behalf of this WD.

    IOCompletedKB

    FLOAT,
    NOT NULL

    KB of I/O completed on behalf of this WD.

    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.

    Note: DecayLevel1IO is used for Timeshare WDs only (see “Glossary” for a description of this workload management method).

    DecayLevel2IO

    FLOAT,
    NOT NULL

    Number of times SQL requests in the WD decay level 2 due to I/O.

    Note: DecayLevel2IO is used for Timeshare WDs only (see “Glossary” for a description of this workload management method).

    DecayLevel1CPU

    FLOAT,
    NOT NULL

    Number of times SQL requests in the WD hit decay level 1 due to CPU.

    Note: DecayLevel1CPU is used for Timeshare WDs only (see “Glossary” for a description of this workload management method).

    DecayLevel2CPU

    FLOAT,
    NOT NULL

    Number of times SQL requests in the WD hit decay level 2 due to CPU.

    Note: DecayLevel2CPU is used for Timeshare WDs only (see “Glossary” for a description of this workload management method).

    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 (see “Glossary” for a description of this workload management method).

    TacticalExceptionCPU

    FLOAT,
    NOT NULL

    Number of times SQL requests in the WD hit a tactical per-node exception due to CPU.

    Note: TacticalExceptionCPU is used for Tactical WDs only (see “Glossary” for a description of this workload management method).

    The following example shows how the parcels for a MONITOR WD request, built by CLIv2, appear when sent to the Teradata Database server.

    Note: 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

    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.

    The MONITOR WD request returns values approximately as shown below when TASM Workloads are enabled (see Teradata Viewpoint User Guide for information on this rule):

    Note: 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
                                           .
                                           .
                                           .