Generating and acting on recommendations for collecting statistics for query workloads that run on DB2 for z/OS

The Workload Statistics Advisor enables database administrators to collect statistics that are relevant to the SQL statements in an entire query workload.

Before you begin

About this task

The Workload Statistics Advisor generates a number of RUNSTATS commands to collect necessary statistics for the tables that SQL statements in a query workload reference.

There are many situations where statistics are stale or conflict with each other. There are also situations where statistics might not have been collected. Collecting column-group statistics can be expensive. The Workload Statistics Advisor can decide which statistics are needed, so that you do not waste time and resources collecting statistics that you do not need to collect.

After the Workload Statistics Advisor generates RUNSTATS commands for collecting statistics, you can review the current state of the statistics and the problems that the advisor found in them. If you have the appropriate authorities on the database, you can run the RUNSTATS statements that you select.

Procedure

  1. Create a workload of the SQL statements that you want to tune.
    1. Capture the SQL statements that you want to tune. See Locations from which you can capture SQL statements to create query workloads that run on DB2 for z/OS®.
    2. After capturing SQL statements, click the Save All to Workload button.
    3. In the Input Workload Name and Description window, specify information for identifying the new query workload.
    The Manage and Tune Workloads page of the Manage section opens. The new query workload appears in the table on this page. The status of the query workload is CAPTURED.
  2. Collect EXPLAIN information about the SQL statements that are in the query workload. The workflow assistant must run the EXPLAIN statement for each of the SQL statements that are in the query workload. The Workload Statistics Advisor requires up-to-date information about the SQL statements.
    1. On the Manage and Tune Workloads page, click on the query workload.
    2. Click the Tasks icon in the toolbar above the table. The Tasks page opens. This page displays the tasks that are scheduled to run on the query workload.
    3. Click the Explain icon. The Schedule Collection of EXPLAIN Information window opens.
    4. Schedule a task for explaining the query workload. For help, click the Help icon in the lower-left corner of the window. If you want the workflow assistant to alert you when the task is done, select the Receive notification when the scheduled task is finished check box.
    The task starts at the scheduled time. After it is finished, proceed to step 3.
  3. Run the Workload Statistics Advisor.
    1. Click the Invoke Advisors and Tools icon, which is on the left end of the toolbar above the list of query workloads. The Run Workload Advisors page of the Invoke section opens.
    2. Optional: Modify values for the options for the Workload Statistics Advisor.
      Click Set Advisor Options under Workload on the left side of the Invoke section. Then, click the Statistics tab to display the options that you can modify. For help with the options, click the Help icon in the Statistics section. After you are finished modifying the values for options, click Run Workload Advisors on the left side of the Review section.
    3. Click the Select What to Run button.
    4. In the Select Activities window, select the Statistics check box and click OK.
    The Review Workload Advisor Recommendations page of the Review section opens. The Summary section on this page shows whether the Workload Statistics Advisor has new recommendations for the query workload.
  4. In the Review Workload Advisor Recommendations page, click the Statistics tab if there are new recommendations for RUNSTATS commands.
  5. Use these columns to judge which objects have statistics that are the most important to correct for improving the performance of the query workload.
    Cardinality
    The number of rows that are in the table. If the cardinality is unknown, the value is -1.
    Reference Count
    The number of SQL statements in the query workload that reference the table.
    Weighted Reference Count
    The query weight multiplied by the reference count. Query weights are applied only to SQL statements that are captured from a statement cache or from the monitor by using the Monitor List view.
    You can set the query weight in the global preferences. From the main menu bar, select Window > Preferences. In the Preferences window, expand Data Management > Query Tuner > Tune Workload and select the Workload General Options page. If you change the query weight, you must close and then reopen the workflow assistant for the change to take effect.
  6. For the most important tables, use the two sections below the RUNSTATS commands to look up the problems that the Workload Statistics Advisor identified.
    Statistics Advisor report
    Displays all of the statistics for the objects that the SQL statements in the query workload reference. The report shows and annotates the problematic statistics together with the non-problematic statistics.
    Conflicts
    Displays any conflicts in the statistics for the objects that the SQL statements in the query workload reference.
  7. Select the check box for each of the tables that you want to run one of the following options for. Then, click the corresponding icon.
    Run
    Lets you run all of the listed RUNSTATS commands.
    Copy
    Copies all of the listed RUNSTATS commands to the system clipboard.
    Save to File
    Saves all of the listed RUNSTATS commands to a file.
    Save in History of Recommended RUNSTATS Commands
    Saves all of the listed RUNSTATS commands to a table that contains a history of all of the RUNSTATS commands that have been recommended for this query workload.
    DB2 10 for z/OS only: Create a RUNSTATS Profile For Referenced Tables
    Saves the options in the selected RUNSTATS command as a RUNSTATS profile for the corresponding tables. A RUNSTATS profile is a saved set of options for the RUNSTATS utility that apply for a particular table. DB2 uses RUNSTATS profiles for autonomic statistics maintenance. You can also use RUNSTATS profiles to quickly invoke the RUNSTATS utility with a predefined set of options.
    DB2 10 for z/OS only: Update the RUNSTATS Profile for Referenced Tables
    Update the current RUNSTATS profile for the referenced tables with the options in the selected RUNSTATS commands. A RUNSTATS profile is a saved set of options for the RUNSTATS utility that apply for a particular table. DB2 uses RUNSTATS profiles for autonomic statistics maintenance. You can also use RUNSTATS profiles to quickly invoke the RUNSTATS utility with a predefined set of options.
    Retrieve RUNSTATS Commands from the Database Server
    Displays the RUNSTATS commands that the Workload Statistics Advisor recommended previously for the query workload, if you chose to save those previous recommendations to the history of recommended RUNSTATS commands.

What to do next

Generate and act on recommendations from the Workload Index Advisor.

Feedback