Teradata Active System Management - Teradata Database

Teradata Active System Management (TASM) is a set of products, including system tables and logs, that interact with each other and a common data source. It facilitates automation in the following four key areas of system management:

Workload management

Performance tuning

Capacity planning

Performance monitoring

With careful planning, TASM can improve and optimize your workload management and performance. It can also improve response times and ensure more consistent response times for critical work. This can reduce the effort required by DBAs.

Some Key TASM Products and Components

The following table describes some of the key products and components of TASM.

Product / Component	Description
Teradata Workload Analyzer (Teradata WA)	A product that analyzes collected DBQL data to help group workloads and define rules to manage system performance. See “Database Query Log” on page 146.
Teradata Viewpoint	A product that enables users to: Create rules for filtering, throttling, and defining classes of queries (workload definitions) Create events to monitor system resources
Open APIs	An interface, invoked from any application, that provides an SQL interface to PMPC through User-Defined Functions (UDFs) and external stored procedures. See “Workload Management Application Programming Interface” on page 103.
Query Bands	A set of name-value pairs that are defined by the user or middle-tier application. Query Bands allow the user to tag session or transactions with an ID through an SQL interface. For specific information on Query Bands, see SQL Data Definition Language and Database Administration.
Resource Usage Monitor	Data collection subsystem that includes TASM-related data collection. See “Resource Usage Monitoring” on page 186.

Teradata Viewpoint

Teradata Viewpoint, including the Workload Designer portlet, supports the creation of the following based on business-driven allocations of operating resources:

Filter rules

Throttle rules

Rules that define classes of queries (Workload Definitions [WDs])

Events to monitor system resources

States to allow changes to rule values

Teradata Viewpoint Workload Designer portlet lets users define rules according to which workload is managed. The following table describes the three categories of Teradata Active System Management (ASM) rules.

Rules	Description
Filter	Reject unwanted logon and query requests before they are executed. Filters restrict access to specific database objects for some or all types of SQL requests. You can prohibit queries that are estimated to access too many rows, take too long, and perform some types of joins.
Throttle (also called concurrency rules)	Enforce session and query concurrency limits on specific objects. When creating throttle rules, you can: Restrict the number of requests simultaneously executed against a database object (such as requests made by a user, or against a table). Reject requests over the concurrency limit on a state-by-state basis (where state is a complete set of working values for a rule set). Enforce concurrency limits on FastLoad, MultiLoad, FastExport, DSA, and ARC utilities. Apply them to Priority Scheduler Performance Groups (PGs).
Workload (also called Workload Definitions [WDs])	Specify how Teradata Database should handle queries while they are executing by specifying parameters for up to 36 separate workload definitions. In each workload definition, you can specify: The Include and Exclude conditions or database objects, or both the workload definition and database objects that determine whether a query is assigned to the class. Note: Wildcard characters (such as “*” and “?”) can be used to include all or a group of database objects, and then exclude specific ones. The execution priority (by more or less transparently creating a Priority Scheduler configuration). The query concurrency limits. Requests over the concurrency limit can be rejected on a state-by-state basis. The set of conditions that invoke an exception once a query has started running.

Teradata Viewpoint Workload Designer portlet allows you to specify filter, throttle, and workload rules (WDs) that dynamically adjust their behavior based on system and user-defined events.

An event is any condition or indication that you think is pertinent to workload management.

Event	Description
Health Condition	Reflects the health of the system, such as a Teradata Database component degrading or failing (a node down, for example), or resources below a threshold for some period of time.
Planned Environment	Includes the kinds of work Teradata Database is expected to perform, such as batch and loads or month-end processing, defined as time periods.

Utility Management

You can manage load utilities (FastLoad, MultiLoad, FastExport) and Teradata Archive/Recovery utility similarly to how you manage SQL requests: by classifying them into workload definitions with throttle limits based on:

Utility name

“Who” criteria (such as user, account, or queryband)

“Where” criteria (the name of the database, table, or view) (not available for Archive/Recovery)

For example, you can specify that:

User A cannot run more than two FastLoad jobs at the same time

Only one MultiLoad job can run at a time against Database XYZ

In addition, using Workload Designer, a portlet of Viewpoint, you can define rules that control the number of sessions that Archive/Recovery or a load utility can use. To create session configuration rules, specify at least one of the following criteria:

Utility name (required)

Data size

“Who” criteria (for example, user, account, client address, or query band)

and then specify the number of sessions to be used when the criteria are met.

For example, you can specify using:

Ten sessions for standalone MultiLoad jobs submitted by Joe

Four sessions for JDBC FastLoad from the application called WebApp

If you do not create a session configuration rule or the utility currently running does not meet the rule criteria, then Workload Designer automatically uses default session rules to select the number of sessions based on these criteria:

Utility name

System configuration (the number of AMPs)

Optional data size

Teradata Workload Analyzer

Teradata Workload Analyzer (WA) is a tool to analyze resource usage patterns of workloads and to correlate these patterns with specific classification and exception criteria. Teradata WA helps:

DBAs identify classes of queries (workloads).

Provides recommendations on workload definitions and operating rules to ensure that database performance meets Service Level Goals (SLGs).

Through graphical displays such as pie charts showing CPU and I/O utilization by accounts, applications, users, profiles, or query bands, through histograms showing actual service levels, as well as recommended settings, Teradata WA makes it easier for DBAs to manage distribution of resources effectively.