Tuning query workloads that run on DB2 for z/OS

You can get recommendations for improving the performance of groups of SQL statements that access the same DB2® for z/OS® subsystem.

Before you begin

About this task

Although there is no predetermined order in which to run the advisors for tuning a query workload, there is a general order on which you can base your own tuning scenarios.

Procedure

In most cases, to tune an SQL statement, you can follow these steps.

  1. Create a workload of the SQL statements that you want to tune.
    1. Capture the SQL statements that you want to tune. See Locations from which you can capture SQL statements to create query workloads that run on DB2 for z/OS.
    2. After capturing SQL statements, click the Save All to Workload button.
    3. In the Input Workload Name and Description window, specify information for identifying the new query workload.
  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. Run the Workload Statistics Advisor to generate RUNSTATS commands for ensuring that the DB2 optimizer has the most current and the most needed statistics. See Generating and acting on recommendations for collecting statistics for query workloads that run on DB2 for z/OS.
  4. Run the Workload Index Advisor and follow its recommendations to ensure that the right indexes exist to help avoid unnecessary table scans. See Generating and acting on recommendations for indexes for improving the performance of query workloads that run on DB2 for z/OS.
  5. Run the Workload Query Advisor and follow its recommendations to find and revise structures in SQL statements that are likely to cause the DB2 optimizer to choose a suboptimal access path. See Generating and acting on recommendations for revising SQL statements in query workloads for better performance.
  6. Run the Workload Access Path Advisor to discover whether the DB2 optimizer is using a suboptimal access path to process the statement. Then, follow the advisor's recommendations. See Generating and acting on recommendations for changing the access paths for SQL statements in query workloads that run on DB2 for z/OS.

Feedback