Tuning query workloads that run on DB2 for Linux, UNIX, and Windows

You can get recommendations for improving the performance of groups of SQL statements that access the same DB2 for Linux, UNIX, and Windows database.

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 a query workload, 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 Linux, UNIX, and Windows.
    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.
    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. 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 Linux, UNIX, and Windows.
  3. Run the Workload Statistics Advisor again to generate recommendations for creating statistical views and modifying existing statistical views. See Generating and acting on recommendations for creating and modifying statistical views for query workloads that run on DB2 for Linux, UNIX, and Windows.
  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 Linux, UNIX, and Windows.

Feedback