16.20 - TDWMThrottleStatistics - Teradata Vantage NewSQL Engine

Teradata Vantage™ Application Programming Reference

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
created_date
March 2019
category
Programming Reference
featnum
B035-1090-162K

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:
  • 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)
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:
  • 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

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