Finding and fixing changes to access plans after release migration or applying maintenance fixes

A release migration could be a DB2® version-to-version upgrade (such as migrating from DB2 Version 9 for z/OS® to DB2 10 for z/OS) or a DB2 maintenance level upgrade (such as applying an APAR or a PTF). A release migration might introduce new features and change the behavior of the DB2 SQL optimizer. Rebinding a DB2 package after a release migration might change the access plan for the SQL statements and affect performance.

Before you begin

Ensure that the prerequisites are met.

About this task

In this procedure, before you migrate to another release or apply maintenance fixes, you create and explain a query workload that contains the SQL statements that are in your application. Then, you migrate or apply maintenance fixes. Finally, you create and explain another query workload that contains the SQL statements in your application, and compare the EXPLAIN snapshot for the first query workload to the EXPLAIN snapshot for the second query workload.

Although this procedure makes use of two separate query workloads, you can create a single query workload, explain it, migrate or apply maintenance fixes, and then explain the query workload again. You can then compare the two EXPLAIN snapshots.

Procedure

To identify and address changes to access plans due to release migration or applying maintenance fixes:

  1. Create a query workload of the SQL statements that are in your application.
    1. Capture the SQL statements.
    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. Explain the query workload.
    Note: If you captured SQL statements from the following sources and you chose to save EXPLAIN information when creating the query workload, you do not need to explain the query workload.
    • DB2 for Linux, UNIX, and Windows: ACTIVITIES event monitors, EXPLAIN tables, packages, the package cache
    • DB2 for z/OS: Catalog plans and packages, the dynamic statement cache
    1. On the Manage Workloads page, select the query workload and select Explain Selected Workload in the More actions field.
    2. In the Collect EXPLAIN Information window, specify optional values for the runtime environment of the SQL statements that are in the query workload.
  3. Migrate or update your installation of DB2. After you are finished, if the statements are static, rebind the packages with the EXPLAIN option set to YES and the ACTION option set to REPLACE.
  4. Create another query workload of the SQL statements that are in your application.
    1. Capture the SQL statements.
    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.
  5. Explain the query workload.
    Note: If you captured SQL statements from the following sources and you chose to save EXPLAIN information when creating the query workload, you do not need to explain the query workload.
    • DB2 for Linux, UNIX, and Windows: ACTIVITIES event monitors, EXPLAIN tables, packages, the package cache
    • DB2 for z/OS: Catalog plans and packages, the dynamic statement cache
    1. On the Manage Workloads page, select the query workload and select Explain Selected Workload in the More actions field.
    2. In the Collect EXPLAIN Information window, specify optional values for the runtime environment of the SQL statements that are in the query workload.
  6. Compare the two query workloads.
    1. Click the Manage tab on the left side of the workflow assistant.
    2. On the Manage Workloads page, select the query workload that you explained in step 2. Then, click the Compare button.
    3. In the Select to compare with another workload or snapshot window, select the option Compare the access plans for the selected workload with the access plans for another workload and click OK.
    4. Follow the steps in the Compare Access Plans with Access Plans in Another Workload wizard.
    5. When the comparison is finished, look for changes to access plans and to estimated costs. If there are no changes that require action, then you are finished with the comparison. However, if there are changes that require action, proceed to the next step.
  7. If you want to create and tune a query workload that contains the SQL statements with performance problems, select Create a new query workload for tuning in the More actions field of the View Comparison Results by Workload EXPLAIN Snapshots and SQL Statements page of the Compare section. The Generate New Query Workload wizard opens. In this wizard, you can set criteria for including SQL statements in the query workload. After the query workload is created, you can refer to these general steps for tuning it.
  8. If you want to create optimization hints or optimization profiles for one or more problematic statements, follow either of these steps:
    1. Select a statement in the list of statements for which you compared the access plans.
    2. Click the View Detail button at the top of the list.
    3. At the top of the View Comparison Results by SQL Statement Access Plan page, click the Create an optimization hint button or the Create an optimization profile button. The button that appears depends on the database connection that you are working with.
  9. If you want to use the previous set of access plans because they were better, select Lock Down Access Plans for All SQL Statements in the More actions field of the View Comparison Results by Workload EXPLAIN Snapshots and SQL Statements page of the Compare section. Specify the required information in the Generate Scripts for Locking Down Access Plans window. After you generate the scripts, review them and save them. Then, you can deploy them according to the instructions for your version of DB2.
  10. If the SQL statements are static and you tuned a query workload of the problematic statements, created optimization hints or optimization profiles, or locked down access plans, rebind the packages. Set the EXPLAIN option to YES, if you want to be able to use the bind-time EXPLAIN snapshot in future comparisons.

Feedback