DB2 Application Performance Tuning Course

Description

The course provides detailed coverage of Application Performance Tuning for DB2 UDB for z/OS Version 7 and Version 8. It is intended for DBAs, analysts, and programmers. A working knowledge of DB2 is a prerequisite. The course includes practical exercises.

The same material is also available as two separate courses. See DB2 Basic Application Performance Course and DB2 Advanced Application Performance Course

Duration

5 days

Course Outline

1 Introduction

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

3 System Performance Overview

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.
 - Multiple Index Access.
 - Sequential, Dynamic and List Prefetch.
 - Predicates.
 - Access Path Selection.
 - 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, Joins, and Subqueries.
 - Nested Table Expressions.
 - Common Table Expressions.
 - Recursive SQL.
 - Materialized Query Tables.

8 Transaction Locking
 - Lock Size, Mode, and Duration.
 - Tablespace, Page, Row, and Index Locks.
 - Lock Avoidance and Page Latching.
 - IRLM and Locking Parameters.
 - LOB Locking.
 - Monitoring and Tuning Locking.

9 Other Serialisation Mechanisms
 - Partition and Index Independence.
 - Claim and Drain.
 - Restrictive States.

10 Using the Accounting Trace to Monitor Applications
 - Analysing elapsed time components.
 - Counting the SQL statements.
 - Identifying I/O Activity.

11 Dynamic SQL Issues
 - Dynamic Statement Caching.

12 DB2 Attachments
 - TSO, CICS, IMS, Call, and RRS Attaches.
 - Two Phase Commit.
 - Monitoring and Tuning the Attaches.

13 Advanced Optimizer Topics
 - Correlated Column Statistics.
 - View Merge and Materialisation.
 - Subquery Transformation.
 - Direct Row Access.
 - Temporary Tables.
 - Scrollable Cursors.
 - Multi-Row Fetch.
 - More Explain Tables.

14 Parallelism and Star Join

15 Reorganising Tablespaces & Indexes