TDWMThrottleStatistics Function | Application Programming Reference | Vantage - TDWMThrottleStatistics - Advanced SQL Engine - Teradata Database

Application Programming Reference
Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
hvk1593628831140.ditamap
dita:ditavalPath
hvk1593628831140.ditaval
dita:id
B035-1090
lifecycle
previous
Product Category
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
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.
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.
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 examples below).

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