DB2 Migration to DB2 10: Three More Items to Ensure your DB2 Performance

Talking with people at the DB2 Washington DC-Baltimore user group meeting was very interesting. Surveying the group showed that everyone was planning to perform DB2 migration to DB2 10 as soon as possible. Everyone was excited about the DB2 10 performance features and especially the cost savings offered through its new features and functionality.

Since everyone is looking to perform DB2 migration to DB2 10 as soon as possible there are many things that need to be done. As I stated in my previous September 7th, 2011 blog “DB2 Migration to DB2 10-Three Critical Performance Items,” these items include getting up-to-date with all the Hipers/APARs, documenting your pre- DB2 migration CPU-IO performance and looking at processes that might impacted by DB2 10. In addition to those items there are a few others that I have run into recently.

First: Leverage the DB2 Version 8 and DB2 Version 9 DB2 Plan Stability feature. The DB2 Plan Stability feature is a built-in plan and package management process that helps ensure that your DB2 access path is always the best. Through the DB2 Plan Stability set up and process, two or three copies of old static SQL packages can be kept in the DB2 Catalog by using the PLANMGMT(BASIC) or PLANMGMT(EXTENDED) parameters. By leveraging the REBIND SWITCH parameter, the best DB2 access path can be installed to guarantee the best performance even after DB2 migration to Version 10.

Two: Document and capture all your current pre-DB2 migration access paths. Even if your systems DBAs haven’t had a chance to implement Plan Stability yet, capture your DB2 Plan and DB2 Package access paths from your EXPLAIN plan tables. With the two simple queries below, the first one finds all your DB2 EXPLAIN tables and the second extracts the package access path rows documenting your pre-migration access paths. These result rows have the timestamp of when the EXPLAIN was done, so verify that you have current information. Also if you don’t find any PLAN_TABLEs in your system make sure to create them and EXPLAIN each of your current plan and packages.

SELECT *

FROM SYSIBM.SYSTABLES

WHERE NAME LIKE ‘PLAN_TABL%’

 SELECT *

FROM creatorid.PLAN_TABLE

Three: Get rid of all of your simple table spaces. Simple table spaces can be migrated to DB2 10 but cannot be created or recovered within DB2 10. So if a simple table space is migrated and then has a problem, you will be able to recover it but do you really want to support something that you know is not the best situation. So alter, migrate or replace your simple table spaces as soon as possible. That becomes one less thing to do during the DB2 migration to Version 10.

I will be speaking at the IBM Information on Demand Conference in Las Vegas. I will be presenting my “DB2 10 Temporal Database Designs for Performance” on Tuesday, October 25th, 10:30 PM – 2:30 PM. For more details on my presentation click here. I am also arranging a get together for colleagues either Tuesday or Wednesday night before the IBM festivities start. Please let me know if you are attending and maybe we can talk.

I will also be speaking at the International DB2 User Group (IDUG) European Conference in Prague. I will be presenting my “DB2 10 Temporal Database Designs for Performance” on Mon, November 14, 1:30 PM – 2:30 PM. For more details on my presentation click here. (http://www.idug.org/e/in/eid=2&req=info&s=97&all=1 )I will also be involved in the Data Warehousing Special Interest Group (SIG) and the final discussion panel, so please join all the presentations, networking and discussions on DB2. Sign up now at www.idug.org.

Finally, I am going to be presenting at the midwest DB2 Users groups in the 4^th quarter. I will be doing Minneapolis on December 6, Milwaukee, Wisconsin (http://www.wdug.com/) on December 7 and Chicago, Illinois (http://www.mwdug.org/) on December 8. I look forward to seeing everyone at the meetings to discuss all their plans for the 2012 year.

Come see me in any of these venues and ask me your DB2 performance or data warehouse performance questions.