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: Monitoring & Tuning Applications' Performance Training

DB2 for z/OS: Monitoring & Tuning Applications' Performance Training

Course#: WA2107

This course examines and explains the critical factors that affect DB2 UDB for z/OS performance. The teaching of the effective use of monitoring and tuning tools is an integral part of all tuning topics throughout the course. This course is appropriate for DB2 for z/OS environments up to and including DB2 for z/OS.


All programmers and applications support personnel with responsibility for DB2 application performance.


A sound understanding of DB2 and z/OS concepts is required.


3 Days

Outline of WA2107 DB2 for z/OS: Monitoring & Tuning Applications' Performance Training

1. Performance Overview

  • What is 'performance'?
  • Performance objectives
  • Workload categories
  • Service Level Agreements
  • Performance factors
  • System parameters
  • EDM pool size
  • Buffer pool sizes
  • Bufferpool thresholds
  • Bufferpool development
  • Database design factors
  • Application design factors
  • DB2 traces

2. DB2 Traces

  • Trace types
  • Trace destination
  • Accounting trace classes
  • Audit trace classes
  • Statistics trace classes
  • Performance trace classes
  • Monitor trace classes
  • Global trace classes
  • IFCID types
  • Other trace options
  • Controlling traces.

3. DB2 Optimizer

  • DB2 optimiser
  • Input to the optimiser
  • Catalog statistics
  • Filter factors and how they are used
  • Influencing the optimiser: Manually adjusting statistics, Using optimisation hints, Modelling production values
  • Catalog statistics
  • Updating statistics using RUNSTATS.


  • EXPLAIN examples: Access paths, Multi-index access, Nested queries.

5. Database Design Factors

  • Normalisation forms
  • De-normalisation considerations
  • Table creation
  • Null attributes
  • Variable length columns
  • Column considerations
  • Constraint and domain management
  • Tablespace considerations
  • Space allocation and free space
  • Work file considerations
  • Data set placement
  • Index structure
  • Creating indexes
  • When and when not to index
  • Indexes and clustering
  • non-key columns in unique indexes
  • Composite keys
  • Reorganising indexes.

6. Application Design Factors

  • Predicates
  • Access paths
  • Indexable and non-indexable predicates
  • SQL statement processing
  • Stage 1 and stage 2 predicates
  • Summary of predicate processing
  • Predicate evaluation sequence
  • Sequential prefetch
  • List prefetch
  • Index lookaside
  • Joins
  • Join transformation
  • Join method
  • Subqueries
  • Non-correlated vs. correlated subqueries
  • Case expressions
  • Union and case performance
  • Table expressions
  • Temporary tables
  • Table comparisons.

7. Locking &

  • Concurrency
  • Reasons for locking
  • Lock compatibility - row and page locks
  • Lock compatibility - table and tablespace locks
  • Bind - ACQUIRE and RELEASE parameters
  • Bind - ISOLATION and CURRENTDATA parameters
  • Lock avoidance
  • Accessing currently committed data
  • Locking and concurrency recommendations.

8. Application Programs versus DB2 Utilities

  • Loading data
  • Online LOAD
  • Deleting data
  • Unloading data using the REORG utility
  • Unloading data using the UNLOAD utility
  • UNLOAD utility restrictions
  • Dynamic SQL in utility statements.

9. Accounting Reports

  • Monitoring response times
  • Suspensions
  • The accounting reports
  • Elapsed and class 2 time distribution
  • Highlights of application performance
  • Class 1, class 2, and class 5 times
  • Wait times (class 3 suspensions)
  • Package times and suspensions (class 7 and class 8)
  • Application termination data
  • Locking activity
  • Buffer pool activity
  • Group buffer pool activity
  • SQL activity
  • RID list and ROWID processing
  • Stored procedures, UDFs and triggers
  • Dynamic SQL and LOB storage
  • Query parallelism
  • DDF activity
  • Data sharing locking.

10. Statistics Reports

  • Statistics reports
  • Highlights of system performance
  • SQL activity 1
  • Subsystem services
  • Locking activity
  • EDM pool
  • Dynamic SQL statements
  • RID list processing &
  • direct row access
  • Query parallelism
  • Stored procedures, UDFs and triggers.

11. Performance Analysis

  • EXPLAIN information
  • DB2PM reports
  • short accounting reports
  • long accounting reports
  • report usage summary
  • a general approach to problem analysis in DB2.
  • Locking overview
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.