MonitorAWTResource Function | Application Programming Reference | Vantage - 17.10 - MonitorAWTResource - 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 statistics on AMPs based on the in-use AMP Worker Tasks (AWTs).

Syntax

REPLACE FUNCTION SYSLIB.MonitorAWTResource (
  Threshold1 INTEGER,
  Threshold2 INTEGER,
  Threshold3 INTEGER,
  Threshold4 INTEGER
) RETURNS TABLE (
    IntervalCount1 INTEGER,
    IntervalCount2 INTEGER,
    IntervalCount3 INTEGER,
    IntervalCount4 INTEGER,
    FlowControl INTEGER,
    HighAMP1VprocId INTEGER,
    HighAMP1InUseCount INTEGER,
    HighAMP2VprocId INTEGER,
    HighAMP2InUseCount INTEGER,
    HighAMP3VprocId INTEGER,
    HighAMP3InUseCount INTEGER,
    HighAMP4VprocId INTEGER,
    HighAMP4InUseCount INTEGER,
    HighAMP5VprocId INTEGER,
    HighAMP5InUseCount INTEGER,
    LowAMP1VprocId INTEGER,
    LowAMP1InUseCount INTEGER,
    LowAMP2VprocId INTEGER,
    LowAMP2InUseCount INTEGER,
    LowAMP3VprocId INTEGER,
    LowAMP3InUseCount INTEGER,
    LowAMP4VprocId INTEGER,
    LowAMP4InUseCount INTEGER,
    LowAMP5VprocId INTEGER,
    LowAMP5InUseCount INTEGER,
    FCVprocId1 INTEGER,
    FCVprocId2 INTEGER,
    FCVprocId3 INTEGER,
    FCVprocId4 INTEGER,
    FCVprocId5 INTEGER
  )
  ...
;

Syntax Elements

Threshold1
Minimum value for in-use AWTs to qualify an AMP for inclusion into this interval.
Threshold2
Threshold3
Threshold4
Start value for in-use AWTs to qualify an AMP for inclusion into this interval.
IntervalCountn
Where n in [1, 4]: Number of AMPs in the system whose in-use AWT counts fall at, or above, the Thresholdn value and do not qualify for the higher thresholds.
FlowControl
Number of AMPs currently in some form of Flow Control.
HighAMPnVprocId
Where n in [1, 5]: Vproc ID of the AMP with the nth highest in-use count in the system. A value of -1 indicates this field is not defined.
HighAMPnInUseCount
Where n in [1, 5]: In-use count associated with HighAMPnVprocId. Applicable only if HighAMPnVprocId is defined.
LowAMPnVprocId
Where n in [1, 5]: Vproc ID of the AMP with the nth lowest in-use count in the system. A value of -1 indicates this field is not defined.
LowAMPnInUseCount
Where n in [1, 5]: In-use count associated with LowAMPnVprocId. Applicable only if LowAMPnVprocId is defined.
FCVprocIdn
Where n in [1, 5]: The vproc ID of the AMP in flow control. A value of -1 indicates this field is not defined.

Usage Notes

The MonitorAWTResource functions are two overloaded functions. One requires the threshold values to be passed in; the other sets the threshold values to 45, 55, 62 and 75.

The MonitorAWTResource function provides similar functionality to the PMPC MONITOR AWT RESOURCE request. For information about this interface, see MONITOR AWT RESOURCE.

Example: Using MonitorAWTResource

SELECT * FROM TABLE (MonitorAWTResource(1,2,3,4)) AS t1;
 *** Query completed. One row found. 30 columns returned.
 *** Total elapsed time was 1 second.
    IntervalCount1            0
    IntervalCount2            0
    IntervalCount3            2
    IntervalCount4            4
       FlowControl            0
   HighAMP1VprocId            2
HighAMP1InUseCount            6
   HighAMP2VprocId            5
HighAMP2InUseCount            4
   HighAMP3VprocId            3
HighAMP3InUseCount            4
   HighAMP4VprocId            1
HighAMP4InUseCount            4
   HighAMP5VprocId            4
HighAMP5InUseCount            3
    LowAMP1VprocId            4
 LowAMP1InUseCount            3
    LowAMP2VprocId            0
 LowAMP2InUseCount            3
    LowAMP3VprocId            5
 LowAMP3InUseCount            4
    LowAMP4VprocId            3
 LowAMP4InUseCount            4
    LowAMP5VprocId            1
 LowAMP5InUseCount            4
        FCVprocId1           -1
        FCVprocId2           -1
        FCVprocId3           -1
        FCVprocId4           -1
        FCVprocId5           -1