15.10 - TDWMThrottleStatistics - Teradata Database

Teradata Database Application Programming Reference

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
featnum
B035-1090-151K

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

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
   )
                     .
                     .
                     .
;
 

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.

The TDWMThrottleStatistics function provides similar functionality to a TDWM STATISTICS request. For more information, see “TDWM STATISTICS” on page 360.

When issuing this Teradata dynamic workload management software function, it must be qualified by the database name, TDWM (see examples below).

 

Column Name

Description

ObjectType

Type of object for this statistic:

  • User
  • Account
  • Performance Group
  • 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.

    Note: 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
  • Example : Requesting 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 : Requesting 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 : Requesting 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 : Requesting 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