Generating table reports for query workloads

You can generate a report to see information about the tables that are referenced in the SQL statements of an entire workload.

Before you begin

About this task

You can create a detailed report about the tables that are referenced in the SQL statements of a workload and statistics and cost information associated with those tables.

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. Click the Select What to Run button.
  5. In the Select Activities window, select Report statistics from access plans and click OK.
  6. When the Generate Workload Table Report window appears, choose the format in which to generate the report. Then, click OK.

Results

The report is displayed in the Open Workload Table Report page of the Review section.

Feedback