DB2 Basic Application Performance Course

Description

The course provides coverage of basic application performance tuning for DB2 UDB for z/OS Version 7 and Version 8. It is intended for DBAs, analysts, and programmers. A good working knowledge of DB2 is a prerequisite. The course includes practical exercises.

Duration

3 days

Course Outline

1 Introduction
 - What is Performance?
 - Performance Factors.
 - Performance Objectives.

2 DB2 Architecture
 - DB2 Address Spaces.
 - RDS, DM and BM.
 - Connections and Threads.
 - Serialisation Mechanisms.
 - Resource Accounting.

3 System Performance Overview
 - Buffer Pools.
 - EDM Pools.
 - Logging.

4 Database Design
 - Simple, Segmented, and Partitioned Tablespaces.
 - Tablespace Layout.
 - Indexes and Index Structure.
 - Choosing Indexes.
 - Clustering and Free Space.

5 Single Table Access Paths
 - Tablespace Scan.
 - Matching and Non-Matching Index Scan.
 - One-Fetch Access.
 - Index-Only Access.
 - Multiple Index Access.
 - Buffer Manager I/O.
 - Sequential, Dynamic and List Prefetch.
 - Stage 1 and Stage 2 Predicates.
 - Non-Column Expressions.
 - Predicate Filter Factors.
 - Range Predicates.
 - Non-Uniform Data Distribution.
 - Runstats Options.
 - DB2 Sort.
 - Volatile Tables.

6 Using Explain to Analyse Access Paths
 - Overview of Explain.
 - The Plan Table.
 - Access Path Information.
 - Sequential and List Prefetch.
 - Visual Explain.

7 Multiple Table Access
 - Unions.
 - Inner and Outer Joins.
 - Predicate Pushdown and Join Simplification.
 - Join Algorithms.
 - Explaining Joins.
 - Subqueries.
 - Nested Table Expressions.
 - Scalar Fullselect.
 - Common Table Expressions.
 - Materialized Query Tables.

8 Transaction Locking Basics
 - Lock Size, Mode and Duration.
 - Tablespace Locks.
 - Page and Row Locks.
 - Tuning and Monitoring Locking.

9 Reorganising Tablespaces & Indexes