15.10 - Creating Workload Management Rules - Teradata Workload Analyzer

Teradata Workload Analyzer User Guide

prodname
Teradata Workload Analyzer
vrm_release
15.10
category
User Guide
featnum
B035-2514-035K

Teradata WA provides the ability to analyze current Teradata Database usage and recommend workload management rules appropriate for the system.

Create these rules using one of the following methods:

  • Saving the PDSet to the database for migration
  • To enable detail or summary logging for a specific user or account
  • Saving the PDSet to the database for migration

    Use this method if the optimal priority scheduler settings have already been created for the workload.

    Note: Skip this procedure if Priority Scheduler Administrator (PSA) is already used to manage your priority scheduler settings.

    1 Create a PDSet that contains your Priority Scheduler settings.

    2 If more than one set of priority scheduler settings is used (for example, one configuration during the day and another at night), then create PDSets for the time that each priority scheduler configuration is active.

    TWA migrates PDsets currently available in the DBCMngr database.

    Capturing the DBQL Query Log

    Use this second method if your site uses the default priority scheduler settings. This method requires logging query activity to DBQL for the period of time that represents a typical workload (for example, one week).

    Note: Skip this procedure if DBQL data is already collected for your workloads.

    Teradata WA analyzes both the DBQL detail and summary tables. The detail table contains one row for each query submitted. The summary table is more efficient: it is updated at periodic intervals with a count of the number of queries submitted by each user account.

  • To enable detail logging, enter one of these commands at a command console:
  • BEGIN QUERY LOGGING ON USER1, USER2, USER3;
    BEGIN QUERY LOGGING ON ACCOUNT = (‘ACCT1’, ‘ACCT2’, ‘ACCT3’);
  • To enable summary logging, enter one of these commands:
  • BEGIN QUERY LOGGING LIMIT THRESHOLD = 3600 ON USER1, USER2;
    BEGIN QUERY LOGGING LIMIT THRESHOLD = 3600 ON ACCOUNT = (‘ACCT1’);
  • To disable detail or summary logging, use one of these commands:
  • END QUERY LOGGING ON USER1, USER2, USER3;
    END QUERY LOGGING ON ACCOUNT = (‘ACCT1’, ‘ACCT2’, ‘ACCT3’);
  • TWA also uses data from the DBQL Step Table, if available, for analysis on estimated processing time. The syntax for step data looks something like this:
  • BEGIN QUERY LOGGING WITH STEPINFO ON USER1;

    Note: This turns on both detail and step logging for “user1” — there is no syntax for turning on only stepinfo.

    For assistance with DBQL syntax and usage, see SQL Data Definition Language (B035‑1144), and Database Administration (B035‑1093).