MONITOR PHYSICAL SUMMARY Request | Application Programming Reference | Vantage - 17.05 - MONITOR PHYSICAL SUMMARY - Teradata Database

Teradata Vantage™ - Application Programming Reference

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
17.05
created_date
June 2020
category
Programming Reference
featnum
B035-1090-170K

Purpose

Collects global summary information that includes the following types of information:
  • CPU usage (average, high, and low)
  • Disk usage (average, high, and low)
  • BYNET usage (total, up/down)
  • Rate information (resource logging rate and resource monitoring rate)
  • Current software release and version numbers

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 2 or later.

For a general explanation of monitor version choices, see MONITOR VERSION.

Monitor Privileges

To use this request, you must have any one of the following monitor privileges as part of your default role or any of these privileges must be granted directly to you:
  • ABORTSESSION
  • MONRESOURCE
  • MONSESSION
  • SETRESRATE
  • SETSESSRATE
For more information on roles and privileges, see:

Usage Notes - MONITOR PHYSICAL SUMMARY

For some of the MONITOR PHYSICAL SUMMARY and MonitorPhysicalSummary fields, NULL returns if:
  • A node is down or offline
  • The ResMonitor is set to zero
    You must set the ResMonitor rate to a nonzero value to allow the MONITOR PHYSICAL SUMMARY request or MonitorPhysicalSummary function to return meaningful data.
  • A request for data is made before completion of the first collection period following either a system outage or a change in the ResMonitor rate

    After a system outage or a change in the ResMonitor rate, do not request data again until after completion of the first collection period requested after the crash or change in rate. Otherwise, the data returned will contain NULL except NetAUp, NetBUp, SampleSec, ProcId, AMPCount, PECount, and Status, and may not be fully representative. The in-memory counters reset after a crash. Typically the contents of the counters are not well-defined until a full collection period has elapsed. If you were logged on prior to the system outage, and you issue the first MONITOR PHYSICAL SUMMARY request or MonitorPhysicalSummary function after the outage, you will receive a warning that the database system has been restarted.

If PE only (AMP-less) nodes exist and if the nodes should be included in the MONITOR PHYSICAL SUMMARY statistics calculation, you must set the DBS Control field, MPS_IncludePEOnlyNodes, to TRUE.

In normal use of Monitor Physical Summary, the MPS_IncludePEOnlyNodes field is set to FALSE.

By default, the statistics for PE only nodes are excluded in the calculation of MONITOR PHYSICAL SUMMARY statistics. For information about the MPS_IncludePEOnlyNodes field, see Teradata Vantage™ - Database Utilities , B035-1102 .

CLIv2 Response Parcels

The response returned from the database resembles a summary of the type of response returned by a MONITOR PHYSICAL SUMMARY request. The response is one row of 22 fields. The response returned from the database contains the following sequence of parcel types.

Parcel Sequence Parcel Flavor Length

(Bytes)

Comments/Key Parcel Body Fields
Success 8 18 to 273 StatementNo = 1

ActivityCount = 1

ActivityType = 94 (PCLMONPSUMMARY)

DataInfo 71 6 to 64100 Optional; this parcel is present if request was IndicData parcel.
Record 10
  • 5 to 64100 (record mode)
  • 6 to 64100 (indicator mode)
Depending on request (Data or IndicData), data is in record or indicator mode. This record contains the Data or IndicData physical summary information and the date and time the Physical Resource cache was last refreshed.
EndStatement 11 6 StatementNo = 2-byte integer
EndRequest 12 4 None

For more information on parcel body fields, see the appropriate section in Teradata® Call-Level Interface Version 2 Reference for Mainframe-Attached Systems, B035-2417 or Teradata® Call-Level Interface Version 2 Reference for Workstation-Attached Systems, B035-2418.

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 .

The following table describes the resource usage information returned from the Record parcel for the MONITOR PHYSICAL SUMMARY response.

Field/Column Name Data Type Description
AvgCPU FLOAT Average % CPU usage (CPUUse) time of all online nodes currently in the Teradata Database configuration.

This value is NULL if certain conditions apply, see Usage Notes - MONITOR PHYSICAL SUMMARY.

AvgDisk FLOAT Average % disk usage (from DiskUse) of all online nodes currently in the Teradata Database configuration.

This value is NULL if certain conditions apply, see Usage Notes - MONITOR PHYSICAL SUMMARY.

AvgDiskIO FLOAT Average number DiskReads and DiskWrites for all online nodes currently in the Teradata Database configuration.

This value is NULL if certain conditions apply, see Usage Notes - MONITOR PHYSICAL SUMMARY.

