Dirk Johann

Speaker Details


Dirk Johann is a senior DB2 consultant at Software Engineering Germany. With more than 17 years of hands-on experience in the MVS OS/390 z/OS world, his DB2 skills encompass application programming, monitoring, performance optimization, maintenance, and quality assurance. As a specialist in the implementation of DB2 quality assurance, he is involved in the onsite QA processes at large banking and insurance installations in Germany.

Past presentation
13 June 2006
16:00 - 16:45
SQLAdria Seminar – Dubrovnik 2006 DB2 V8 Experiences – Access path patterns

Full Presentation Abstract:
The DB2 V8 Optimizer has many enhancements under the hood. As DBAs it is our nature to care about performance implications during migration. At several customers, we implemented procedures that kept us in control of access paths and insured good SQL performance before V8 migration. In this presentation, I will show the methodology we used to implement a V8 early precheck of all dynamic and static SQL while still on a production V7 system. Not only were we able to predict and evaluate the resulting V8 access paths, we also determined that patterns of expected access path changes will occur using the V8 Optimizer. By making such Optimizer decisions transparent, we took our performance-securing measures one step further. By using the results of the early precheck in combination with the new function of the V8 RUNSTATS utility regarding the column distribution statistics and skewed columns, we were able to influence Optimizer decisions under V8.

Presentation Outline:
1.Comparing access path changes between two DB2 Versions: the procedure
Simulating a V8 comparison environment
Evaluating V7 and V8 differences
2.A look at specific types of access path changes that are typical under V8
Static SQL
Dynamic SQL
3.Risk control management
Pinpointing which access path changes are relevant
Securing access paths under V8
4.Exploiting V8 Column Distribution Stats
Nourishing the V8 Optimizer with extended statistics
And, how early recognition of expected access path changes can help
Presentation Bullet Points:
Stage a V8 environment in which to do an SQL performance early precheck
Identify the Optimizer differences between V7 and V8
Show expected access path changes when migrating
Show access path change patterns for static and dynamic SQL
Opportunities to secure application performance using extended features of the V8 RUNSTATS



SQL Adria is the independent, non-profit organization that gathers relational database users for Croatia and Slovenia. It was founded 1994. and in the same year it has become the regional user group.

Follow us



Events in 2024

VODICE -  10th - 13th June 2024