Comparing access plans for SQL statements in packages

Use this comparison to identify SQL performance changes in packages that were rebound due to SQL statement changes, environment changes, release migration, or running the RUNSTATS command.

Before you begin

Procedure

To compare access plans for SQL statements in packages:

  1. Create a workload for access plan comparison. The workload consists of a set of filters that can be used to compare the access plans and analyze the performance of SQL statements in packages. The workload can be stored and reused for future comparison.
    1. In the Data Source Explorer, connect to the subsystem with the packages for comparison.
    2. Expand the connection and open the workflow assistant by clicking Analyze and Tune > Start Tuning.
    3. Click the Manage tab to open the Manage and Tune Workloads page and click the Create Workload for Comparison icon.
  2. Run the access plan comparison using the workload generated in step 1.

What to do next

If the performance of the SQL statements meets your goals, use the rebound packages. Change and rebuild the application program as necessary.

If the performance of any SQL statements regressed, you can take one of these actions.
  • Create a query workload with the regressed statements. You can then tune them collectively or individually. Then, rebind the packages.
  • Create a plan hint and rebind the packages to apply the hint.
  • If you are using DB2 for z/OS Version 9 or higher, you can revert to a previously stored access plan.

Feedback