Articles & Content


Detecting and Preventing Undesirable Access Path Changes When Moving to DB2 V8 (G09)

Topic: 2007 EU

Subtopic: Cross Platform DB2 for z/OS & LUW

DATE: 2007-11-7 (11:00 - 12:00)
SPEAKERS: Gary Bronziet (Cogito)

SQL response time, throughput and resource utilization are the never ending challenges faced by the DB2 database administrator. Once an acceptable performance level has been established, then avoiding performance degradation when making system or application changes is another challenge which must be addressed.When moving to DB2 V8, users are required to re-bind all plans/packages in the V8 environment. Therefore, the potential for access path changes is one of the major challenges faced by the DBA. Many access paths will undoubtedly change, hopefully mainly for the better. When performing such bulk re-binds, it is clearly impossible to check for and prevent such undesirable access path changes without the help of a tool designed specifically for the purpose.This presentation describes how the EZ-DB2 family of products from Cogito can assist a DB2 user in moving both static and dynamic SQL to DB2 V8 whilst avoiding potentially undesirable access path changes.

EXP. LEVEL: Beginner,Intermediate,Advanced


Defining the scope of an analysis using the available filtering options. For example, the analysis scope may include dynamic and/or static SQL captured by EZ-DB2, or may include static SQL extracted from specified DBRM modules or the DB2 Catalog.

Using the different available comparison functions. For example, predicting or checking the impact of an environmental change; predicting or checking the impact of application migration; predicting or checking the impact of a new DB2 release.

Using the online and batch reports to focus on the most critical access path changes. The different DRILL options will be described as well as the different SQL detail views, including SQL cost and access paths changes and "alerts" added or dropped.

Using the different HINTS functions available, such as making a HINT from a particular access path, or performing a bulk creation of hints. For example, copying existing DB2 V7 access paths to the DB2 V8 system as hints.

Attendees will also learn how EZ-DB2 can be used to safely perform routine DBA maintenance such as RUNSTATS in a Dynamic SQL environment without fear of unexpected access path changes.

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.