15.10 - Working with Workload Classification Criteria - Teradata Workload Analyzer

Teradata Workload Analyzer User Guide

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

Teradata Database detects classification criteria before executing queries. Classification criteria identifies the “who,” “what,” and “where” of requests as a way of grouping them for better control, to ensure resources are allocated as appropriate to your needs.

For example, to allocate more resources to requests originating from a particular department in your company, your classification criteria might specify the application or account that corresponds to those requests. Conversely, using classification criteria, you can isolate workloads that you want to assign lower priorities.

You can choose up to six classification criteria. It is recommended that you select at least one item from the “who” criteria; you can combine additional criteria in accord with the following limitations.

  • You cannot select a classification type more than once.
  • You cannot combine a utility type with anything other than a “who” characteristic, so if you pick a utility as criteria 2, criteria 3 must be a “who” criteria item. If your second criteria item is a “what” or “where,” you cannot follow it with a utility.
  • If you do not specify classification attributes for a WD, Teradata WA will initially not place any queries into that WD. However, you could use that WD as a lower-priority WD, for example, when queries encounter run-time exceptions in their original WDs and are downgraded to a lower priority WD.

    Defining Classification Criteria #1

    The selection shown for Criteria #1 in the Classification tab of the Workload Attributes window automatically appears based on the criteria selected when “Defining Existing Workload DBQL Inputs” on page 126. For example, if you select Users for DBQL input criteria, Username appears in the Criteria #1 field of the Classification tab.

    Depending on the DBQL input selected, the following options populate the Criteria #1 list box, identifying the “who” of the workload:

  • Account Name
  • Note: The Account Name criteria includes the names of accounts. PG information is not included. For example, an Account Name criteria containing DBC‑MANAGER only affects accounts such as $H‑DBC‑MANAGER and $M‑DBC‑MANAGER.

  • Account String
  • Application
  • Client Address
  • Client Address can be specified using:

    IPv4 address format, where XXX is the range 0 to 255 decimal:

    XXX.XXX.XXX.XXX
  • Client ID (for logon)
  • Profile
  • Username
  • Include QueryBand and Exclude QueryBand
  • Defining or Modifying Classification Criteria #2 - 6

    If you look closely at the Classification tab of the Workload Attributes window (see “Defining Classification Criteria #1” on page 146), you’ll find that some of its options are not enabled. In particular, you cannot use the tab to add more classification criteria.

    Note: Workload classification criteria cannot be manually modified from the Classification tab of the Workload Attributes window. You can, however, add more classification criteria using the Split option as described in this topic.

    1 To define or modify classification criteria # 2 to 6 for a workload candidate, right-click the workload in the list on the left side of the Workload Attributes window.

    2 Click Split from the right-click menu.

    The Split Workloads dialog box appears. For details on the fields used for splitting workloads, see “Splitting Workloads” on page 135.

    3 Click Add New Classification. The Classification tab appears similar to the tab of the same name on the Workload Attributes window, however all functionality is enabled.

    4 Select the Choose another selection criteria check box. The --AND-- pane of the tab becomes visible, showing the Criteria #2 drop-down list box.

    The list includes the items shown as check boxes in the Category pane of the Define DBQL Inputs dialog and the items described in Table 17.

     

    Table 17: Classification Options for Criteria #2 

    Selected Item

    Also select...

    Data Objects

    Data objects to include and exclude in the Classify by Data Objects dialog box that appears after selecting Data Objects and clicking the Choose button.

    These data objects can be selected:

  • database
  • table
  • view
  • macro
  • stored procedure
  • function
  • method
  • In the Classify by Data Object dialog box, select objects to include or exclude, and select the type of object to add to the criteria. Load the objects and choose the specific object by selecting it and clicking the Add button. For more information, see “Example: Classification by Data Object” on page 152.

    Statement Type

    The statement types you want by selecting the appropriate check boxes.

    Utility Type

    The name of the utility (FastLoad, FastExport, MultiLoad, all three, or Archive/Restore) that you want to control in the list box.

    Teradata WA utility rules apply to FastLoad, MultiLoad, FastExport, TPT Load/Update/Export operator, JDBC FastLoad, and ARCMAIN. Teradata WA classifies the first utility-specific SQL request from a utility job into a WD. All subsequent requests for the utility job run under the same WD (priority).

    AMP Limits

    The check box for Include single or few AMP queries only if appropriate.

    Note: Selecting this check box causes the workload to accept only queries that are not all-AMP queries. If AMP Limits is selected but this check box is left cleared, no AMP limit is defined and an error message appears because the classification data is missing for this criteria.

    Row Count

    Either the Minimum Rows or the Maximum Rows check box or both and type the corresponding values as appropriate.

    Final Row Count

    Either the Minimum Final Rows or the Maximum Final Rows check box or both, and type the corresponding values, as appropriate.

    Estimated Processing Time

    Either the Minimum Time or Maximum Time by selecting the appropriate check box and specifying the CPU limits for each. You can specify CPU time in hundredths of a second (using the format HHH:MM:SS.dd).

    Join Type

    Types of joins to include, including:

  • All Joins
  • No Joins
  • All Product Joins
  • No Product Joins
  • All Unconstrained Product Joins
  • No Unconstrained Product Joins
  • Datablock Selectivity

    DB selectivity access

    IPE

  • Not an IPE
  • IPE with Dynamic or Static
  • Memory Usage

  • Increased
  • Large
  • Very Large
  • Maximum Step Time

    Maximum Time (maximum time to take for the step)

    5 Select the desired classification criteria for the selected workload from the Criteria #2 drop-down list box.

    For examples of how to proceed, see the following:

  • “Example: Classification by Application” on page 151
  • “Example: Classification by Data Object” on page 152
  • “Example: Classification by Query Band” on page 153
  • 6 To select a additional criteria for the selected workload, repeat step 4.

    You can specify a total of six classification criteria.

    7 When criteria selections are complete, click OK.

    8 Select Show to display of a report of all WD classification settings.

    The Classification Description dialog box appears showing all workload classification criteria.

    9 To close the dialog box, click OK, or, to save or print the information in the dialog box, click Save or Print.

    The typical Windows Save or Print dialog box appears.

    10 To view the information in the Classification Description dialog box in plain text, click Plain Text.

    The plain text version of the Classification Description dialog box appears.

    The text version of the Classification Description dialog describes the classification criteria in plain text.

    11 To save or print the information in the dialog box, click Save or Print.

    The standard Windows Save or Print dialog box appears.

    To exit the dialog box without saving, click OK.

    Example: Classification by Application

    1 Follow the instructions from “Defining or Modifying Classification Criteria #2 - 6” on page 147 and select Application from the Criteria #2 list of the Classification tab.

    The Choose box and button become enabled.

    2 Click Choose.

    The Classify by Application dialog box appears.

    3 Click Load to display all items that can be used as application attributes.

    4 From the Available list, select the items you want to include as attributes and click Include.

    Note: Use the SHIFT and CTRL keys to select multiple consecutive or non-consecutive attributes. To move an attribute back to the Available list, select it and click Remove.

    5 From the Available list, select the attributes to exclude and click Exclude.

    6 When finished, click OK to return to the Classification tab and select additional criteria. (Use Cancel to discard classification selections and start over.)

    Example: Classification by Data Object

    1 Follow the instructions from “Defining or Modifying Classification Criteria #2 - 6” on page 147 and select Data Objects from the Criteria #2 list of the Classification tab.

    The Choose box and button become enabled.

    2 Click Choose.

    The Classify by Data Object dialog box appears.

    3 From the Classify by Data Object dialog box, click Choose from the Object Type drop-down list, and then select the type of data object to be used for classification from one of the following.

  • Database
  • Table
  • View
  • Macro
  • Stored Procedure
  • Function
  • Method
  • The Load button becomes enabled.

    4 Click Load to display all items that can be used as attributes for the selected data object.

    5 From the Available list, select the items you want to include as attributes and click Include.

    Note: Use the SHIFT and CTRL keys to select multiple consecutive or non-consecutive attributes. To move an attribute back to the Available list, select it and click Remove.

    6 From the Available list, select the attributes to exclude and click Exclude.

    7 When finished, click OK to return to the Classification tab and select additional criteria.

    Use Cancel to discard classification selections and start over.

    Example: Classification by Query Band

    The Include QueryBand and Exclude QueryBand classification options enable requests coming from a single or common logon to be classified into different workloads based on the query band set by the originating application. This enables an application to influence different priorities for different requests. For example, the GUI for an application may have dialogs that require quick responses and other dialogs that submit long running reports that run in the background. The application can set a different query band for each type of job, causing the requests to be classified into different workloads and run at different priorities. Instead of running the entire application in a single workload, the application can execute the requests in different workloads, each with a different priority, enabling better use of system resources.

    Query band values with the same name are OR’ed. Query band values with different names are AND’ed.

    (NameA=ValA1 OR … OR NameA=ValAn ) AND

    (NameB=ValB1 OR … OR NameB=ValBm ) AND

    (NameX=ValX1 OR … OR NameX=ValXx )

    For more on query band usage, see Teradata Viewpoint User Guide (B035‑2206).

    For this example, assume that a workload is being created for batch requisition types by north or east regions, but an exception is needed to exclude accounting jobs.

    1 Follow the instructions from “Defining or Modifying Classification Criteria #2 - 6” on page 147 and select Include QueryBand from the Criteria #2 list of the Classification tab.

    2 Click Choose.

    The Include QueryBand dialog box appears.

    3 Select Load Names to display all query band names and for this example, select the Region name.

    Alternatively, type the name Region in the QueryBand Names box.

    4 Select Load Values to display all query band values, and for this example, select the North values.

    Alternatively, type the value North in the QueryBand Values box.

    5 Select Add to add the name and value pair.

    6 Repeat steps 2-4 steps to add a name and value pair for the East region.

    7 When finished, click OK to return to the Classification tab.

    Click Cancel to discard your classification selections and start over.

    8 Click the Choose another selection criteria check box.

    The Criteria drop-down list appears for you to select the next criteria.

    9 Select Exclude QueryBand. Notice that Include QueryBand is no longer an option.

    The Exclude QueryBand dialog box appears.

    10 Repeat the steps used to configure the Include QueryBand dialog box and define the accounting job whose query band you want to exclude.

    11 When finished, select OK to close the dialog box and return to the Classification tab, which appears similar to the following.