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

Teradata Vantageā„¢ - Application Programming Reference

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
17.05
created_date
June 2020
category
Programming Reference
featnum
B035-1090-170K

Purpose

Returns the delayed query data fields and delay information.

Definition

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

Input Parameter

Parameter Description
RequestType O = Return the system query or system session delay queue

W = Return the workload delay queue

A = Return all delayed queues

Usage Notes

This table function is only supported in Constant Mode.

Result Rows

Column Name Description
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.
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
GroupDelayed 1= Query is delayed for a workload group throttle.

0= Query is not delayed for a workload group throttle.

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

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