Classification Criteria | Teradata Vantage - 17.00 - Classification Criteria - Teradata Workload Management

Teradata Vantage™ - Workload Management User Guide

prodname
Advanced SQL Engine
Teradata Workload Management
vrm_release
17.00
created_date
June 2020
category
Administration
User Guide
featnum
B035-1197-170K
There is a common set of classification criteria used for workload, filter, and throttle rules.
Classification Criteria Description
Request Source Request source comes from one of the following:
  • Username
  • Account name (account string minus the first 4 characters)
  • Account string
  • Profile
  • Application
  • Client IP address
  • Client logon ID
If a TASM rule contains multiple criteria of the same request source type (such as multiple users and multiple accounts), then only one of the types defined in the rule needs to match the attributes of the current request for the request to qualify for the rule. If the rule contains criteria of different request source types, then the attributes of the current request must match one of each of the types defined in the rule to qualify for the rule.
If a user and a profile are defined, you can select whether to AND or OR the user and profile criteria for the rule, since profiles are a collection of users.
Request Target Which database objects the request operates on and which type of step applies to that object:
  • Database
  • Table (excluding volatile tables)
  • View
  • Macro
  • Stored procedure
  • User-defined functions (this selection includes table operators)
  • User-defined methods
  • QueryGrid server
    • The external server name, such as a Hadoop server, which the syntax accesses, for example: SELECT * FROM Table1@Hadoopserver1;
    • The server object is extracted from the foreign table referenced (Table1 in the preceding example).
  • After you select target criteria, you can, optionally, select more specific criteria, such as “unconstrained product join against table XYZ”. See the following section Adding More Specific Criteria for Request Targets.
In order for stored procedure requests to qualify for any rule, the rule must have stored procedure inclusion criteria. If a rule does not have stored procedure inclusion criteria, stored procedures do not qualify for that rule.
For the purpose of classification, all target objects are considered to be of the same type and will be OR'd together (except for a QueryGrid server, which is treated as a unique DBS object type).
Query characteristics Processing details:
  • Statement type (for example, DDL, DML, SELECT, COLLECT STATISTICS)
  • Multistatement requests, see Multistatement Requests
  • AMP limits (include only single or few-AMP requests or all-AMP requests)
  • Step estimated row count
  • Final estimated row count
  • Estimated processing time
  • Estimated step processing time
  • Estimated memory usage, see Estimated Memory Usage
  • Full-table scan (This criterion cannot be used for views)
    It is a best practice to use full-table scan instead as a more specific, secondary criterion of the specified target object. See the following section Adding More Specific Criteria for Request Targets.
  • Join type (all or no joins, all or no product joins, all or no unconstrained product joins)
    It is a best practice to use join type instead as a more specific criteria of the specified target object. See See the following section Adding More Specific Criteria for Request Targets.
  • Incremental planning and execution (IPE) attribute, see Incremental Planning and Execution
Utility criteria Which utility issues the request. This criterion is not available for system throttles.
Query band The originating application may assign query band name/value pairs to a request. Use of the query band enables requests from a common logon to be classified into different workloads.
When you specify multiple qualification criteria, TDWM treats all of the specified criteria as ANDed conditions. The exceptions to this include DBS objects of the same kind and query bands with the same name.

Adding More Specific Criteria for Request Targets

After you create Target classification criteria, you can, optionally, select more specific classification criteria for most database objects by doing the following things in Viewpoint Workload Designer:
  • Selecting or editing the Target criteria. An Edit Target Criteria dialogue box appears.
  • Selecting the pencil icon in the Selected: area. An Edit Criteria dialogue box appears, where you can add more specific classification criteria.
Adding more specific classification criteria lets you pinpoint the exact characteristics of requests that qualify for a rule. For example, you can specify that a request qualifies for a rule if it operates on the specified table AND requires a product join on that table. Adding more specific classification criteria ensures that TDWM applies rules the way you intend. Consider the following request:
  • In one step, a small table will require a join.
  • In another step, there is no join, but a large table will generate many rows.
In this situation, you specify classification criteria for the following items:
  • The large table
  • Any joins
  • Step row count

If you want TASM to detect if the large table returned many step rows AND was joined, the preceding request should not qualify for the rule, yet it may be a false positive. The only way to get the functionality you want is to specify Any Join and Estimated Step Row Count as more specific criteria for the large table. This means that you want both the join and the step row count to occur in any step in a request that operates on the large table.

You can apply the following more specific criteria to most DBS objects that are request targets:
  • Full Table Scan (Include or Exclude)
  • Join Type (all or no joins, all or no product joins, all or no unconstrained product joins)
  • Estimated Step Row Count (above or below a specified number)
  • Estimated Step Processing Time (above or below a specified number)
You cannot create more specific classification criteria for UDFs and UDMs.
You can specify the following more specific classification criteria for tables:
  • Estimated percent of table blocks accessed during the query (greater than or equal to a specified percentage); this is the percentage of the table the request is expected to access
  • Only statements of the following type:(DDL, DML, Select)

The following figure is an example of the Edit Target Criteria screen. To add more specific classification criteria to the inventory table, you would select the pencil icon in the Selected: area next to lumber.inventory.