What you will learn | |
After completing this course, the student should be able to:
|
|
Audience | |
This course is for experienced Data Processing personnel who have used SQL and need to explore Application Performance and Tuning issues for BOTH standalone SQL and in COBOL Application programs. |
|
Prerequisites | |
At least six months experience using DB2 SQL and COBOL Programming experience is needed. |
|
Duration | |
Two Days. |
Outline for DB2 for z/OS Application Performance & Tuning Training
Day 1
- DB2 SQL Performance Introduction
- What makes up the Major elements of DB2 SQL Performance?
- What “causes” DB2 SQL Performance Problems?
- How can we “improve” DB2 SQL Performance?
- Filter Factors
- Indexes are NOT free & How to determine their cost
- Performance Overview with Guidelines
Workshop - EXPLAIN Plan / VISUAL EXPLAIN and analyze results
- Stage 1 and Stage 2 Predicates
- Tuning Guidelines and Examples
- EXPLAIN Plan / VISUAL EXPLAIN with Examples – TS, MIS, NMIS, IXONLY, SORT, Part Scan
WorkShop - More EXPLAIN Plan / VISUAL EXPLAIN exercises - Part 1
- SQL RE-Coding Guidelines
WorkShop – Re-Formatting SQL Statements
- Using EXPLAIN Plan / VISUAL EXPLAIN to improve SQL Performance
WorkShop - More EXPLAIN Plan / VISUAL EXPLAIN exercises - Part 2
- The DSN_STATEMNT_TABLE
- Improving SQL statements using EXPLAIN Plan / VISUAL EXPLAIN results
WorkShop - EXPLAIN Plan / VISUAL EXPLAIN - DSN_STATEMNT_TABLE / PLAN_TABLE
Day 2
- Join Performance Analysis - The 3 Join Methods, Influencing the Join Method????
WorkShop - EXPLAIN Plan / VISUAL EXPLAIN –Join Performance Analysis
- SubQuery Analysis - Single Valued, Multiple Valued, Correlated
WorkShop - EXPLAIN Plan / VISUAL EXPLAIN –SubQuery Performance Analysis
- UNION Analysis - The “Good“ and the “Bad“ of UNION
WorkShop - EXPLAIN Plan / VISUAL EXPLAIN –UNION Performance Analysis
- Application Program Performance Analysis – REOPT, Optimize for N rows, Dynamic SQL, PK & FK, Prefetch, Correlated Columns, Massive Batch Jobs, Massive Online Jobs
- VQUBE – Very Quick Upper Bound Estimate
WorkShop - EXPLAIN Plan / VISUAL EXPLAIN – COBOL Program Performance Analysis
- Accounting Trace
- Performance Monitors
Visual Explain, Performance Analyzer