TDWMGetDelayedQueries Function | Application Programming Reference | Vantage - 17.10 - TDWMGetDelayedQueries - 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 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 = AWT Resource Limit
  • 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