IntroductionCourse Outline
What you will learn
 

After completing this course, the student should be able to:

  • Discuss overall DB2 Performance considerations
  • Discuss the “tuning tools” of EXPLAIN Plan / VISUAL EXPLAIN and analyze results to analyze SQL statement and examine the results
  • Analyze the column values in the PLAN_TABLE and the DSN_STATEMNT_TABLE
  • Review how to Tune / Rewrite SQL queries to improve their efficiency
  • Evaluate the efficiency of “alternative” SQL statements that product the same result sets
  • Develop a set of Performance “guidelines”
  • Discuss various Predicate Types and heir impact on performance
  • Discuss the basic & intermediate access paths and how they impact performance
  • Analyze Multi-Table and other “complex” access paths
  • Review the various DB2 Catalog Statistics that are used by the DB2 optimizer
  • Review different Access Path Selection
  • Apply tuning guidelines to application programs – REOPT(VARS), Optimize for N rows, Dynamic SQL, Cursor processing
  • Discuss the use of the following tools – VQUBE, Accounting Trace, Performance Monitors, Explain Plan / Visual Explain, or similar tool (e.g. IBM’s Performance Analyzer)
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

Day 1

  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
  1. Performance Overview with Guidelines

Workshop - EXPLAIN Plan / VISUAL EXPLAIN and analyze results

  1. Stage 1 and Stage 2 Predicates
  2. Tuning Guidelines and Examples
  3. EXPLAIN Plan / VISUAL EXPLAIN with Examples – TS, MIS, NMIS, IXONLY, SORT, Part Scan

WorkShop - More EXPLAIN Plan / VISUAL EXPLAIN exercises - Part 1

  1. SQL RE-Coding Guidelines

WorkShop – Re-Formatting SQL Statements

  1. Using EXPLAIN Plan / VISUAL EXPLAIN to improve SQL Performance

WorkShop - More EXPLAIN Plan / VISUAL EXPLAIN exercises - Part 2

  1. The DSN_STATEMNT_TABLE
  2. Improving SQL statements using EXPLAIN Plan / VISUAL EXPLAIN results

WorkShop - EXPLAIN Plan / VISUAL EXPLAIN - DSN_STATEMNT_TABLE / PLAN_TABLE

 

Day 2

  1. Join Performance Analysis - The 3 Join Methods, Influencing the Join Method????

WorkShop - EXPLAIN Plan / VISUAL EXPLAIN –Join Performance Analysis

  1. SubQuery Analysis - Single Valued, Multiple Valued, Correlated

WorkShop - EXPLAIN Plan / VISUAL EXPLAIN –SubQuery Performance Analysis

  1. UNION Analysis - The “Good“ and the “Bad“ of UNION

WorkShop - EXPLAIN Plan / VISUAL EXPLAIN –UNION Performance Analysis

  1. Application Program Performance Analysis – REOPT, Optimize for N rows, Dynamic SQL, PK & FK, Prefetch, Correlated Columns, Massive Batch Jobs, Massive Online Jobs
  2. VQUBE – Very Quick Upper Bound Estimate

WorkShop - EXPLAIN Plan / VISUAL EXPLAIN – COBOL Program Performance Analysis

  1. Accounting Trace
  2. Performance Monitors

Visual Explain, Performance Analyzer