Leverage DB2 Version 9 BASIC and EXTENDED Plan Stability Features

Last week I talked about leveraging the DB2 Version 9 BASIC and EXTENDED plan stability features. I also commented that if you don’t have DB2 9 and are coming from DB2 Version 8 to DB2 10 that you can still save your access plans to maintain DB2 performance.

If you are migrating from DB2 Version 8, move all your DB2 Plans to Packages and make copies of the Packages with different OWNERs or COLLIDs such as “SAVED” or something equally obvious. This way you can copy back, include the backup collection or manipulate your application to use these SAVED packages. Keeping the old SQL optimization and access path information is vital. The following diagrams show how to setup a way save your SQL optimization access path and then back out a module. This worked for me a long time ago Version 3 or 4 and for a client with Version 7, so make sure you test out your setup and back out procedures. Then the process can be quickly executed by operations personnel with a REXX exec or library control process such as Endevor or other change management products. Since a picture is a thousand words below are two charts that illustrate how to set up a simple DB2 PLAN PKLIST access path preservation process to retain DB2 performance.

Gene Fuh and Terry Purcell wrote an article describing this type of technique and the CURRENT PACKAGESET options for protecting your access paths. Check out “Insurance for Your Access Paths Across Rebinds” for further detailed explanation.

Remember DB2 Plan Stability with DB2 9 and DB2 10 is the best and simplest way to implement a method to protect your SQL Optimizer access paths. There are also software tools on the market for handling more complex backup and recovery situations. Use DB2 10 Plan Stability as soon as possible, but there are several other techniques to protect your access paths and guarantee performance.

My DB2 10 information has been put into a collaborative book with information from Roger Miller, Julian Stuhler, Surekha Pureka. Our book DB2 10 for z/OS – Cost Savings ….. Out of the Box along with several other books is being given away at IOD this year. View the IOD conference website for a complete list of books and book signings at IOD Conference. [make the entire sentence a link to http://bit.ly/bT5xxv ]

If you are or someone at your company is attending the IOD Conference [link to IOD conference link] at the Mandalay Bay in Las Vegas please come to my session and say “Hi.”

Database Performance Discoveries and Recommendations TDZ-3077A

Tuesday, October 26, 2010

11:15 AM – 12:15 PM

South Pacific B – Mandalay Bay North Convention Center

DB2 10 for z/OS – Cost Savings ….. Out of the Box (Sessions # 3736 and 3625) Book Giveaway

Roger Miller, David Beulke, Julian Stuhler, Surekha Pureka

Monday at 12:30-1:15

Tuesday at 12:30 – 1:30

At IOD Conference Bookstore

Also if you are or someone at your company is attending the IDUG EU Conference in Vienna

Monday November 8, 2010

Database Performance Discoveries and Recommendations

Session F1 11:30-12:30

Tuesday November 9, 2010
Java DB2 Developer Performance Best Practices
Session 11:00-12:00

Tuesday November 9, 2010
Data Warehousing and Business Intelligence Special Interest Group–

SIG-D 13:00-14:00