Virtually testing recommended indexes and indexes that you propose for query workloads that run on DB2 for Linux, UNIX, and Windows

You can test recommended indexes and indexes that you propose without first creating them.

Before you begin

About this task

The results of each test show you whether the indexes improve the performance of the current query workload, the estimated disk space that would be required for the indexes, and whether the DB2® optimizer would use the indexes in the access plans for the current query workload.

Procedure

To test virtually recommended indexes and indexes that you propose:

  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 Linux, UNIX, and Windows.
  2. 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 Linux, UNIX, and Windows
  3. Run the Workload Statistics Advisor again to find out whether the advisor has additional recommendations.
  4. Return to the Run Workload Advisors page of the Invoke section.
  5. If you want to test only indexes that you propose and (optionally) disable existing indexes for the test: On the left side of the workflow assistant, select Run Workload Test Candidate Indexes. The Workload Test Candidate Indexes page opens.
  6. If you want to test both indexes that the Workload Index Advisor recommends and indexes that you propose, and (optionally) disable existing indexes for the test, follow these steps.
    1. Run the Workload Index Advisor to generate recommendations for new indexes.
    2. If the Workload Index Advisor generates recommendations, double-click the Indexes row in the Summary table in the Review Single-Query Advisor Recommendations page of the Review section. The Indexes section opens.
    3. Click the Test Candidate Indexes button.
  7. On the Test Candidate Indexes page, determine the selection of indexes that you want to test virtually.
    Candidate indexes table
    If you ran the Workload Index Advisor, recommended new indexes are listed in this table.

    You can edit these index definitions or leave them as is. You can add and edit your own indexes to the table by clicking the Add Index icon.

    Indexes with selected check boxes are included in the test.

    Existing indexes table
    This table lists all of the existing indexes on the tables that the current SQL statement references.

    In this table is a column with the heading Virtually Drop. Select the check box of each existing index that you do not want to include in the virtual test. This check box is selected by default in these cases:

    • The index is recommended to be altered, and the replacement index is in the Candidate indexes table. You cannot include in the test any indexes that the Workload Index Advisor recommends to be altered.
    • The index is recommended to be dropped. You can deselect the check box to include the index in the test.
  8. After adding and editing the indexes that you want to test virtually, click the Test Candidate Indexes button.
  9. In the Test Candidate Indexes window, customize the statistics for the indexes, or leave the default statistics. You can also choose to collect EXPLAIN information that makes use of the set of indexes that you are testing. You can compare this snapshot of the EXPLAIN data with another EXPLAIN snapshot. Then, click OK to run the test.

Results

The Review Workload Advisor Recommendations page opens. Double-click the Test Candidate Indexes entry in the Summary section.

This page lists the indexes that you tested and shows whether the DB2 optimizer uses the indexes in access plans.

What to do next

If you decide that you want to create one or more of the indexes that you tested, select the check box next to each statement and click the Review and Run DDL icon. Copy or save the DDL, so that you can run it outside of the workflow assistant.

If you want to run another test, return to the recommendations from the Workload Index Advisor by selecting the Review tab on the left side of the workflow assistant.


Feedback