Generating reports of recommendations for query workloads that run on DB2 for Linux, UNIX, and Windows

You can generate, save, and print reports that list recommendations from the workload advisors.

Before you begin

Procedure

  1. Create a workload of the SQL statements that you want to tune.
    1. In the Data Source Explorer, right-click the database where the SQL statements are located and select Analyze and Tune > Start Tuning. The workflow assistant opens to the Capture section.
    2. On the left side of the Capture section, select the location of SQL statements that you want to tune together as a workload. A page opens from which you can capture SQL statements from that location.
      For example, if you selected Package Cache under DB2 for Linux, UNIX, and Windows Sources, the Capture SQL from Package Cache page opens.
    3. Follow the instructions in the page that opens. You can click the Help icon to the right of the title for information about the page.
    4. After capturing SQL statements, click the Save All to Workload button.
    5. 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. 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. Modify values for the options for the Workload Statistics Advisor and Workload Index Advisor. Click Set Advisor Options under Workload on the left side of the Invoke section. Then, click the Statistics and Indexes tabs to display the options that you can modify. For help with the options, click the Help icons in the Statistics and Indexes sections. After you are finished modifying the values for options, click Run Workload Advisors on the left side of the Review section.
  4. If you want to be sure that the advisors have access to the latest EXPLAIN information for the SQL statements that are in the query workload, select the option Re-collect EXPLAIN information before running advisors.
  5. Click the Select What to Run button.
  6. In the Select Activities window, select the types of recommendations that you want. Also, select Summarize and report recommendations. Then, click OK.

Results

The report is generated in HTML and displayed in the Open Workload Summary Report page of the Review section. To open this page, click Open Workload Summary Report on the left side of the workflow assistant. If you want to save the report to your filesystem, click the Save Report button at the top of the report. You can also right-click the report and select the option for printing it.

Feedback