DB2 Tuning and Performance - Topical Outline

Introduction
DB2 Architecture
    DB2 objects
    Physical storage structures
    DB2 catalog and directory
    Address spaces
    Connecting to DB2: Threads
    Logging, the boot strap data set, and checkpoints
    Pools: Buffer, Hiper, and EDM
Application Programming Tuning
   The DB2 Sample Application
   Access Strategies Overview
   EXPLAIN and PLAN_TABLE
   Computer Exercise: Access Strategies and EXPLAIN

Access Strategies Details
   Application of Predicates
   Indexable, Stage 1, and Stage 2 Predicates
   Catalog Tables and Access Strategies
   Filter Factors
   Catalog Table Modification
   Computer Exercise: Access Strategis in Aggregated Queries

Join Access Strategies
Star joins
   Computer Exercise: Access Strategies in Joins

Subquery Access Strategies
View and UNION
Query Parallelism
   Computer Exercise: Access Strategies in Subqueries

EXPLAINing plans and packages
Optimization hints
DSN_STATEMNT_TABLE and DSN_FUNCTION_TABLE
Predictive Governing
DSN_STATEMENT_CACHE_TABLE
    Computer Exercise: Hints and Costs

BIND Parameter Performance Considerations
Concurrency and Locking
   Claim/Drain Processing
   Lock Avoidance: Commit LSN and PUNC Bits
   Computer Exercise: Bind parameters and locking

Other Programming Considerations
   Sequential and List Prefetch
   Dynamic SQL
   Batch Issues
   Computer Exercise: Plans, Indexes, and Views

Security and Performance
   CICS Security
   IMS Security
   Table Access Issues
   Authorization Exits
   Security Strategies
Visual Explain
    Computer Exercise: Visual Explain

Attachment Facilities and Performance
   CICS Attachment
   IMS Attachment
   TSO Attachment
   Call Attachment
   Recoverable Resource Services Manager Attachment
   Computer Exercise: Attachment Facility Parameters

Data Base Design Considerations
   Referential Integrity and Normalizing
   Data Bases, Storage Groups, Table Spaces, Tables, and Indexes
   Creating new Indexes
   REORG Implications
   Computer Exercise: Index Support Activities

Utility Considerations
   Utility Partition Independence
   LOAD
   COPY, MERGECOPY, and RECOVER
   Partial Recovery
   REORG, RUNSTATS, and QUIESCE
   Computer Exercise: A Study in Partial Recovery

Other Topics
   Bufferpools and Hiperpools
   Distributed Processing Issues
   DB2 System Parameters
   DB2 Instrumentation Facility
   MVS Issues

Optional Computer Exercise: The Real World

Course information: G-580 / 5 Days   These Materials © Copyright 2007 by Hunter Cobb    V2.x

Course Objectives
Course description
Home page

this page last updated: 01 February, 2007
this page copyright © 2007 by The Trainer's Friend, Inc.