Courses DB2 DB2 for z/OS Application Performance & Tuning Training
Course #:WA1872

DB2 for z/OS Application Performance & Tuning Training

Review overall DB2 SQL Performance Considerations.  Discuss the issues in Performance “problems”.  Discuss the "behavior" or “mis-behavior” of the DB2 Optimizer. Explore DB2 SQL performance issues as revealed via the EXPLAIN Plan / VISUAL EXPLAIN feature. 

The major columns PLAN_TABLE and DSN_STATEMNT_TABLE are analyzed in detail.  The results of the DB2 EXPLAIN Plan / VISUAL EXPLAIN process are reviewed to obtain insights into the Way(s) that the Optimizer chooses the access path to access DB2 data.

Extensive workshops allow the students to test the performance “goodness” of several SQL statements. The Performance Guidelines are applied to COBOL Application programs. Discuss how & when the Optimizer uses Filter Factors based on Valid Statistics & Defaults

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 of DB2 for z/OS Application Performance & Tuning Training

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

We regularly offer classes in these and other cities. Atlanta, Austin, Baltimore, Calgary, Chicago, Cleveland, Dallas, Denver, Detroit, Houston, Jacksonville, Miami, Montreal, New York City, Orlando, Ottawa, Philadelphia, Phoenix, Pittsburgh, Seattle, Toronto, Vancouver, Washington DC.
View Course Outline
Share This
Request On-Site or Customized Course Info
View Scheduled
Classes
View Virtual
Classes
Register For Free
Webinars
View Success
Stories
Browse More Free
Resources
USA - 1.877.517.6540 Canada - 1.877.812.8887