In this blog, I’ll describe how to launch the feature, and then I’ll describe some common scenarios.
Launching the Access Plan Comparison
First you must be connected to a DB2 for z/OS server. You can then invoke this feature by going to Data Source Explorer->Query Tuner->Compare Access Plans.
The following input screen is displayed:
Each of the options circled above is useful for different scenarios, which I’ll describe here.
Option: Compare two collections that contain the same packages
Use this option in scenarios such as:
- Identifying packages whose performance can be impacted following DB2 migration from one release to another. It can help you identify packages that can benefit from REBIND after migration and packages that should not be rebound
- After running RUNSTATS to identify packages whose performance can be impacted if a REBIND is performed.
Let’s look at the first scenario in more detail - version to version migration. In DB2 for z/OS environment, after migration you have two options for running application programs that contains embedded SQL statements:
- REBIND – Rebind the existing packages to make use of new features that are introduced in the newer version. However, the rebind may alter (positively or negatively) the access plan of SQL statements in the underlying packages.
- Do nothing – Continue to operate with the packages that were bound in the earlier DB2 version. Thus there are no changes in the access plan of the SQL statements in the underlying packages. The flip side is that some of the SQL statements that could benefit from changes in the newer DB2 version would still operate in the old way.
Assume that you want to determine if you need to rebind the selected collections (group of packages) to exploit new features available in the new version of DB2 for better performance. You would need to do the following:
- Before the migration, bind any packages that are going to be analyzed with EXPLAIN(YES).
- After the migration, bind the packages to a new collection with EXPLAIN(YES) to insure that the current packages are not overlaid and are available for execution. The packages must be exactly the same in the before and after migration collections. The underlying application programs must not be changed in any way or rebuilt. Also be sure that the EXPLAIN output for the bind points to the same set of EXPLAIN tables. In either case, the access plan comparison report will show an error if you don’t do this correctly.
- From Optim Query Tuner, in the input screen shown above, you specify the sets of collections to be compared by entering the name of the first collection in the Source Collection Name field. For example, in a migration scenario, the source collection might be the name of the collection prior to the migration and the target collection might be the name of the collection following the migration. You can also use filtering to narrow down to a subset of packages.
- Click Add. See the screen-shot below for an example (you can repeat the above steps to add additional collections):
- Click Generate Report to get the comparison report. Query Tuner will look into the catalog tables and EXPLAIN output to compare the access plan of all the operative and valid SQL statements in the underlying packages pre and post migration of the selected collection(s) and then generate an HTML comparison report.
The HTML report is self-descriptive. It displays result in two sections:
Summary Table shown below:
Lists out all the compared packages in decreasing order of the performance degradation. Thus the package whose performance has degraded the most is shown at the top. The package cost is determined by aggregating the cost of individual SQL statements within the package. TOTAL_COST is used as a parameter for computing performance degradation. However TOTAL_COST information was added in the explain table only in DB2 z/OS v8 new function mode. For releases that don’t have this information, the PROCMS value is used to determine the performance degradation. For easy navigation, your can click on the package name to navigate to the section where the package details are listed.
Package Table shown below:
Lists out all the SQL statements within a package in the decreasing order of the performance degradation. The same parameter that was used for determining package degradation is used for computing individual SQL degradation also.
Use this report to determine whether or not the performance of the SQL statements in the target collection has degraded or whether any packages have errors. Packages that have errors are highlighted in red, indicating one of the following reasons:
- The collection name is invalid or no qualifying packages were found.
- The package content was different between the two collections.
- A package appears in only one of the collections.
- The explain entries are missing for a package in one or both of the collections.
What to do next
You would either rebind the package/s to the original collection to make them live, or begin analysis of those statements that have been identified as having degradation in performance and not rebind them until you have resolved any performance issues. You can use Optim Query Tuner to analyze the SQL statements that have problems and to receive specific advice about the problem queries.
Option: Compare two different versions of the same collection
Use this option for those cases when you rebind packages regularly and want to gain an understanding of the changes in the access plan before and after the rebind. As with the previous scenario, bind any packages that are going to be analyzed with EXPLAIN(YES), before the rebind. Then rebind packages to the original collection with EXPLAIN(YES) to insure that the current packages are overlaid and are available for execution. The packages pre- and post-rebinds must be exactly the same. The underlying application programs must not be rebuilt or changed in any way.
Now, in the input screen you will specify the name for the collection that contains the packages to be analyzed in the Collection Name field as shown here:
Click Generate Report to get the comparison report. Query Tuner will look into the catalog tables and EXPLAIN output to compare the access plan of all the operative and valid SQL statements in the underlying packages pre and post rebind of the selected collection(s) and then generate a comparison report. From the generated HTML report you can determine whether or not the later version of the collection has degraded performance.
I hope you find this new feature useful. Let us know what you think. You can also ask questions and discuss with other users on the Optim Query Tuning Solutions discussion forum.