Overview
This course is designed to teach you how to prevent application performance problems and to improve the performance of existing applications.
Public
This advanced course is for DB2 for z/OS application developers, DB2 for z/OS DBAs and anyone else with a responsibility for application performance and tuning in a DB2 for z/OS environment.
Prerequisits
You should have:
- Familiarity with DB2 for z/OS application programming
Objective
- Design better indexes
- Determine how to live with the optimizer (avoid pitfalls, help when necessary)
- Avoid locking problems
- Use accounting traces to find significant performance problems in an operational application
Topics
Introduction to Application Performance and Tuning
- Why Performance Disappoints
- Causes of Performance Problems
- A Simple Example
- SQL EXPLAIN Information
- Modify the Index
- The Accounting Trace
- Accounting Trace Information
- Filter Factors
- Another Index Modification
- Who Detects Problems?
- When Should Problems be Detected?
- VQUBE2
- What is a Touch?
- Further Analysis (1 of 2)
- Further Analysis (2 of 2)
- The Message
Performance Analysis Tools
- Components of Response Time
- Methodology Formulas
- VQUBE2
- Simple Example in VQUBE2
- SQL EXPLAIN
- The Plan Table
- Running the Explain
- Interpreting the Results
- The Accounting Trace
- Reading an Accounting Trace
- The Bubble Chart
- Monitoring Execution
- Performance Thresholds
- Tuning Potential
Towards Better Indexes
- Section 3.1 DB2 Index Structure and Usage, Part 1
- Section 3.2 DB2 Index Structure and Usage, Part 2
- Section 3.3 Tuning Methodology and Index Cost
- Section 3.4 Lab 1: Customer Order Application
- Section 3.5 Index Design
- Section 3.6 Lab 2: Poorly Performing Application
** Machine Exercise 1 - Designing Indexes **
- Section 3.7 Advanced Access Paths
Multiple Table Access
- Section 4.1 Joining Issues
- Section 4.2 Lab 3: Joining Tables
- Section 4.3 Subqueries and Global Query Optimization
- Section 4.4 Lab 4: Different Implementations of One Problem
- Section 4.5 Union, Except, and Intersect
** Machine Exercise 2 - Subqueries **
Towards Better Tables
- Section 5.1 Table Design
- Section 5.2 Materialized Query Tables
- Section 5.3 Temporal Tables
** Machine Exercise 3 - MQTs and Denormalized Tables **
Learning to Live with the Optimizer
- Section 6.1 Dangerous Predicates
** Machine Exercise 4 - NonIndexable and NonBoolean Predicates **
- Section 6.2 Lab 5: Introduction to Browsing
- Section 6.3 Filter Factors
- Section 6.4 Helping the Optimizer
** Machine Exercise 5 - Helping the Optimizer **
Massive Batch
- Section 7.1 Batch Performance Issues
- Section 7.2 Benefit Analysis
- Section 7.3 Massive Delete
Locking Issues
- Section 8.1 Serialization
- Section 8.2 Transaction Locking
- Section 8.3 Application Design
** Machine Exercise 6 - Locking Issues **
Course Summary
Components of CPU Time
