MonitorPhysicalSummary Function | Application Programming Reference | Vantage - 17.10 - MonitorPhysicalSummary - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Application Programming Reference

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1090-171K
Language
English (United States)
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

Syntax

REPLACE FUNCTION SYSLIB.MonitorPhysicalSummary (
) RETURNS TABLE (
    AvgCPU FLOAT, 
    AvgDisk FLOAT, 
    AvgDiskIO FLOAT, 
    HighCPUUse FLOAT, 
    HighDisk FLOAT, 
    HighDiskIO FLOAT, 
    HighCPUProcId INTEGER,
    HighDiskProcId INTEGER,
    HighDiskIOProcId INTEGER,
    LowCPUUse FLOAT, 
    LowDisk FLOAT, 
    LowDiskIO FLOAT, 
    LowCPUProcId INTEGER,
    LowDiskProcId INTEGER,
    LowDiskIOProocId INTEGER,
    NetUse FLOAT, 
    NetAUp CHAR(1)CHARACTER SET LATIN,
    NetBUp CHAR(1)CHARACTER SET LATIN,
    ResLogging SMALLINT,
    ResMonitor SMALLINT,
    ReleaseNum VARCHAR(30)CHARACTER SET LATIN,
    Version VARCHAR(32) CHARACTER SET LATIN
  )
  ...
;

Syntax Elements

AvgCPU
Average % CPU usage (CPUUse) time of all online nodes currently in the database configuration.
This value is NULL if certain conditions apply, see usage notes.
AvgDisk
Average % disk usage (from DiskUse) of all online nodes currently in the database configuration.
Assuming n is the number of online AMPs in the configuration, AMPAvgDisk is computed from DiskUse data as:
(DiskUse 1+ ... + DiskUse n) / n
This value is NULL if certain conditions apply, see usage notes.
AvgDiskIO
Average physical disk DiskReads and DiskWrites of all online AMPs in the configuration.
Assuming n is the number of online AMPs in the configuration, AMPAvgDiskIO is computed from DiskReads and DiskWrites data as:
(DiskReads 1+ DiskWrites 1+ ... + DiskReads 1+ DiskWrites n) / n
This value is NULL if certain conditions apply, see usage notes.
HighCPUUse
Highest 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.
HighDisk
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.
HighDiskIO
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.
HighCPUProcId
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.
HighDiskProcId
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.
HighDiskIOProcId
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.
LowCPUUse
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.
LowDisk
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.
LowDiskIO
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.
LowCPUProcId
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.
LowDiskProcId
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 if certain conditions apply, see usage notes.
LowDiskIOProocId
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 if certain conditions apply, see usage notes.
NetUse
% 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.
NetUse returns a value of zero because resource usage data is not currently available.
NetAUp
NetBUp
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
Interval in seconds at which resource usage data is written to one or more active resource usage database tables.
ResMonitor
Interval in seconds at which all resource usage data is collected in memory for reporting via the PM/API.
ReleaseNum
Release number of the currently running database software (for example, 15.00.00.00).
This value is supplied by the database.
Version
Version number of the currently running database software (for example, 15.00.00.00).
This value is supplied by the database.

Usage Notes

The MonitorPhysicalSummary function provides similar functionality to the PMPC MONITOR PHYSICAL SUMMARY request. For information about this interface, see MONITOR PHYSICAL SUMMARY.

Example: Using MonitorPhysicalSummary

SELECT * FROM TABLE (MonitorPhysicalSummary()) AS t1;

 *** Query completed. One row found. 22 columns returned.
 *** Total elapsed time was 1 second.

          AvgCPU   1.00000000000000E 002
         AvgDisk   7.51986754966887E 000
       AvgDiskIO   6.64700000000000E 003
      HighCPUUse   1.00000000000000E 002
        HighDisk   7.51986754966887E 000
      HighDiskIO   6.64700000000000E 003
   HighCPUProcId        10001
  HighDiskProcId        10001
HighDiskIOProcId        10001
       LowCPUUse   1.00000000000000E 002
         LowDisk   7.51986754966887E 000
       LowDiskIO   6.64700000000000E 003
    LowCPUProcId        10001
   LowDiskProcId        10001
 LowDiskIOProcId        10001
          NetUse   0.00000000000000E 000
          NetAUp  U
          NetBUp  U
      ResLogging      60
      ResMonitor      60
      ReleaseNum  16t.00.00.97
         Version  16t.00.00.97_dr182707k