Examples: Using MonitorPhysicalResourceSV - Teradata Vantage

Example: Tracking Resource Usage While a Query is Running

SELECT ProcId, CPUUse, Id, "Type" from SYSLIB.MonitorPhysicalResourceSV(
    USING
    details('1')
) 
AS dt;

The output for a single compute system is similar to this:

|ProcId|CPUUse      |Id                                                 |Type           |
|------|------------|---------------------------------------------------|---------------|
|10,001|0.3871875   |pog-64ccdd083c                                     |primary cluster|
|20,001|0.1932591218|sys-64ccddbd6c_computegroup1_computeprofile1_tv5cug|compute cluster|

Example: Displaying Basic Configuration Details

This query displays basic configuration details for each compute cluster in a system.

SELECT "TYPE"               AS COMPONENT_TYPE
      ,ID                   AS COMPONENT_ID
      ,COUNT(*)             AS NODE_CNT
      ,SUM(AMPCOUNT)        AS AMP_CNT
      ,SUM(PECOUNT)         AS PE_CNT
  FROM MonitorPhysicalResourceSV(
       USING DETAILS('1')
       ) AS DT
GROUP BY 1,2
ORDER BY 1,2
; 

Identifying the Status of All Nodes by Compute Cluster

This query identifies the status of all nodes by compute cluster and reports on the current status (online, offline) of the nodes in each compute cluster.

SELECT "TYPE"               AS COMPONENT_TYPE
      ,ID                   AS COMPONENT_ID
      ,SUM(CASE WHEN STATUS = 'U' THEN 1 ELSE 0 END) AS NODES_ONLINE
      ,SUM(CASE WHEN STATUS = 'D' THEN 1 ELSE 0 END) AS NODES_OFFLINE
  FROM MonitorPhysicalResourceSV(
USING DETAILS('1')
       ) AS DT
GROUP BY 1,2
ORDER BY 1,2;