Three Things to Do to Retain Your DB2 Query Performance

Everyone is really wondering whether the DB2 performance improvement numbers are really true for DB2 10. The answer is a big “YES” according to the majority of the DB2 10 Beta customers. The DB2 performance figures are true and sometimes better than the 5-10% saving right out of the box that is being advertised. But this is not to say that your DB2 10 migration and experience will be as good or better. Previous DB2 version migration horror stories abound and, as a DB2 consultant, I have helped many clients tune and improve their DB2 system and application performance. Some of these engagements have seen DB2 performance improvements by correcting DSNZPARM settings that obviously got messed up. Did it happen during a migration, maybe? So here are three things you can do now in DB2 V9 that will help your application retain its current DB2 query performance and get the most out of your DB2 10 system and application.

Gather Current DB2 Performance and DB2 Explain Output

To retain your DB2 performance going into DB2 10, you first need to understand and measure your current DB2 9 performance. Gather performance figures and EXPLAIN output for all your applications. Gather and understand the SQL access and overall processing of the application. Having these statistics before going into a DB2 10 migration is the first step to understand how much improvement your systems and applications are experiencing from the new DB2 10 features.

Leverage DB2 Version 9 BASIC and EXTENDED Plan Stability Features

To retain your DB2 performance, leverage DB2 Version 9 BASIC and EXTENDED plan stability features. The DB2 9 BASIC and EXTENDED plan stability features are there to provide an easy way to preserve or fall back to a good package access path. By using this feature you can save off a good access path associated with the EXPLAIN information that was gathered in Step 1. Also by setting up the BASIC and EXTENDED plan stability features any special bind parameters or table/index statistics considerations can be exposed and documented before the migration to DB2 10. In addition the REBIND process will help your system make the transition to all DB2 packages from DB2 plans and bind everything in the current DB2 9. If you are migrating from DB2 Version 8, get all your DB2 Plans to Packages and make a copy of the Packages with a different OWNER or COLLID such as “SAVED” or something obvious. This way you can copy back, include the backup collection or manipulate your application to use these SAVED packages.

Determine Impact of DB2 10

Next, determine whether there is a high, medium or low probability that the DB2 package will be influenced or leverage the new DB2 10 features. The DB2 10 improved parallel INSERT into multiple indexes feature will improve elapsed time but not CPU. While the new Stage 1 SQL Optimization improvements will potentially cut both elapsed and CPU time for your applications. Determine which of your individual application DB2 packages could see a benefit from the many DB2 10 features. (If you need a full list go to my DB2 10 White Paper here ( for a complete list of the DB2 10 enhancements.) Analyze your applications and understand which ones will benefit or may have potential issues before your DB2 migration. Use your Plan Stability BASIC and EXTENDED packages to use the best performing access path regardless of whether it is DB2 10 or DB2 9 and you will definitely have success and a good experience once your DB2 10 migration is complete.

Also if you are or someone at your company is attending the IOD Conference at the Mandalay Bay in Las Vegas please come to my session on and say “Hi”.


Tuesday, October 26, 2010

Database Performance Discoveries and Recommendations TDZ-3077A

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

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>