Articles & Content

Archives

DB2 V8 Optimizer: Early Prechecking SQL Performance (E08)

Topic: 2006 EU

Subtopic: DB2 for z/OS



DATE: 2006-10-3 (16:45 - 17:45)
SPEAKERS: Stephan Terhorst (LVM Versicherungen)

The DB2 V8 Optimizer has many enhancements under the hood. As DBAs it is our nature to care about performance implications during migration. At LVM Insurance, we implemented procedures that kept us in control of our 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.

EXP. LEVEL: Intermediate,Advanced

OBJECTIVES:

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



This file is being provided by IDUG. We would encourage you to join IDUG to get full access to all of our files and resources. Joining IDUG is FREE and signing up is simple. Click here to join! or login!

Download File
Click to Download

NOTE: These are only open to members of IDUG. If you are not a member, please CLICK HERE for more information.