DB2 10 for z/OS and REBIND…Part 2

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.

Summary

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.

These postings are my own and do not necessarily represent BMC's position, strategies, or opinion.

Share This Post


Jonathan Adams

Jonathan Adams

Jonathan Adams is vice president and general manager of ZSolutions and Select Technologies at BMC Software, Inc. He leads product management and research and development for the company’s full suite of mainframe products as well as those in its Select Technologies portfolio including solutions for DB2, IMS and MainView as well as the MLC Cost Optimization suite. During Adams’ 20+ year career with BMC, he has led BMC to significantly improving product reliability and delivering new releases that target customers’ top needs. Adams began his career as a systems programmer for BellSouth Services. He received his bachelor’s degree from the University of Alabama and a master’s of business administration from the University of Alabama at Birmingham.