Generating reports of recommendations for query workloads that run on DB2 for z/OS

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 Statement Cache under DB2 for z/OS Sources, the Capture SQL from Statement 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. Collect EXPLAIN information about 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.
    1. On the Manage and Tune Workloads page, click on the query workload.
    2. Click the Tasks icon in the toolbar above the table. The Tasks page opens. This page displays the tasks that are scheduled to run on the query workload.
    3. Click the Explain icon. The Schedule Collection of EXPLAIN Information window opens.
    4. Schedule a task for explaining the query workload. For help, click the Help icon in the lower-left corner of the window. If you want the workflow assistant to alert you when the task is done, select the Receive notification when the scheduled task is finished check box.
    The task starts at the scheduled time. After it is finished, proceed to step 3.
  3. 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.
  4. 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.
  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