HighCPUUse FLOAT Highest CPUUse number associated with any online node that is currently part of the Teradata Database configuration.

This value is NULL if certain conditions apply, see Usage Notes - MONITOR PHYSICAL SUMMARY.

HighCPUProcId INTEGER ID of a node with CPPUse equal to the value reported as HighCPUUse.

This value is computed as the module number within a cabinet plus the cabinet number times 10000. For example, a node #123 on cabinet #4 would return an INTEGER value of 40123.

This value is NULL if certain conditions apply, see Usage Notes - MONITOR PHYSICAL SUMMARY.

LowCPUUse FLOAT Lowest CPUUse number associated with any online node that is currently part of the database configuration.

This value is NULL if certain conditions apply, see Usage Notes - MONITOR PHYSICAL SUMMARY.

LowCPUProcId INTEGER ID of a node with CPPUse equal to the value reported as LowCPUUse.

This value is computed as the module number within a cabinet plus the cabinet number times 10000. For example, a node #123 on cabinet #4 would return an INTEGER value of 40123.

This value is NULL if certain conditions apply, see Usage Notes - MONITOR PHYSICAL SUMMARY.

HighDisk FLOAT Highest % disk usage (from DiskUse) associated with any online node that is currently part of the database configuration.

This value is NULL if certain conditions apply, see Usage Notes - MONITOR PHYSICAL SUMMARY.

HighDiskProcId INTEGER ID of a node with DiskUse equal to the value reported as HighDisk.

This value is computed as the module number within a cabinet plus the cabinet number times 10000. For example, a node #123 on cabinet #4 would return an INTEGER value of 40123.

This value is NULL if certain conditions apply, see Usage Notes - MONITOR PHYSICAL SUMMARY.

LowDisk FLOAT Lowest % disk usage (from DiskUse) associated with any online node that is currently part of the database configuration.

This value is NULL if certain conditions apply, see Usage Notes - MONITOR PHYSICAL SUMMARY.

LowDiskProcId INTEGER ID of a node with DiskUse equal to the value reported as LowDisk.

This value is computed as the module number within a cabinet plus the cabinet number times 10000. For example, a node #123 on cabinet #4 would return an INTEGER value of 40123.

This value is NULL when LowDisk is NULL.

HighDiskIO FLOAT Highest DiskReads and DiskWrites number associated with any online node that is currently active in the database configuration.

This value is NULL if certain conditions apply, see Usage Notes - MONITOR PHYSICAL SUMMARY.

HighDiskIOProcId INTEGER ID of a node with DiskReads and DiskWrites equal to the value reported as HighDiskIO.

This value is computed as the module number within a cabinet plus the cabinet number times 10000. For example, a node #123 on cabinet #4 would return an INTEGER value of 40123.

This value is NULL if certain conditions apply, see Usage Notes - MONITOR PHYSICAL SUMMARY.

LowDiskIO FLOAT Lowest DiskReads and DiskWrites number associated with any online node that is currently part of the database configuration.

This value is NULL if certain conditions apply, see Usage Notes - MONITOR PHYSICAL SUMMARY.

LowDiskIOProcId INTEGER ID of a node with DiskReads and DiskWrites equal to the value reported as LowDiskIO.

This value is computed as the module number within a cabinet plus the cabinet number times 10000. For example, a node #123 on cabinet #4 would return an INTEGER value of 40123.

This value is NULL when LowDiskIO is NULL.

NetUse FLOAT % of total BYNET use (that is, average of the online BYNETs).

If both BYNETs are up, the value is computed from ResUsageSpma table data as:

NetUse = Average NetAUse per node / NetCount

where:
  • NetCount is 2 if both NetA and NetB are up or 1 if only one of the BYNET is up.
  • Average NetAUse is the sum of all NetAUse of each node divided by the number of online nodes.

This value is NULL if certain conditions apply, see Usage Notes - MONITOR PHYSICAL SUMMARY.

NetUse returns a value of zero because resource usage data is not currently available.
NetAUp VARCHAR (1)

NOT NULL

Status of the BYNETs (if there are more than two, the first two) on a system-wide basis:
  • U = All node BYNETs are up/online.
  • D = One or more node BYNETs is down/offline.
  • “” = A temporary condition where the BYNET data is not available.
NetBUp VARCHAR (1)

NOT NULL

Status of the BYNETs (if there are more than two, the first two) on a system-wide basis:
  • U = All node BYNETs are up/online.
  • D = One or more node BYNETs is down/offline.
  • “” = A temporary condition where the BYNET data is not available.
ResLogging SMALLINT

NOT NULL

range 0-3600 seconds,

