15.10 - TDWMGetDelayedQueries - Teradata Database

Teradata Database Application Programming Reference

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
featnum
B035-1090-151K

Returns the delayed query data fields and delay information.

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

Parameter

Description

RequestType

O = Return the system query or system session delay queue

W = Return the workload delay queue

A = Return all delayed queues

This table function is only supported in Constant Mode.

 

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.

Note: 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.

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

    Example  

    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

    Example BlockingCnt 0

    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  

    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