Articles & Content


Static SQL and Access Path Review - Tips and Tricks (E05)

Topic: DB2 for z/OS

Subtopic: 2010

DATE: 2010-5-12 (08:30 AM - 09:30 AM)
SPEAKERS: Paul Walters (Sallie Mae)

Demonstrate an approach for reviewing static SQL and Access Paths for zOS. Identify the impact of System Software, Statistics Changes and Application changes upon an access path at bind time. Review specific sources of information such as the DB2 Catalog, Plan Tables, Real Time Statistics and performance data that indentify problems prior to impacting a production environment.

EXP. LEVEL: Beginner


Managing Change - Types of Changes: - DB2 Changes - ZPARMS - Statistics - Application Changes

Data to Review: 1. Plan tables a. PLAN_TABLE b. DSN_STATEMENT_TABLE c. DSN_FILTER_TABLE d. DSN_DETCOST_TABLE e. DSN_PREDICATE_TABLE 2. Objects a. Object information and creation b. Object placement in buffer pools 3. Statistics a. DB2 Statistics b. Real Time Statistics 4. SQL statements and source code 5. Key indicators a. Plan Selection Indicators b. DB2 statistics and Real Time Statistics c. Package Execution Statistics

System Changes: 1. Impacts to Access Path Selection 2. Identification 3. Managing rebinds during upgrades

Application Changes: 1. New Packages 2. New Objects 3. Changed Objects 4. Changed Packages

Best Practices: 1. Explain(YES) 2. Regular review 3. Know your application

Click Here to Download

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