TDWMThrottleStatistics Function | Application Programming Reference | Teradata Vantage - TDWMThrottleStatistics - Teradata Vantage

Returns statistics for throttled database system queries or sessions, throttled workloads, or all throttles.

Syntax

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,
    TimeUnit       VARCHAR(1) CHARACTER SET LATIN,
    CurQualifyTime INTEGER,
    ReqQualifyTime INTEGER
   )
  ...
;

Syntax Elements

RequestType

Request type:

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.

ObjectType

Type of object for this statistic:

User
Account
Account String
Profile
Client
Network Address
Application
Database
Table
Macro
Stored Procedure
Workload
Utility
Collection
View
Queryband
Function
Method
Virtual partition
RESAWT (AWT resource limit)
Arrival Rate Meter
RESMEM (memory resource limit)

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.

If ObjectType is Arrival Rate Meter, this value is used with TimeUnit to describe the current rate. The current rate is Active/TimeUnit, such as 8 (requests)/minute.

If ObjectType is RESMEM, this value is the current total memory usage, in megabytes, of active requests that qualify for a specific memory resource limit rule.

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.

If ObjectType is Arrival Rate Meter, this is the arrival rate limit.

If ObjectType is RESMEM, this is the memory limit, in megabytes, for requests that qualify for a specific memory resource limit rule.

Delayed

Number of requests on the delay queue at this time for this object.

If ObjectType is Arrival Rate Meter, this is the number of requests being deferred by the arrival rate meter.

ThrottleType

Type of throttle statistic. Possible values include:

Q = Throttled database system query
S = Throttled database system session
W = Throttled workload

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

TimeUnit

Time unit of the arrival rate meter:

H = hour
M = minute
S = second

CurQualifyTime

Number of seconds in which the current arrival rate has continuously remained at the limit.

ReqQualifyTime

Number of seconds in which the current arrival rate must continuously remain at the limit before new requests are held in the Defer queue.

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 the following examples).

Example: Using TDWMThrottleStatistics to Request Query Throttle Statistics

select * from table (TDWM.TDWMThrottleStatistics('Q')) AS t1;

Result:

 *** 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;

Result:

 *** 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;

Result:

*** 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;

Result:

 *** 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