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.
Guest post by Jim Kurtz, Advisory Software Consultant
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 it's 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 may cause fear and angst to most 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 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 post 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).
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 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 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.
* 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
Next week, we'll look at the results of the query and an easier way to compare access paths.
The postings in this blog are my own and don't necessarily represent BMC's opinion or position.
I am proud to announce that the BMC solutions for DB2 for z/OS support DB2 10. In addition to DB2 10 support, this release provides more zIIP enablement, an enhanced GUI for the performance tools, and more Advisor technology. Our Performance Advisors can help you migrate to DB2 10 with a smart rebind strategy, maximize savings with an intelligent index tool, and manage by exception.
See for yourself how BMC solutions for DB2 can help you reduce costs while improving service. Don't miss this opportunity to make the most of your DB2 investment, provide unparalleled service to your users, and achieve even higher levels of efficiency across your organization.