Creating or modifying statistical views

The Workload Statistics Advisor can recommend new statistical views and changes to existing ones. Statistical views help the DB2® optimizer to estimate cardinality better when SQL statements have complex predicates, relationships among complex predicates, and relationships across tables.

Before you begin

About this task

After the Workload Statistics Advisor generates recommendations for statistical views, you can review the recommendations and details that can help you decide which recommendations you want to follow.

Procedure

  1. In the Manage section, open the Manage and Tune Workloads page.
  2. Select the query workload and 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.
  3. Optional: Modify values in the Statistical View Constraints section of 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 and then the Statistical View Constraints tab to display the options that you can modify. For help with the options, click the ? icon. After you are finished modifying the values for options, click Run Workload Advisors on the left side of the Review section.
  4. Ensure that the Re-collect EXPLAIN information before running workload advisors option is selected.
  5. Click the Select What to Run button.
  6. In the Select Activities window, select the Statistical views check box and click OK.
  7. In the Collect EXPLAIN Information window, specify values for the runtime environment of 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. For more information, click the Help icon in the lower-left corner of the window.
  8. In the Review Workload Advisor Recommendations page, click the Statistical views tab if there are new recommendations for statistical views.
  9. Review the list of recommended statistical views. These columns appear in the table that lists the recommendations.
    State
    Shows whether the view is a new recommendation or an existing view
    Tables in Definition
    The number of tables that the view is defined on.
    Affected Statements
    The number of statements for which the view improves performance.
    Estimated Execution Time
    The estimated sum in timerons of the execution times for all of the affected statements, if the recommendation is followed.
    Estimated RUNSTATS Time
    The estimated time in minutes that is required to run the RUNSTATS command that gathers statistics for the view.
    RI Recommended
    For DB2 for Linux, UNIX, and Windows, Version 10 and later: Indicates whether NOT TRUSTED informational relational integrity constraints are recommended for one or more of the tables that the statistical view is defined on. Such constraints can help the DB2 optimizer infer statistics from the statistical view in cases where DB2 does not maintain the referential integrity of your data and there might be a very small percentage of non-NULL values in foreign keys that do not match values in corresponding primary keys.
    When you click a recommendation, you can view these details in the Statements Assisted by the Statistical View section below the list.
    Statement Text
    The text of the SQL statement
    Number of Executions
    The number of times that the SQL statement runs as part of the query workload that it is in.
    Time to Execute Once
    The length of time in timerons that the statement takes to run.
    Current Execution Time
    The length of time in timerons that the statement takes to run multiplied by the number of times that the statement runs.
    The Tables Defining the Statistical View section shows these details of the highlighted recommendation.
    Owner
    The qualifier of the table.
    Table Name
    The name of the table.
    Cardinality
    The number of rows that are in the table. A value of -1 means that the cardinality is unknown.
    Last RUNSTATS Execution
    The last time that statistics were collected for the table.
    Times Used by Existing and Recommended Statistical Views
    The number of existing and recommended statistical views that include this table in their definitions.
  10. Select the check box next to each statistical view that you want to create or modify.
  11. Right-click anywhere in the table of statistical views and select View Scripts for Creating or Modifying the Selected Statistical Views.
  12. In the Review Recommended Scripts for Statistical Views wizard, run the scripts to follow the recommendations that you selected.

What to do next

Run the Workload Index Advisor.

Feedback