Comparing two collections that contain the same packages

Use this comparison to find out how rebinding a collection of packages after, for example, migration of DB2® for z/OS® would alter access plans, improving or degrading the performance of the SQL statements in those packages.

About this task

When you migrate DB2 for z/OS to a new version, it can be difficult to determine whether to rebind a collection of packages. Rebinding might change the access plans of the SQL statements, and change the performance of statements for the better or for the worse.

For the same reason, it can be difficult to determine whether to rebind a collection of packages after you run the RUNSTATS command to update statistics.

Use the Compare Access Plans window to generate a report that shows you how the performance of SQL statements changes due to a rebind of a collection of packages.

Procedure

  1. Before migrating versions or running RUNSTATS, bind any packages that are going to be analyzed with the EXPLAIN option set to YES.
  2. After migrating versions or running RUNSTATS, bind the packages to a new collection with the EXPLAIN option set to YES.
    • The packages must be exactly the same in the original and new collections.
    • The application programs that access the packages must not be changed in any way or rebuilt.
    • Ensure that the EXPLAIN output for the BIND operation points to the same set of EXPLAIN tables that you used when binding the original collection.
  3. Open the Compare Access Plan Costs window.
    1. In the Data Source Explorer, connect to the subsystem with the two collections.
    2. Expand the connection.
    3. Right-click the subsystem and select Query Tuner > Compare Access Plans.
  4. Select Compare two collections that contain the same packages.
  5. Specify the original collection in the Source Collection Name field.
  6. Specify the new collection in the Target Collection Name field.
  7. Click Add.
  8. Click Generate Report.

What to do next

If the performance of the SQL statements meets your goals, rebind them to the original collection.

If the performance of any SQL statements regressed, create a query workload with those statements. You can then tune them collectively or individually.


Feedback