|What you will learn|
After completing this course, the student should be able to:
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.
At least six months experience using DB2 SQL and COBOL Programming experience is needed.
Outline for DB2 for z/OS Application Performance & Tuning
- 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
- 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