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