TDWMAddClassificationForRule | Application Programming Reference | Vantage - 17.10 - TDWMAddClassificationForRule - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Application Programming Reference

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1090-171K
Language
English (United States)

Adds one classification criterion for a specific TASM rule in one or all existing rulesets. This step is repeated for each additional classification criterion, if necessary. This XPS is intended to support any TASM rule type (for example, throttle, arrival rate meter, workload, filter, and so on.). However, only system throttle and arrival rate meter are supported in this release.

Syntax

REPLACE PROCEDURE TDWM.TDWMAddClassificationForRule (
  IN RulesetName TD_ANYTYPE,
  IN RuleName TD_ANYTYPE,
  IN Description TD_ANYTYPE,
  IN ClassifcationType VARCHAR(20) CHARACTER SET LATIN,
  IN ClassifcationValue TD_ANYTYPE,
  IN ClassifcationOperator VARCHAR(6) CHARACTER SET LATIN,
  IN ReplaceOption CHAR(1) CHARACTER SET LATIN
)
  ...
;

Syntax Elements

RulesetName
Name of the ruleset that contains the RuleName. Use ‘ALLRULESETS’ to indicate all existing rulesets. Cannot be null and its length must be between 1 and 30.
RuleName
Name of the rule to add the criterion. The specified name must be unique among all existing TASM rules: throttles, arrival rate meters, workloads, filters. A combination of RulesetName and RuleName uniquely identifies a specific rule. If RulesetName is ‘ALLRULESETS’, the criterion is added to the same rule name in all existing rulesets. It cannot be null. The length must be between 1 and 30.
Description
Description of the criterion.
ClassificationType
This table describes possible values for ClassificationType:
Value Description Group
USER User name Request Source
ACCT Account name Request Source
ACCTSTR Account string Request Source
PROFILE Profile Request Source
APPL Application name Request Source
CLIENTADDR Client IP address Request Source
CLIENTID Client logon ID Request Source
DB Database Target
TABLE Table Target
VIEW View Target
MACRO Macro Target
SPROC Stored procedure Target
FUNCTION User-defined function Target
METHOD User-defined method Target
SERVER QueryGrid server Target
STMT Statement type Query Characteristics
ALLAMP All AMP request Query Characteristics
MSR Multi statement request Query Characteristics
MINSTEPROWS Minimum estimated step row count Query Characteristics
MAXSTEPROWS Maximum estimated step row count Query Characteristics
MINFINALROWS Minimum estimated final row count Query Characteristics
MAXFINALROWS Maximum estimated final row count Query Characteristics
MINSTEPTIME Minimum estimated step processing time Query Characteristics
MAXSTEPTIME Maximum estimated step processing time Query Characteristics
MINTOTALTIME Minimum estimated total processing time Query Characteristics
MAXTOTALTIME Maximum estimated total processing time Query Characteristics
JOIN Join type Query Characteristics
FTSCAN Full table scan Query Characteristics
MEMORY Memory usage Query Characteristics
IPE Incremental Planning and Execution Query Characteristics
QUERYBAND Query Band Query Band
ClassificationValue
Value of the classification type to match for this criterion. A name can include the following wildcard characters:
  • '*' = Matches zero or more characters
  • '?' = Matches one character
Classification Type Classification Value
USER User name
ACCT Account name
ACCTSTR Account string
PROFILE Profile name
APPL Application name
CLIENTADDR Client IP address
CLIENTID Client logon ID
DB Database name
TABLE Table name
VIEW View name
MACRO Macro name
SPROC Stored procedure name
FUNCTION User-defined function name
METHOD User-defined method name
SERVER QueryGrid server name
STMT
  • D = DDL
  • M = DML
  • S = SELECT
  • C = COLLECT STATISTICS

Or a combination of the values above.

This ClassificationType can only be included, so ClassificationOperator must be set to 'I'.

ALLAMP Not applicable
MSR Integer (>= 2) specifying minimum statement count
MINSTEPROWS Integer (>= 1) specifying minimum estimated step row count
MAXSTEPROWS Integer (>= 1) specifying maximum estimated step row count
MINFINALROWS Integer (>= 1) specifying minimum estimated final row count
MAXFINALROWS Integer (>= 1) specifying maximum estimated final row count
MINSTEPTIME Decimal (>= 0) specifying minimum estimated step processing time
MAXSTEPTIME Decimal (>= 1) specifying maximum estimated step processing time
MINTOTALTIME Decimal (>= 1) specifying minimum estimated total processing time
MAXTOTALTIME Decimal (>= 1) specifying maximum estimated total processing time
JOIN Only one of these values is allowed.
  • N = no join
  • A = any join type
  • P = product join
  • Q = no product join
  • U = unconstrained product join
  • V = no unconstrained product join
FTSCAN Not applicable
MEMORY Only one of these values is allowed.
  • I = increased
  • L = large
  • V = very large
IPE Not applicable
QUERYBAND Query Band name-value pair
ClassificationOperator
This parameter specifies whether:
  • The classification criterion is for inclusion or exclusion.
  • For Profile and User inclusion criteria, the implicit AND operation can be overridden with an OR operation (see below).

A request is qualified for a rule (throttle, AMR, workload, and so on) if:

  1. It satisfies all inclusion criteria of the rule, and
  2. It satisfies no exclusion criteria of the rule

When multiple criteria are attached to a rule name, TASM performs implicit OR/AND operations among the criteria.

  • For multiple criteria from the Request Source group:
    • Criteria of the same ClassificationType are joined together by the OR operator. For example, if the criteria of a throttle include USER=user1, USER=user2, then the throttle is applied to requests from user1 OR user2.
    • Criteria of the different ClassificationType are joined together by the AND operator. For example, if the criteria of a throttle include USER=user1, ACCT=finance, then the throttle is only applied to requests from user1 AND finance account. This operation can be overridden so that USER and PROFILE criteria are joined together by the OR operator.
  • For multiple criteria from the Target group (except SERVER), they are joined together by the OR operator. For example, if the criteria of a throttle include DB=db1, TABLE=db3.tableX, then the throttle is applied to requests that reference db1 OR db3.tableX.
  • For multiple criteria from different groups, they are joined together by the AND operator. For example, if criteria include:
    • USER: user1, user2, and user3
    • DB: db1, db2
    • TABLE: db3.tableX, db4.tableY
    Then the criteria are interpreted as: (user1 OR user2 OR user3) AND (db1 OR db2 OR db3.tableX OR db4.tableY)
    Values Descriptions
    I Inclusion criterion (mutually exclusive with E)
    E Inclusion criterion (mutually exclusive with I)
    O Join Profile and User criteria with an OR operator
    • Only valid with PROFILE or USER
    • Can be combined with either I or E
ReplaceOption
  • 'Y' = Delete the existing criteria of the specified rule and add the criterion. If the specified rule does not exist or does not have any criteria, an error is returned.
  • 'N' = Add a criterion. If the specified criterion exists, an error is returned.