Generating and acting on recommendations for collecting statistics for database objects that are in the access path for an SQL statement

The Statistics Advisor recommends RUNSTATS commands that you can use to collect or repair statistics. The collected statistics might help the optimizer to choose a more efficient access path. However, it is possible for the optimizer to choose the same access path even after you collect the recommended statistics.

Before you begin

About this task

In many situations, statistics can be stale or conflict with each other. There are also situations where statistics might not have been collected for a table or index. Collecting column-group statistics can be expensive. The 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.

Procedure

To get and act on recommendations for collecting statistics:

  1. Capture the SQL statement that you want to tune. See Locations from which you can capture an SQL statement for single-query tuning.
  2. Select one of the captured statements and click the Invoke Advisors and Tools button. The Run Single-Query Advisors and Analysis Tools page in the Invoke section opens.
  3. If you need to set the schema to use for unqualified objects that the SQL statement references, or (if the statement runs on DB2 for z/OS) change the SQL ID, specify these values in the Schema and SQLID fields above the SQL statement on this page. For information about setting global defaults for schemas and SQL IDs, see Setting the schema to use for unqualified objects, the SQL ID, or both.
  4. Optional: Modify values for the options for the Statistics Advisor. Click Set Advisor Options on the left side of the Invoke section. Then, click the Statistics Advisor tab to display the options that you can modify. After you are finished, in the left side of the Invoke section click Run Advisors and Analysis Tools to return to the Run Single-Query Advisors and Analysis Tools page.
  5. Click the Select What to Run button.
  6. In the Select Activities window, select the Statistics check box. Then, click OK. After the Statistics Advisor runs, the Review Advisor Recommendations page of the Review section opens. The Summary section shows whether there are any recommendations for fixing statistics.
    High
    Recommendations of this type indicate that important statistics are missing or obsolete, or that conflicts exist among the statistics.
    Maintenance
    Recommendations of this type have a low priority, and indicate that the catalog statistics are complete and accurate. The recommendation provides a RUNSTATS job that you might want to include periodically in your maintenance cycle to maintain the health of the relevant statistics.
  7. In the Review Single-Query Advisor Recommendations page, double-click the Statistics row in the Summary table.
  8. For DB2 for z/OS® only: Choose whether to run the Repair version or the Complete version of the recommended RUNSTATS commands.
    Repair version
    This version of the RUNSTATS commands repairs the problems that the Statistics Advisor found. Run this version to conserve time and CPU resources.
    Complete version
    This version of the RUNSTATS commands collects a full set of statistics for the objects that are related to the SQL statement. In the process of collecting the full set, these commands repair any problems that the Statistics Advisor found.
  9. In the Statistics Advisor Report section, review the findings about the statistics for the referenced tables. There are two different versions of the report, which you can select by clicking the left-most icons in the toolbar.
    Summary report
    Click the Summary report icon (the first icon in the toolbar above the RUNSTATS commands) to display only the problems that the Statistics Advisor found and that the RUNSTATS commands fix.
    Detailed report
    Click the Detailed report icon (the second icon in the toolbar above the RUNSTATS commands) to display a report of all of the statistics for the objects that the SQL statement references. The report shows the problematic statistics together with the non-problematic statistics.
  10. Look in the Conflicts Detail section to learn of any conflicts that the advisor found in the statistics.
  11. Select any of these options:
    Note: Before you select any of the options to run RUNSTATS commands, ensure that you have the appropriate authorities and privileges on the data server.
    Run
    Runs all of the listed RUNSTATS commands.
    Run and Save to History of Recommended RUNSTATS Commands
    Runs all of the listed RUNSTATS commands and saves them for future reference.
    Run and Save in Statistics Profiles
    Runs all of the listed RUNSTATS commands. Each command generates and stores a specific statistics profile in the system catalog tables.
    For DB2 for z/OS: Retrieve RUNSTATS Commands from the Database Server
    Displays the RUNSTATS commands that the Statistics Advisor recommended previously for the objects that the SQL statement references, if you chose to save those previous recommendations to the history of recommended RUNSTATS commands.
    For DB2 for Linux, UNIX, and Windows: Retrieve Statistics Profiles
    Retrieves the RUNSTATS commands that are stored in the statistics profiles for the current tables. These commands are displayed to the right of the current recommended commands.
    For DB2 for z/OS: Save in History of Recommended RUNSTATS Commands
    Saves the listed RUNSTATS commands on the database. If you retune the same SQL statement, or tune another statement that references the same objects, you can display the RUNSTATS commands that the Statistics Advisor recommended previously. You can then compare the current recommendations with the previous recommendations.
    For DB2 for Linux, UNIX, and Windows: Save in Statistics Profiles
    Saves the settings for all listed RUNSTATS commands in statistics profiles for the tables that the commands are for.
    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.

What to do next

Before moving on to other advisors, run the Statistics Advisor again, and collect the recommended statistics until you have resolved all high recommendations. The other single-query advisors rely on accurate statistics to make recommendations. You might also want to generate a new access plan graph and compare it to the original access plan graph to learn whether the access path has changed.

To run the Statistics Advisor again, follow these steps:

  1. Ensure that the local cache of the system catalog for the connected database or subsystem is refreshed.
  2. Click the Invoke tab on the left side of the workflow assistant.
  3. On the Run Single-Query Advisors and Tools page, select the Re-explain the query option.
  4. Click the Select What to Run button.
  5. In the Select Activities window, select the Statistics option and click OK.

Feedback