DB2 10 for z/OS Application Performance and Tuning

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