15.10 - MONITOR PHYSICAL SUMMARY - Teradata Database

Teradata Database Application Programming Reference

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

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
  •  

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

    For a general explanation of monitor version choices, see “MONITOR VERSION” on page 162.

    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:

  • Database Administration
  • Security Administration
  • Teradata JDBC Driver User Guide
  • 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
  • Note: 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 Teradata Database system has been restarted.

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

    The response returned from the Teradata 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 Teradata 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 chapter in Teradata Call-Level Interface Version 2 Reference for Mainframe-Attached Systems or Teradata Call-Level Interface Version 2 Reference for Workstation-Attached Systems.

    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.

    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” on page 104 for details.

    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” on page 104 for details.

    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” on page 104 for details.

    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” on page 104 for details.

    HighCPUProcId

    SMALLINT

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

    This value is NULL if certain conditions apply, see “Usage Notes” on page 104 for details.

    LowCPUUse

    FLOAT

    Lowest 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” on page 104 for details.

    LowCPUProcId

    SMALLINT

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

    This value is NULL if certain conditions apply, see “Usage Notes” on page 104 for details.

    HighDisk

    FLOAT

    Highest % disk usage (from DiskUse) 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” on page 104 for details.

    HighDiskProcId

    SMALLINT

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

    This value is NULL if certain conditions apply, see “Usage Notes” on page 104 for details.

    LowDisk

    FLOAT

    Lowest % disk usage (from DiskUse) 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” on page 104 for details.

    LowDiskProcId

    SMALLINT

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

    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 Teradata Database configuration.

    This value is NULL if certain conditions apply, see “Usage Notes” on page 104 for details.

    HighDiskIOProcId

    SMALLINT

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

    This value is NULL if certain conditions apply, see “Usage Notes” on page 104 for details.

    LowDiskIO

    FLOAT

    Lowest DiskReads and DiskWrite 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” on page 104 for details.

    LowDiskIOProcId

    SMALLINT

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

    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” on page 104 for details.

    Note: 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 Teradata Database software (for example, 15.00.00.00).

    This value is supplied by Teradata Database.

    Version

    VARCHAR (32),
    NOT NULL

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

    This value is supplied by Teradata 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.

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

    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

    28

    Request

    MONITOR PHYSICAL SUMMARY

    0003

    Data

    6

    MonVerID

    9

    0004

    Resp

    6

    BufferSize

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

    Success parcel: 
    StatementNo=1,    ActivityCount=1,
    ActivityType=94,  FieldCount=24
     
    AvgCPU:        99.87   AvgDisk:        29.47   AvgDiskIO:     14499.00
     
    HighCPUUse:    99.87   HighDisk:       29.47   HighDiskIO:    14499.00
    HighCPUProcId:    33   HighDiskProcId:    33   HighDiskIOProcId:    33
     
    LowCPUUse:     99.87   LowDisk:        29.47   LowDiskIO:     14499.00
    LowCPUProcId:     33   LowDiskProcId:     33   LowDiskIOProcId:    33
     
    NetUse:         0.00   NetAUp:  U   NetBUp:  U
     
    ResLogging:      600   ResMonitor:        30
     
    Release: 14f.00.00.249 Version: 14f.00.00.249
     
    Collection Date/Time:  06/15/2011 18:29:01.00
    EnStatement.
    EndRequest.

    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 Messages.

    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” on page 90.

    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.

    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.

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