Finding and fixing changes to access plans after adding, removing, or modifying SQL statements in an application

Typically, when SQL statements are added, removed, or modified in a source program, you must rebind the related DB2® package by running the BIND command with the ACTION(REPLACE) suboption. Running this command 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, you create and explain a query workload that contains the SQL statements in an application. Then, you make the required changes to the SQL statements in your application. Next, you create and explain another query workload that contains the SQL statements in your application. Finally, you compare the EXPLAIN snapshot for the first query workload to the EXPLAIN snapshot for the second query workload.

An EXPLAIN snapshot in InfoSphere® Optim™ Query Workload Tuner is a set of EXPLAIN information that is collected at a point in time.

Procedure

To identify and address changes to access plans due to adding, removing, or modifying SQL statements:

  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. Add, remove, or modify SQL statements in your application. 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 the SQL statements are static and you tuned a query workload of the problematic statements, or created optimization hints or optimization profiles, 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