Dropping existing indexes virtually before running the Workload Index Advisor on query workloads that run on DB2 for z/OS

You can get recommendations for indexes after virtually dropping one or more of the indexes that are currently in the access plans for the SQL statements in a query workload.

Before you begin

About this task

In this procedure, you create a query workload of the SQL statements that you want to tune. Then, you run the Workload Statistics Advisor twice and implement its recommendations. Then, you virtually drop existing indexes that are in the access plans for the SQL statements, and run the Workload Index Advisor.

Procedure

To drop existing indexes virtually before running the Workload Index Advisor:

  1. Create the query workload 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. Run the Workload Statistics Advisor to generate RUNSTATS control statements for improving the statistics for objects that are in the access plans of the SQL statements that are in the query workload. Then, run the recommended RUNSTATS control statements. See Generating and acting on recommendations for collecting statistics for query workloads that run on DB2 for z/OS.
  3. Run the Workload Statistics Advisor again to find out whether the advisor has additional recommendations.
    Implement the additional recommendations that the advisor might have.
  4. Re-explain the query workload.
    1. Return to the Manage section of the workflow assistant.
    2. Select Workload List on the left side of the workflow assistant.
    3. Select the query workload. Then, select EXPLAIN Selected Workload in the More actions field.
    4. After the EXPLAIN task is finished, ensure that the local cache of the system catalog is current.
  5. Return to the Run Workload Advisors page of the Invoke section.
  6. Select Run Workload Test Candidate Indexes on the left side of the workflow assistant. The Workload Test Candidate Indexes page opens. The table in the bottom half of the page lists the indexes that are in the access plans for the SQL statements that are in the query workload.
  7. In the Virtually Drop column, select the indexes that you want the Workload Index Advisor not to take into consideration when generating index recommendations for the query workload.
  8. Click Run Workload Index Advisor. The Review Workload Advisor Recommendations page opens. In the Summary section, you can see whether the Workload Index Advisor generated new recommendations. Click the Indexes tab to see the recommendations.
  9. Review the list of tables that the Workload Index Advisor has recommendations for.
    This information appears for each table.
    Cardinality
    The number of rows that are in the table. The value is -1 if the cardinality is unknown.
    References to Table
    The number of SQL statements in the query workload that reference the table.
    Cumulative Total Cost
    The cumulative cost to execute all of the SQL statements that reference the table.
    Recommended Indexes
    The number of indexes that the Workload Index Advisor recommends for the table.
    IUDM Statements
    The number of INSERT, UPDATE, DELETE, and MERGE statements that reference the table.
  10. Select the check box next to each table that you want to review the recommendations for.
  11. In the Recommendations section, review the new and changed indexes that the advisor recommends.
    This information appears in this section.
    Index
    The name of the recommended index. You can change the name.
    Table
    The name of the table that the index is recommended for.
    Action
    The action that is recommended.
    Create
    It is recommended to create the index.
    Drop
    It is recommended to drop the existing index.
    Attention: Recommendations to drop indexes are based on the current query workload. Before implementing such recommendations, ensure that the indexes are not used by any statements that are outside of the current query workload.
    Alter
    It is recommended to alter the existing index.
    Key Columns
    The key columns in the recommended index.
    Include Columns
    The columns that are appended to the key columns and that can allow queries to use index-only access when accessing data. These columns are not used to enforce uniqueness, but they can be appended only to unique indexes. Include columns are distinct from key columns.
    Old Key Columns
    If the recommendation alters the current index: The key columns in the current index.
    Old Include Columns
    If the recommendation alters the current index: The columns that are appended to the key columns and that can allow queries to use index-only access when accessing data. These columns are not used to enforce uniqueness, but they can be appended only to unique indexes. Include columns are distinct from key columns.
    Estimated Performance Gain
    The percentage by which the recommended index can improve the speed of the execution of statements against the table.
    Estimated Disk Space
    The amount of space that is required to create the recommended index.
    Times Used In Workload
    The sum of the execution counts of all SQL statements in the query workload that reference the table.
    Unique
    Indicates whether or not the index is unique.
    Reason Recommended
    The reason that the advisor is making the recommendation.
  12. In the Existing Indexes section, you can find out whether the DB2 optimizer is using existing indexes and whether the optimizer would continue to use existing indexes after you followed the recommendations from the advisor.
    This information appears in this section.
    Index
    The name of the index.
    Table
    The name of the corresponding table.
    Creator
    The qualifier of the index.
    Key Columns
    The key columns in the existing index.
    Include Columns
    The columns that are appended to the key columns and that can allow queries to use index-only access when accessing data. These columns are not used to enforce uniqueness, but they can be appended only to unique indexes. Include columns are distinct from key columns.
    Used Before
    Indicates whether the index is used in the current access plans for the statements that reference the corresponding table.
    Used After
    Indicates whether the index would be used if the recommended indexes were created.
    Foreign Key Index
    Indicates whether or not the index is on a foreign key in the corresponding table.
    Unique
    Indicates whether or not the index is unique.
    Virtually Drop
    Indicates whether the index was virtually dropped before a test was run from the Workload Test Candidate Indexes page.
    Time Last Used
    Shows when the index was last used in an access plan.
  13. In the Index Chosen by Optimizer but Not Recommended section, you can review a list of indexes that the advisor considered recommending and that the DB2 optimizer would have used, but that the advisor did not recommend because they violated constraints that you set. If you want to see the constraints, click the Constraints tab.
    This information appears in this section.
    Index
    The name of the index.
    Table
    The name of the table that the index was evaluated for.
    Key Columns
    The key columns in the index.
    Estimated Disk Space
    The amount of disk space that the index would require.
    Reason Not Recommended
    The constraint that the index violates.
  14. If you want to see which SQL statements would make use of the recommended indexes, in the Recommendations section select the check box next to each index that you are interested in and click the Show SQL Affected by the Selected Indexes icon.
  15. Optional: Change one or two constraints on the Workload Index Advisor and generate a new set of recommendations. In the Recommendations section, click the Change Constraints icon. You can change the amount of disk space to allocate for new indexes and the number of indexes that are allowed per table.
  16. If you want to run or save the DDL statements for the recommendations, click the Run DDL icon. The Run DDL for Selected Indexes window lets you run the statements or save them to a file. You can also modify the statements before running or saving them.

What to do next

After running the DDL, re-run the Workload Statistics Advisor.

Feedback