TDWMGetDelayedQueries Function | Application Programming Reference | Teradata Vantage - TDWMGetDelayedQueries - Teradata Vantage

Returns the delayed query data fields and delay information.

This table function is only supported in Constant Mode.

Syntax

REPLACE FUNCTION TDWM.TDWMGetDelayedQueries (
  RequestType VARCHAR(1) CHARACTER SET LATIN
) RETURNS TABLE (
    Username          VARCHAR(128) CHARACTER SET UNICODE,
    HostId            SMALLINT,
    SessionNo         INTEGER,
    RequestNo         INTEGER,
    RuleName          VARCHAR(128) CHARACTER SET UNICODE,
    RuleId            INTEGER,
    TotalTimeHeld     INTEGER,
    OverRidable       CHAR CHARACTER SET LATIN,
    BlockingCnt       INTEGER,
    PEId              INTEGER,
    WDDelayed         INTEGER,
    ObjDelayed        INTEGER,
    UtilDelayed       INTEGER,
    RuleType          INTEGER,
    GroupDelayed      INTEGER,
    FlexEligible      SMALLINT,
    MeterDeferred     INTEGER
  )
  ...
;

Syntax Elements

RequestType

Request type:

O = Return the system query or system session delay queue
W = Return the workload delay queue
A = Return queries on the Delay and Defer queue.
M = Return queries on the Defer queue only.

Username

Logon user name of session.

HostId

Host ID of the session number for the delayed request.

SessionNo

Session number for the held request.

RequestNo

Request number for the delayed request.

RuleName

Rule name identified in the Rule ID field.

RuleId

Rule ID for the workload or the system query or system session throttle rule that caused the query to be delayed or the rule ID for an arrival rate meter that caused the query to be deferred.

TotalTimeHeld

Total number of wall clock seconds that this request or session has been held.

BlockingCnt

Count of the number of consecutive times that this request has been identified as blocking at least one other session.

The value is zero if Request Flag is 6.

OverRidable

Request or session allowed to be aborted or released by the administrator. A session cannot be released if it exceeds the internal AMP worker task limit. A delayed session can always be aborted.

If the value is Y, the request or session is overridable.

If the value is N, the request or session is not overridable.

The queue table requests are controlled internally by the database and cannot be altered by the administrator.

If Request Flag is 6, this field indicates if the delayed session can be released. A session cannot be released if it exceeds the internal AMP worker task limit or an internal utility limit.

A delayed session can always be aborted.

PEId

PE VPROC ID which initiated this request.

WDDelayed

Indicator that the request is delayed for a workload rule.

A value of zero indicates the values is not delayed because of a workload throttle.

ObjDelayed

Indicator that the request is delayed for a system query or system session throttle rule.

A value of zero indicates the request is not delayed because of a system query or system session throttle.

UtilDelayed

Indicator that the request is delayed for a Utility rule.

A value of zero indicates the request is not delayed because of a utility throttle.

RuleType

Rule type for the request:

0 = Workload
1 = System
2 = Utility
3 = Workload Group
4 = Virtual Partition
5 = Resource Limit (AWT or Memory)
6 = Arrival Rate Meter

GroupDelayed

Whether Query is delayed for a workload group throttle: 1 (yes) or 0 (no).

This field is only valid on monitor version software ID 10 or later.

FlexEligible

The delayed request that is in a workload and is tagged as eligible for the Flex Throttle feature. Note, that flagged requests must meet all requirements for Flex Throttles before being released, such as system throttle restrictions.

0 = Flex eligible
1 = Not Flex eligible

MeterDeferred

Output parameter. It is the indicator of a request being deferred for an arrival rate meter.

0 = Query is not deferred
1 = Query is deferred

Example: Using TDWMGetDelayedQueries to Get the System Query or System Session Delay Queue

This example shows how to get the system query or system session delay queue.

SELECT * FROM TABLE (TDWM.TDWMGetDelayedQueries('O')) AS t1;

where 'O' is for system query or system session delay queue.

*** Query completed. One row found. 9 columns returned.
 *** Total elapsed time was 1 second.
   Username  LUMBER
     HostId       1
  SessionNo      1010
  RequestNo            3
     WDName  test2
       WDId            1
   TimeHeld        6676
OverRidable   Y
BlockingCnt            0

Example: Using TDWMGetDelayedQueries to Get the Workload Delay Queue

This example shows how to get the workload delay queue.

SELECT * FROM TABLE (TDWM.TDWMGetDelayedQueries('W')) AS t1;

where 'W' is for workload delay queue.

*** Query completed. 2 rows found. 9 columns returned.
*** Total elapsed time was 1 second.
   Username LUMBER
     HostId 1
  SessionNo 1008
  RequestNo 3
     WDName test-wd
       WDId 6
   TimeHeld 7131
OverRidable Y
BlockingCnt 0
   Username LUMBER
     HostId 1
  SessionNo 1009
  RequestNo 3
     WDName test-wd
       WDId 6
   TimeHeld 7114
OverRidable Y
BlockingCnt 0

Example: Using TDWMGetDelayedQueries to Get All Delay Queues

This example shows how to get all the delay queues.

SELECT * FROM TABLE (TDWM.TDWMGetDelayedQueries('A')) AS t1;

where 'A' or null is for all delay queues.

*** Query completed. 3 rows found. 9 columns returned.
*** Total elapsed time was 1 second.
Username HostId  SessionNo  RequestNo  WDName  WDId  TimeHeld  OverRidable
-------- ------  ---------  ---------  ------  ----  --------  -----------
LUMBER        1       1010          3  test2      1      7717  Y
LUMBER        1       1008          3  test       6      7748  Y
LUMBER        1       1009          3  test       6      7731  Y