Interval in seconds at which resource usage data is written to one or more active resource usage database tables.
ResMonitor SMALLINT

NOT NULL

range 0-3600 seconds

Interval in seconds at which all resource usage data is collected in memory for reporting via the PM/API.
Release VARCHAR (29)

NOT NULL

Release number of the currently running database software (for example, 15.00.00.00).

This value is supplied by the database.

Version VARCHAR (32)

NOT NULL

Version number of the currently running database software (for example, 15.00.00.00).

This value is supplied by the database.

CollectionDate DATE

NOT NULL

Date the Physical Resource cache was last refreshed.
CollectionTime FLOAT

NOT NULL

Time the Physical Resource cache was last refreshed.

Sample Input - CLIv2 Request

The following example shows how the parcels for a MONITOR PHYSICAL SUMMARY request, built by CLIv2, look when sent to the database.

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 28 Request MONITOR PHYSICAL SUMMARY
0003 Data 6 MonVerID 9
0004 Resp 6 BufferSize 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 following example shows the values returned in character text format for the MONITOR PHYSICAL SUMMARY request. Your application program may display returned values in a different format.

Submitting request MONITOR PHYSICAL SUMMARY; ...

AvgCPU:       100.00   AvgDisk:        10.75   AvgDiskIO:      6536.00

HighCPUUse:   100.00   HighDisk:      100.00   HighDiskIO:       10.75
HighCPUProcId: 10001   HighDiskProcId: 10001   HighDiskIOProcId: 10001

LowCPUUse:     10.75   LowDisk:      6536.00   LowDiskIO:      6536.00
LowCPUProcId:  10001   LowDiskProcId:  10001   LowDiskIOProcId: 10001

NetUse:         0.00   NetAUp:  U   NetBUp:  U

ResLogging:       60   ResMonitor:        60

Release: 16t.00.00.97                    Version: 16t.00.00.97_dr182707j
Collection Date/Time:   88/64/110954 00:00:00.00

Warning and Error Messages

All users who are logged on and issue a MONITOR PHYSICAL SUMMARY request after a system restart or after the last rate change can expect to receive a warning. Users will also receive a warning if the resource monitoring rate (ResMonitor) is set to zero.

Either MONITOR PHYSICAL SUMMARY or MONITOR PHYSICAL RESOURCE requests issues a warning for any sessions logged on prior to the database recovery, or prior to the change in the ResMonitor collection rate.

For more detailed information on warning and error messages, see Teradata Vantage™ - Database Messages, B035-1096.

Relationship Between MONITOR PHYSICAL SUMMARY and MONITOR PHYSICAL CONFIG

Use the MONITOR PHYSICAL SUMMARY request with the MONITOR PHYSICAL CONFIG request for an overall system status. These are low overhead requests.
  • Execute the MONITOR PHYSICAL SUMMARY request every 5 or 10 minutes for a low-cost, continuous monitoring of your system.
  • Execute the MONITOR PHYSICAL CONFIG request to get a picture of your system configuration at defined times, such as at the beginning of a day, various times during the day, or when the system is down.

For information on this PMPC CLIv2 or the Teradata JDBC Driver request relationship, see Relationship Between MONITOR PHYSICAL CONFIG and MONITOR PHYSICAL SUMMARY.

Relationship Between MONITOR PHYSICAL SUMMARY and SET RESOURCE RATE

The SET RESOURCE RATE request sets the ResMonitor and ResLogging rates, which are among the responses returned by the MONITOR PHYSICAL SUMMARY or MONITOR VIRTUAL SUMMARY request. Any change to either the ResMonitor or ResLogging rate results in changes in the corresponding response returned by the MONITOR VIRTUAL SUMMARY or MONITOR PHYSICAL SUMMARY request.

You must set ResMonitor to a nonzero rate for MONITOR PHYSICAL SUMMARY or MONITOR VIRTUAL SUMMARY to return meaningful resource utilization data. A zero ResMonitor rate returns NULL for resource utilization information.

Relationship Between MONITOR PHYSICAL SUMMARY and SET SESSION RATE

Changes to the session-level rates (global and local) specified by SET SESSION RATE are reported in the data returned by MONITOR PHYSICAL SUMMARY or MONITOR VIRTUAL SUMMARY.

The local rate reported is your own local rate. If the local rate is not set, the local rate is reported as zero.

As more session-level monitoring is done (by setting a faster SET SESSION RATE), the resulting overhead may increase the level of CPU usage (reported in MONITOR PHYSICAL SUMMARY or MONITOR VIRTUAL SUMMARY data) by your system. However, this may depend on the size of the rate change and the type of work done by other sessions.