Popular Courses

Browse Our Free Resources

  • whitepapers
  • whitepapers
  • webinars
  • blogs

Our Locations

Training Centres

Vancouver, BC
Calgary, AB
Edmonton, AB
Toronto, ON
Ottawa, ON
Montreal, QC
Hunt Valley

locations map


550 6th Av SW
Suite 475
Calgary, AB
T2P 0S2


821A Bloor Street West
Toronto, ON
M6G 1M1


409 Granville St
Suite 902
Vancouver, BC
V6C 1T2

U.S. Office

436 York Road
Suite 1
Jenkintown, PA

Other Locations

Dallas, TX
Miami, FL

Home > Training > DB2 > DB2 for z/OS Application Performance & Tuning Training

DB2 for z/OS Application Performance & Tuning Training

Course#: WA1872

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)

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.

  Two Days.

Outline of WA1872 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

  2. Improving SQL statements using EXPLAIN Plan / VISUAL EXPLAIN results



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. VQUBEVery 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.
*Your name:

*Your e-mail:


*Company name:

Additional notes:

We have received your message. A sales representative will contact you soon.

Thank you!.

more details
buy this course

Register for a courseware sample

It's simple, and free.


Thank You!

You will receive an email shortly containing a link to download the requested sample of the labs for this course.