Comparing access plans for two different versions of the same packages in one collection

You can find out whether rebinding a set of packages made changes to access plans and whether those changes slow the execution of SQL statements.

Before you begin

Ensure that the prerequisites for comparisons that use packages are met.

About this task

Use this procedure if you rebind packages regularly and want to gain an understanding of the changes in access plans before and after rebinding.

Procedure

To compare access plans for two different versions of the same packages in one collection:

  1. Bind the packages that you plan to analyze, setting the EXPLAIN option to YES.
  2. Bind a new version the same packages in the same collection, setting the EXPLAIN option to YES.
  3. Create a comparable workload and run the comparison. A comparable workload is a workload that you create specifically for the comparison. It a logically grouping of both the packages that are in the original collection and the packages that are in the new collection.
    1. In the Data Source Explorer, connect to the DB2® for z/OS® subsystem on which the collections are located.
    2. Right-click the connection and select Analyze and Tune > Start Tuning.
    3. Click the Manage tab on the left side of the workflow assistant to open the Manage section.
    4. On the Manage Workloads page, click the Create Workload for Comparison icon, which is at the top of the page and looks like this: Create Workload for Comparison icon
    5. Follow the steps in the Create Workload for Comparing Access Plans in Packages wizard.
  4. On the Comparison History page, select the comparison and click Review Results.

Results

The View Comparison Results by Package Summary page opens. Double-click listed packages to see the comparisons of individual statements. Double-click statements to see the details of changes to access plans. You can create PLAN_TABLE hints for individual statements, or you can rebind individual packages with the SWITCH option, so that SQL statements use earlier, better access plans.

Feedback