Generating revision recommendations for single queries

You can choose to generate revision recommendations for selected SQL statements that reside on a connected Db2® for z/OS® subsystem, using options found in the IBM® Db2 Query Workload Tuner for z/OS tuning wizard.

About this task

The query revision tuning option looks for opportunities in a query to make the following improvements:
  • Minimize the number of index pages and data rows that have to be read. For example, you can minimize the number of rows read by having predicates in the query that can determine the needed rows from just the index alone.
  • Minimize sort operations. For example, it determines whether ORDER BY or GROUP BY clauses are needed in the query, or if sort operations can be resolved through index access.
After the QWTz Tuning Wizard generates recommendations for revising your SQL statement for better performance, you can review the recommendations and read explanations for the recommendations.

Procedure

  1. From the QWTz database selector, choose the connection where your SQL statement resides.
  2. Go to Optimize > Start Tuning.
  3. Load or enter your SQL Statement in the data source pages, select a schema to use, and then navigate to the Options page.
  4. Expand the Tuning options panel and ensure that the Generate query revision recommendationsoption is selected.
  5. Click Launch Tuning Job.
    A message box opens, explaining that the tuning job is in progress.
  6. Close the message box to go to the Tuning jobs page.
  7. When your job is complete, select the link from the JOB NAME column, to open the Tuning results page for your tuning job.
    From here you can perform any of the following tasks:
    • From the Recommendations page, view a summary of the access path recommendations.
    • Click the link of a listed recommendation to view the complete query, with potential problem lines highlighted.