17.00 - 17.05 - Defining a Ruleset - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Administration

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
17.05
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
rgu1556127906220.ditamap
dita:ditavalPath
lze1555437562152.ditaval

You must create a TASM ruleset to contain any workload management rules that you create. You can create multiple rule sets, but only one ruleset can active at a time. For instructions on using the Workload Designer portlet, see Teradata® Viewpoint User Guide, B035-2206.

In the Ruleset General View:

  1. Name the ruleset.
  2. Use the default Interval settings.
  3. Use the default Blocker settings.
  4. Defining Bypass rules is optional, but not recommended for initial implementation.
    Database user DBC bypasses all rules by default.
  5. Create the needed classification session, filter, and throttle rules within the ruleset to manage your database workload, as shown in the topics that follow.

Classification Settings

You can set the following classification parameters for the session, filter or throttle rules you create, to narrow the effects of the rules, unless you want a rule to apply globally.

  • Source: account name, account string, username, or client IP address
  • Target: The name of a database object that the query accesses, for example, a database, table, macro, view, function name, or method name.
  • Query characteristics, for example, statement type, join type, or full table scan
  • Query band
  • Utility (source of query)

Session Rules

You can use the sessions tab in a ruleset to set limits on the:
  • Number of concurrent query sessions.
  • Number of concurrent utility sessions
  • Number of concurrent sessions for a specific utility
  • The order of precedence of utility session rules

Filter Rules

A filter rejects a query before the query starts running. You can apply filters globally or according to classification rules.

For example, you can create a filter that prohibits a specific user (source classification) from running a query with an estimated processing time of longer than 15 minutes (query characteristic classification).

For information on Ruleset Filters, see Teradata® Viewpoint User Guide, B035-2206.

Throttle Rules

A throttle limits the number of concurrent queries in a filter classification. When the throttle limit is reached, additional affected queries go into the delay queue.

You can create throttles to limit concurrency according to throttle classification, for example:
  • Limit a specific user (source classification) to running no more than 10 queries at a time (query session limit)
  • Limit the number of utilities (utility classification) simultaneously executing against a table (target classification)
  • Limit the number of memory-intensive functions, such as XMLQUERY, and memory-intensive SQL queries executing concurrently

Suggested Custom Throttles

The following example throttles can be useful for most implementations.

Throttle all at 52:
  • Limits the database to execution of 52 concurrent queries
  • Places additional queries into the delay queue
  • Prevents unusually large query demand from putting the system into flow control
Throttle High, Medium, and Low PG queries with estimated processing time > 1 second:
  • Sets a limit of 30 concurrently executing H, M, or L queries that have an estimated processing time > 1 second. There is no built-in limit on shorter queries.
  • Places additional long queries into the delay queue.
  • This throttle does not apply to short queries (<1 sec), or to an R query of any size.