16.20 - MonitorAWTResource - Teradata Vantage NewSQL Engine

Teradata Vantage™ Application Programming Reference

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
created_date
March 2019
category
Programming Reference
featnum
B035-1090-162K

Purpose

Collects statistics on AMPs based on the in-use AMP Worker Tasks (AWTs).

Definition

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

Input Parameters

Parameter Description
Threshold1 Minimum value for in-use AWTs to qualify an AMP for inclusion into this interval.
Threshold2 Start value for in-use AWTs to qualify an AMP for inclusion into this interval.
Threshold3 Start value for in-use AWTs to qualify an AMP for inclusion into this interval.
Threshold4 Start value for in-use AWTs to qualify an AMP for inclusion into this interval.

Usage Notes - MonitorAWTResource

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.

Result Rows

Column Name Description
IntervalCount1 Number of AMPs in the system whose in-use AWT counts fall at, or above, the Threshold1 value and do not qualify for the higher thresholds.
IntervalCount2 Number of AMPs in the system whose in-use AWT counts fall at, or above, the Threshold2 value and do not qualify for the higher thresholds.
IntervalCount3 Number of AMPs in the system whose in-use AWT counts fall at, or above, the Threshold3 value and do not qualify for the higher thresholds.
IntervalCount4 Number of AMPs in the system whose in-use AWT counts fall at, or above, the Threshold4 value and do not qualify for the higher thresholds.
FlowControl Number of AMPs currently in some form of Flow Control.
HighAMP1VprocId Vproc ID of the AMP with the highest in-use count in the system. A value of -1 indicates this field is not defined.
HighAMP1InUseCount In-use count associated with the AMP 1 Vproc ID. This is only applicable if AMP 1 is defined.
HighAMP2VprocId Vproc ID of the AMP with the next highest in-use count in the system. A value of -1 indicates this field is not defined.
HighAMP2InUseCount In-use count associated with the AMP 2 Vproc ID. This is only applicable if AMP 2 is defined.
HighAMP3VprocId Vproc ID of the AMP with the next highest in-use count in the system. A value of -1 indicates this field is not defined.
HighAMP3InUseCount In-use count associated with the AMP 3 Vproc ID. This is only applicable if AMP 3 is defined.
HighAMP4VprocId Vproc ID of the AMP with the next highest in-use count in the system. A value of -1 indicates this field is not defined.
HighAMP4InUseCount In-use count associated with the AMP 4 Vproc ID. This is only applicable if AMP 4 is defined.
HighAMP5VprocId Vproc ID of the AMP with the next highest in-use count in the system. A value of -1 indicates this field is not defined.
HighAMP5InUseCount In-use count associated with the AMP 5 Vproc ID. This is only applicable if AMP 5 is defined.
LowAMP1VprocId Vproc ID of the AMP with the lowest in-use count in the system. A value of -1 indicates this field is not defined.
LowAMP1InUseCount In-use count associated with the AMP 1 Vproc ID. This is only applicable if AMP 1 is defined.
LowAMP2VprocId Vproc ID of the AMP with the lowest in-use count in the system. A value of -1 indicates this field is not defined.
LowAMP2InUseCount In-use count associated with the AMP 2 Vproc ID. This is only applicable if AMP 2 is defined.
LowAMP3VprocId Vproc ID of the AMP with the lowest in-use count in the system. A value of -1 indicates this field is not defined.
LowAMP3InUseCount In-use count associated with the AMP 3 Vproc ID. This is only applicable if AMP 3 is defined.
LowAMP4VprocId Vproc ID of the AMP with the lowest in-use count in the system. A value of -1 indicates this field is not defined.
LowAMP4InUseCount In-use count associated with the AMP 4 Vproc ID. This is only applicable if AMP 4 is defined.
LowAMP5VprocId Vproc ID of the AMP with the lowest in-use count in the system. A value of -1 indicates this field is not defined.
LowAMP5InUseCount In-use count associated with the AMP 5 Vproc ID. This is only applicable if AMP 5 is defined.
FCVprocId1,

FCVprocId2,

FCVprocId3,

FCVprocId4,

FCVprocId5

The vproc ID of the AMP in flow control. A value of -1 indicates this field is not defined.

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