Guest post by Jim Kurtz, Advisory Software Consultant
Last week, we discussed comparing workloads in DB2 9 and DB2 10.
Identifying Access Path Changes
Query results let you visually spot differences in an access path. If any of the data for the following five PLAN_TABLE columns has changed from DB2 9 to DB2 10, then the access path will change when you REBIND in DB2 10:
- METH (METHOD): The type of JOIN that’s used (if any)
- ACC (ACCESSTYPE): How the data is accessed
- MTCH (MATCHCOLS): If an index is used, how many of the keys in the index it uses
- IX (INDEXONLY): Y if it can get all the info it needs from the index
- PRE (PREFETCH): What type of prefetch, if any, is used.
This presumes that the stats you copied from DB2 9 to DB2 10 in step 4 are the same stats that were in place when you last rebound the PACKAGE in DB2 9. If the DB2 9 stats you copied are different (e.g., more current) from those used in your last DB2 9 REBIND, then an access path change may be due to the difference in stats and not necessarily to DB2 10. Remember, however, that access path selection also depends upon buffer pool statistics and central processor model and these two factors alone can change your access paths from one subsystem to another, even if all the catalog statistics are identical.
DB2 computes a cost for the access path in units of timerons, an abstract unit of measurement used to provide a rough relative estimate of the resources (or cost) required by DB2 to execute the query; it doesn’t directly equate to any actual elapsed time. The resources used in calculating the estimate include CPU and I/O costs. I/O costs weigh more heavily than CPU—twice as much by default. The timeron value is externalized to the COMPCOST column in the DSN_DETCOST_TABLE and to the TOTAL_COST column of the DSN_STATEMNT_TABLE during a BIND/REBIND…EXPLAIN(YES).
If you expand this process to evaluate and compare same subsystem PACKAGE changes (perhaps as part of your production migration process), then the cost could be used in conjunction with any access path changes to gauge the severity of the change and its possible performance impact. Having said that, remember that cost estimates can sometimes be less than accurate and using tools and/or shop standards that employ a good set of “rules” will help to find suboptimal access paths.
DB2 10 for z/OS promises reduced CPU savings of up to 10 percent by optimizing processor times and memory access, leveraging the latest processor improvements, larger amounts of memory, and z/OS enhancements. But a REBIND of static SQL is required after migrating to DB2 10 is necessary to get the best performance. If you’re concerned about what impact that a REBIND may have on your current access paths, you can use this process to evaluate that impact see if they will change before you do the REBIND. You can complete this process manually, but the process is time-consuming and can be error-prone. Using a vendor-purchased product such as BMC SQL Performance for DB2 simplifies the process and ensures accurate results.