TDWMSummary Function | Application Programming Reference | Teradata Vantage - 17.10 - TDWMSummary - 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)

Returns the Teradata dynamic workload management software WD summary data fields.

Syntax

REPLACE FUNCTION TDWM.TDWMSummary (
) RETURNS TABLE (
    WDId INTEGER,
    Arrivals INTEGER,
    Completions INTEGER,
    MinRespTime FLOAT,
    MaxRespTime FLOAT,
    AvgRespTime FLOAT,
    MinCPUTime FLOAT,
    MaxCPUTime FLOAT,
    AvgCPUTime FLOAT,
    DelayedCnt INTEGER,
    AvgDelayTime FLOAT,
    ExceptionCnt INTEGER,
    MetSLGCnt INTEGER,
    ActiveQueryCnt INTEGER,
    ActiveDelayedCnt INTEGER,
    ErrorCnt INTEGER,
    AbortCnt INTEGER,
    CollectionDate INTEGER,
    CollectionTime FLOAT,
    ExceptionAbCnt INTEGER,
    ExceptionMvCnt INTEGER,
    ExceptionCoCnt INTEGER,
    IntervalDelayCnt INTEGER,
    RejectedCount INTEGER,
    MovedInCount INTEGER,
    VirtualPartNum INTEGER,
    AvgIOWaitTime FLOAT,
    MaxIOWaitTime FLOAT,
    AvgOtherWaitTime FLOAT,
    MaxOtherWaitTime FLOAT,
    AvgCPURunDelay FLOAT,
    MaxCPURunDelay FLOAT,
    AvgSeqRespTime FLOAT,
    MaxSeqRespTime FLOAT,
    AvgLogicalIO FLOAT,
    MaxLogicalIO FLOAT,
    AvgLogicalKBs FLOAT,
    MaxLogicalKBs FLOAT,
    AvgPhysicalIO FLOAT,
    MaxPhysicalIO FLOAT,
    AvgPhysicalKBs FLOAT,
    MaxPhysicalKBs FLOAT,
    ActiveThrottleBypass INTEGER,
    FlexActive INTEGER,
    FlexComplete INTEGER,
    FlexArrivals INTEGER,
    DeferCnt     INTEGER,
    ActiveDeferCnt INTEGER,
    AvgDeferTime FLOAT
  )
  ...
;

Syntax Elements

WDId
WD ID.
Arrivals
Number of queries that were classified into this WD by the Teradata dynamic workload management software.
Completions
Number of queries that completed in this WD in this dashboard or logging interval with:
  • No error code or an error code of 3158
  • No abort flag
  • Either an AMP count of > 0 or a Parser CPU > 0
