The DB2 Optimizer improves with each release of DB2. With these improvements come challenges as well as opportunities.

What HLS Technologies has done here is to present a series of contrasts between the Optimizer choices in V6, V7, V8 and V9. We have taken a series of basic SQL changes and other typical changes such as structure and statistics and created a test case for each. We then extracted the data from SYSCOLUMNS and SYSTABLES. We recreated the tables as PUBLIC.SYSCOLUMNS and PUBLIC.SYSTABLES on a V6, a V7, V8 and V9 subsystem.

This allows virtually the same tests to be run on each subsystem. The test cases present an expected situation and then a modification that is equally likely. We can then contrast the Optimizer choice for each test case before and after. We do this within each version of DB2 and between versions of DB2.

DBAs believe they know what access paths should be chosen. They may be dismayed by the results in this report. They may be surprised by both where differences between releases occur and where they do not!

HLS Technologies is offering both the written analysis of these choices and the load data and DBRMs required to recreate the test cases in your environment. You can then compare your results with ours. We hope to open a dialog that will help us all in finding out more about what influences the DB2 Optimizer access path choices.