Purpose
Returns statistics for throttled database system queries or sessions, throttled workloads, or all throttles.
Definition
REPLACE FUNCTION TDWM.TDWMThrottleStatistics
(RequestType VARCHAR(1) CHARACTER SET LATIN
)
RETURNS TABLE
(ObjectType VARCHAR(17) CHARACTER SET LATIN,
RuleId INTEGER,
RuleName VARCHAR(128) CHARACTER SET UNICODE,
ObjectName VARCHAR(128) CHARACTER SET UNICODE,
SubName VARCHAR(128) CHARACTER SET UNICODE,
Active INTEGER,
ThrottleLimit INTEGER,
Delayed INTEGER,
ThrottleType VARCHAR(1) CHARACTER SET LATIN,
FlexEligible SMALLINT
)
.
.
.
;
Input Parameter
| Parameter | Description |
|---|---|
| RequestType | A = Return all throttle statistics. Q = Return statistics for throttled database system queries. S = Return statistics for throttled database system sessions. W = Return statistics for throttled workloads. |
Usage Notes
The TDWMThrottleStatistics function provides similar functionality to a TDWM STATISTICS request. For more information, see TDWM STATISTICS.
When issuing this Teradata dynamic workload management software function, it must be qualified by the database name, TDWM (see examples below).
Result Rows
| Column Name | Description |
|---|---|
| ObjectType | Type of object for this statistic:
|
| RuleId | When this field is a workload, it contains the workload ID. |
| RuleName | When this field is a workload, it contains the workload name. |
| ObjectName | For table, macro, stored procedure, view, function, and method object types, this is the name of the qualifying database. For all other types, this is the name of the object. For more information on these object types, see the ObjectTypes column. |
| SubName | Name of the table, macro, stored procedure, view, function, or method. |
| Active | Number of requests currently active for this object. This is, the number of active queries or sessions depending on the information requested in the input record. |
| ThrottleLimit | Current Teradata dynamic workload management software limit on the object. This is the limit on the number of queries or sessions depending on the information requested in the input record. A value of -1 indicates that no system query or session throttle limit is defined. Active and delayed sessions are not valid when the ThrottleLimit value is -1.
|
| Delayed | Number of requests on the delay queue at this time for this object. |
| ThrottleType | Type of throttle statistic. Possible values include:
|
| 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 TDWMThrottleStatistics to Request Query Throttle Statistics
select * from table (TDWM.TDWMThrottleStatistics('Q')) AS t1;
*** Query completed. 4 rows found. 8 columns returned.
*** Total elapsed time was 1 second.
ObjectType RuleId RuleName ObjectName Subname Active ThrottleLimit Delayed
---------- ------ -------- ---------- ------- ------ ------------- -------
User 1 test2 LUMBER 2 2 1
Use 2 test4 LUMBER 1 1 0
Table 3 table test LUMBER ORDERS 1 1 0
Macro 4 macro test LUMBER VENDMACRO 0 1 1
Example: Using TDWMThrottleStatistics to Request Session Throttle Statistics
select * from table (TDWM.TDWMThrottleStatistics('S')) AS t1;
*** Query completed. 2 rows found. 8 columns returned.
*** Total elapsed time was 1 second.
ObjectType RuleId RuleName ObjectName Subname Active ThrottleLimit Delayed
---------- ------ -------- ---------- ------- ------ ------------- -------
User 1 test2 LUMBER 2 4 0
User 2 test4 LUMBER 2 2 0
Example: Using TDWMThrottleStatistics to Request Workload Throttle Statistics
select * from table (TDWM.TDWMThrottleStatistics('W')) AS t1;
*** Query completed. 2 rows found. 8 columns returned.
*** Total elapsed time was 1 second.
ObjectType RuleId RuleName ObjectName Subname Active ThrottleLimit Delayed
---------- ------ ----------- ---------- ------- ------ ------------- -------
Workload 0 -1 0 1
Workload 2 WD-ConsoleR 0 -1 0
Workload 3 WD-ConsoleH 0 -1 0
Workload 4 WD-ConsoleM 0 -1 0
Workload 5 WD-ConsoleL 0 -1 0
Workload 1 WD-Default 0 -1 0
Workload 6 test-wd 0 0 2
Example: Using TDWMThrottleStatistics to Request All Throttle Statistics
SELECT ObjectType(FORMAT 'x(10)'), rulename(FORMAT 'x(17)'),
ObjectName(FORMAT 'x(13)'), active(FORMAT 'Z9'),
throttlelimit as ThrLimit, delayed(FORMAT 'Z9'), throttletype as ThrType
FROM TABLE (TDWM.TDWMTHROTTLESTATISTICS('A')) AS t1
ORDER BY 1,2;
*** Query completed. 10 rows found. 7 columns returned.
*** Total elapsed time was 1 second.
ObjectType RuleName ObjectName Active ThrLimit Delayed ThrType
---------- ----------------- ------------ ------ -------- ------- -------
Table t900throttlerule7 T9TESTUSER 1 1 6 Q
User session919rule T900APIUSER4 4 5 0 S
Workload WD-Default 0 -1 0 W
Workload WD1 0 -1 0 W
Workload WD3 1 1 0 W
Workload WD345678901234567 1 1 3 W
Workload WD4 0 1 0 W
Workload WD5 0 -1 0 W
Workload WD6 0 -1 0 W
Workload WD7 0 -1 0 W