DB2 Advanced Application Performance Course

Description

The course provides coverage of advanced application performance tuning for DB2 UDB for z/OS Version 7 and Version 8. It is intended for DBAs, analysts, and programmers. Students should have attended the Basic Application Performance course or have a similar level of knowledge before attending this course. The course includes practical exercises.

Duration

3 days

Course Outline

1 Transaction Locking
 - Lock Size and Mode.
 - Tablespace Locks.
 - Page Locks.
 - Lock Duration.
 - Index Locks.
 - Lock Avoidance.
 - Page Latching.
 - C_LSN and PUNC.
 - IRLM.
 - Locking Parameters.
 - LOB Locking.
 - Monitoring and Tuning Locking.

2 Other Serialisation Mechanisms
 - Partition and Index Independence.
 - Claim and Drain.
 - Restrictive States.
 - Inter-Utility Serialisation.

3 Using the Accounting Trace to Monitor Applications
 - The DB2 Instrumentation Facility.
 - Trace Types.
 - Accounting Trace.
 - Analysing elapsed time components.
 - Monitoring SQL and Locking.
 - Monitoring Buffer Pools.
 - Package Level Information.
 - SQL Trace.
 - Monitoring Locking.

4 Dynamic SQL Issues
 - Dynamic SQL Basics.
 - General Caching Issues.
 - Global Cache.
 - Local Cache.
 - Both Global and Local Cache.
 - Cache Management.
 - Monitoring the Global and Local Cache.

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

6 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.

7 Parallelism and Star Join