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. 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. If you want to be sure to generate the report with the latest EXPLAIN information for the SQL statements that are in the query workload, select the option Re-collect EXPLAIN information before running advisors.
  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