A key feature of DB2 10 for z/OS yields “out-of-the-box," DB2 CPU savings of up to 10 percent for traditional workloads when compared to running the same workloads on DB2 9. That’s attention-getting, and possible without a REBIND. But to obtain the best performance and memory improvements, it will be necessary to REBIND all PACKAGEs containing static SQL—an idea that can bring fear and angst to many DBAs.
To understand why, let’s review. For static SQL, it’s during the BIND/REBIND process that the DB2 optimizer determines the optimal access path to the data for each SQL statement using various inputs such as the SQL statement text, the schema definition, and the current object statistics from the DB2 catalog. The run-time performance of an SQL statement is directly related to the path the optimizer chooses at BIND/REBIND time. As long as a statement is performing well, especially in production, DBAs hesitate to REBIND unless they absolutely must (for example, the PACKAGE is marked as invalid or inoperative) for fear the DB2 optimizer will choose a less efficient, poorer performing access path.
Is REBIND Required for Migration?
REBIND isn’t required for migration to DB2 10, but it’s strongly recommended to achieve the significant performance improvements. However, all PLANs containing Database Request Modules (DBRMs) and all PACKAGEs that were last bound on DB2 V5 or lower must be rebound. So, as Roger Miller said in a recent IDUG Solutions Journal article, “Please be kind and REBIND.”
Wouldn’t it be nice to know before you migrate to DB2 10 what access path changes to expect? This article discusses a process (homegrown or vendor-purchased) that can analyze and identify access path changes between two DB2 subsystems, one at DB2 9 and one at DB2 10, before migrating. Your actual “code” for this process will vary, but here are the basic steps.
Using the PLAN_TABLE and DSN_DETCOST_TABLE
This process relies on data that’s externalized to the userid.PLAN_TABLE and the userid.DSN_DETCOST_TABLE when executing a BIND/REBIND…EXPLAIN(YES) command. It presumes baseline information exists in those tables in the DB2 9 subsystem for the desired PACKAGE. In most shops, this is a byproduct of the standard for production migrations.
- In the DB2 10 subsystem, create the DB2 objects associated with the PACKAGE exactly the same way as they’re created in the DB2 9 subsystem but with DEFINE NO on the CREATE TABLESPACE and CREATE INDEX statements.
- In the DB2 10 subsystem, create a userid.PLAN_TABLE* and a userid.DSN_DETCOST_TABLE* if they don’t already exist.
- In the DB2 9 subsystem, create a userid.PLAN_TABLE_V10* and a userid.DSN_DETCOST_TABLE_V10* (using the DB210 format).
(*) Note: for steps 2 and 3, the DB2 10 format for these tables is slightly different from the DB2 9 format:
- userid.PLAN_TABLE: 59 columns in DB2 9, 64 columns in DB2 10
- userid.DSN_DETCOST_TABLE: 118 columns in DB2 9, 129 columns in DB2 10.
- For the objects created in step 1, copy all DB2 catalog statistics associated with access path selection from the DB2 9 subsystem to the DB2 10 subsystem. In DB2 9 and 10, there are 17 DB2 catalog tables (see Figure 1) containing user-updateable columns that the DB2 optimizer uses for access path determination. Be sure to get them all or it may skew what the optimizer chooses for an access path in the DB2 10 subsystem.
- In the DB2 10 subsystem, BIND the PACKAGE with EXPLAIN(YES) and OWNER(userid) to externalize access path information to the userid.PLAN_TABLE and userid.DSN_DETCOST_TABLE.
- In the DB2 10 subsystem, unload the data from the userid.PLAN_TABLE and userid.DSN_DETCOST_TABLE.
- In the DB2 9 subsystem, load the data from step 6 into the userid.PLAN_TABLE_V10 and userid.DSN_DETCOST_TABLE_V10.
- In the DB2 9 subsystem, run a query (Figure 2) that will combine the data from the DB2 9 and DB2 10 tables in a way that visually “stacks” the DB2 9 results over the DB2 10 results while grouping it by QUERYNO, QBLOCKNO, and PLANNO.
Identifying Access Path Changes
The query results in Figure 3 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 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 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. A REBIND of static SQL after migrating to DB2 10 is necessary to get the best performance. If you’re concerned about what impact a REBIND may have on your current access paths, you can use this process to evaluate that impact 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 makes the process easier and ensures accurate results.
- “Best Practices for DB2 on z/OS Performance” by Susan Lawson and Dan Luksetich, http://documents.bmc.com/products/documents/83/61/98361/98361.pdf
- “Exploiting Explain” by Susan Lawson and Dan Luksetich, http://documents.bmc.com/products/documents/35/67/93567/93567.pdf
- Computer Measurement Group (CMG), “Basic Understanding of DB2 Plan Tables for the Performance Analyst” by Nancy Perkinson, www.cmg.org/measureit/issues/mit69/m_69_4.html
- IBM DB2 10 for z/OS homepage: http://www-01.ibm.com/common/ssi/cgi-bin/ssialias?subtype=ca&infotype=an&supplier=897&letternum=ENUS210-380#
- IBM DB2 10 for z/OS: Managing Performance SC19-2978-01, http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2z10.doc.perf/dsnpgm01.pdf?noframes=true
- IBM DB2 10 for z/OS Administration Guide SC19-2968-01, http://publib.boulder.ibm.com/epubs/pdf/dsnagm01.pdf
- IDUG 2008 Europe Session G08: Stopping Unwanted Access Path Changes Before Disaster Strikes by Mike Perry, BMC Software, www.idug.org/conferences/EU2008/data/EU08G08.pdf
- IDUG Solutions Journal, “Questions and Answers About DB2 10 for z/OS” by Roger Miller, www.idug.org/images/stories/solution_journal/December_2010/ISJ-2010-12-roger-miller.pdf
- IDUG Solutions Journal, “To REBIND or Not to REBIND: Why Is That Even a Question?” By Craig S. Mullins, www.idug.org/images/stories/solution_journal/December_2010/ISJ-2010-12-craig-mullins.pdf.