15.10 - Workload Definition - Teradata Database

Teradata Database SQL Request and Transaction Processing

Teradata Database
Programming Reference
User Guide

The objective of workload definition is to capture the set of query plans associated with the requests identified in the workload identification phase and define them to the Teradata Index Wizard.

Workloads are defined to the Teradata Index Wizard using the query plans for the requests identified as a workload. These query plans must first be captured in the QCD using the INSERT EXPLAIN WITH STATISTICS statement (see “INSERT EXPLAIN” in SQL Data Manipulation Language) or the BEGIN QUERY CAPTURE statement (see SQL Data Definition Language Syntax and Examples).

You are limited to the following SQL statements when you define a workload using an INSERT EXPLAIN or a BEGIN QUERY CAPTURE request:

  • You cannot include any load operations, such as a MultiLoad job, in your workload specifications.

    See Teradata Index Wizard User Guide for details about this process.

    The workloads defined by the Index Wizard have no functional relationship to the similarly named workloads defined by the Teradata Workload Analyzer and the Teradata Viewpoint Workload Designer portlet.

    Teradata Database supplies a set of macros to help you define workloads, modify those workloads, and delete them when they are no longer useful. These macros are normally invoked using the Teradata Index Wizard utility. See Teradata Index Wizard User Guide for more information.

    The process of defining SQL workloads for subsequent index analysis is indicated by the following graphic:


    Many phases of the Activity Transaction Modeling process (see Database Design) map well to the re‑engineering phases of workload definition. The following table indicates those mappings:


                       ATM Activity

                                                    Workload Definition Activity

    Identify and define attribute domains and constraints for physical columns.

    Define workload on tables and databases whose definitions exist in the data dictionary of a production environment.

    Because they already exist, they need not be identified.

    Identify and model database applications.

    Not applicable.

    Model application processing activities to include their transactions and run frequencies.

    Submit the SQL requests as transactions and run frequencies using the INSERT EXPLAIN statement or the Index Wizard.

    You determine the run frequency, which is a measure of how often the query is executed in the workload. Assume that DBQL queries are used as the workload input. In case a query is executed repeatedly, DBQL logs the query as many times as it is submitted.

    The Index Wizard client interface helps you to derive the run frequency information as the total count of the entries for the same queries logged in DBQL.

    Model transactions to identify the tables used and the columns required for value or join access and estimating cardinalities

    The workload analysis includes this activity.

    The system assimilates the information for the set of SQL requests submitted as a workload, and then stores it in a query capture database.

    Summarize value and join access information across all transactions.

    The data stored in QCD as part of workload analysis is used to summarize the query information.

    Compile a preliminary set of data demographics by estimating table cardinalities and value distributions and assigning change ratings.

    The workload analysis includes this activity.

    For the tables referenced in the SQL requests submitted as workload, the system assimilates the demographics information and stores it in the QCD. The processing includes a COLLECT STATISTICS (QCD Form) statement performed to obtain the information on columns identified as index candidates.