MinRespTime
Minimum response time in centiseconds of any query.
MaxRespTime
Maximum response time in centiseconds of any query.
AvgRespTime
Average response time in centiseconds for this workload based on the total response time of all completions divided by number of completions.
MinCPUTime
Minimum CPU in milliseconds of all the queries that completed in this workload in this dashboard or logging interval, where CPU is the minimum CPU used by any one AMP of the query plus the Parser CPU time used by that same query.
MaxCPUTime
Maximum CPU in milliseconds of all the queries that completed in this workload in this dashboard or logging interval, where CPU is the maximum CPU used by any one AMP of the query plus the Parser CPU time used by that same query.
AvgCPUTime
Running total in milliseconds of Parser CPU time plus the total AMP CPU of each query that completed in this workload divided by the number of completions of queries in this workload in this dashboard or logging interval.
DelayedCnt
Number of queries that are delayed in this workload.
AvgDelayTime
Average delay time in centiseconds of all the queries that are delayed in this workload.
ExceptionCnt
Number of queries with exceptions in this workload.
MetSLGCnt
Number of queries that met WD Response Time SLG requirements.
If the WD does not have Response Time SLG requirement, the query is still counted as met.
ActiveQueryCnt
Number of active queries in this workload.
ActiveDelayCnt
Number of queries currently delayed in this workload.
ErrorCnt
Number of queries that finished in this WD in this dashboard or logging interval with an error code > 0 but not 3158 or 3156.
AbortCnt
Number of queries that finished in this WD in this dashboard or logging interval with an error code of 0 or 3156 and an abort flag.
CollectionDate
Date of data collection.
CollectionTime
Time of data collection.
ExceptionAbCnt
Number of queries that aborted due to a TDWM exception.
ExceptionMvCnt
Number of queries that were moved into this WD due to an exception.
ExceptionCoCnt
Number of queries that hit an exception but continued in this WD.
IntervalDelayCnt
Number of queries that were delayed in this WD during this interval.
RejectedCount
Number of queries that were rejected by TDWM due to a filter or throttle rule violation.
MovedInCount
Number of queries that were moved into this WD either due to an exception action or manual move via PMPC.
VirtualPartNum
Virtual Partition number of the WD.
AvgIOWaitTime
Average duration of sleeps in milliseconds due to the I/O rate handling over the life of all requests completing in this WD during this interval.
MaxIOWaitTime
Maximum duration of sleeps in milliseconds due to the I/O rate handling of a request completing in this WD during this interval.
AvgOtherWaitTime
This column is reserved for future use.
MaxOtherWaitTime
This column is reserved for future use.
AvgCPURunDelay
Average wait time in milliseconds in the run queue of all the requests completing in this WD during this interval.
MaxCPURunDelay
Maximum wait time in milliseconds in the run queue of a request completing in this WD during this interval.
AvgSeqRespTime
Average of the sum of the response times of the steps (as if all the steps had been executed sequentially) of all the requests completing in this WD during this interval, in milliseconds.
MaxSeqRespTime
Maximum sum of the response times of the steps (as if all steps had been executed sequentially) of a request completing in this WD during this interval, in milliseconds.
AvgLogicalIO
Average count of logical I/Os for a request completing in this WD during this interval.
MaxLogicalIO
Maximum count of logical I/Os for a request completing in this WD during this interval.
AvgLogicalKBs
Average logical I/O usage in KB of all requests completing in this WD during this interval.
MaxLogicalKBs
Maximum logical I/O usage in KB for a request completing in this WD during this interval.
AvgPhysicalIO
Average count of physical I/Os of all requests completing in this WD during this interval.
MaxPhysicalIO
Maximum physical I/O usage of all requests completing in this WD during this interval.
AvgPhysicalKBs
Average physical I/O usage in KB of all requests completing in this WD during this interval.
MaxPhysicalKBs
Maximum physical I/O usage in KB for a request completing in this WD during this interval.
ActiveThrottleBypass
The number of queries reported in the Completions column that were allowed to run due to the ThrottleBypass ruleset attribute.
FlexActive
The number of queries that are currently active in the system that were released by the Flex Throttle feature.
FlexComplete
The number of queries that were released by the Flex Throttle feature that completed during this period.
FlexArrivals
The number of new requests that became active due to Flex Throttle feature.
DeferCnt
The number of queries in this WD that are deferred because of an arrival rate meter rule.
ActiveDeferCnt
Number of currently deferred queries in this WD.
AvgDeferTime
Average defer time due to an arrival rate meter rule, in centiseconds, for all queries assigned to this WD.

Usage Notes

The TDWMSummary function provides similar functionality to a TDWM Summary request. For more information about this interface, see TDWM SUMMARY.

TDWMSummary returns information for all queries completed in the dashboard interval for the WD.

Example: Using TDWMSummary

SELECT WDId, Arrivals, MetSLGCnt, ActiveReqs FROM TABLE (TDWM.TDWMSummary()) AS t2
 WHERE arrivals <>0;
 *** Query completed. 13 rows found. 4 columns returned.
 *** Total elapsed time was 1 second.
       WDId     Arrivals    MetSLGCnt   ActiveReqs
-----------  -----------  -----------  -----------
        213            8            8            0
        217            1            0            0
        221            3            3            0
        240          109           91            1