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

  • Learn via: Classroom / Virtual Classroom / Online
  • Duration: 3 Days
  • We can host this training at your preferred location. Contact us!

This course examines and explains the critical factors that affect Db2 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.<br>This course is appropriate for Db2 for z/OS environments up to and including Db2 11 for z/OS.<br><br>This course is also available for one-company, on-site presentations and for live presentation over the Internet, via the Virtual Classroom Environment service.

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

  • plan for application monitoring and tuning
  • identify database design factors that affect performance
  • identify application design factors that affect performance
  • use EXPLAIN as a first step towards performance tuning
  • use Db2 traces to identify problem areas and identify tuning opportunities.

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.

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.

Db2 Optimizer & Access Path Selection

Db2 optimiser; Access path selection; Access paths - index usage; Access paths - direct row access; Access paths - matching index access; Access paths - non-matching index access; Access paths - table(space) scan; Input to the optimiser; Filter factors; Filter factor and: clustering, column cardinality, column correlation, column distribution; Influencing the optimiser; Influencing the optimiser: manually adjusting statistics, using optimisation hints, modelling production values; Catalog statistics; Updating catalog statistics using RUNSTATS; Statistics columns; RUNSTATS examples.


EXPLAIN; PLAN_TABLE; DSN_STATEMNT_TABLE; DSN_FUNCTION_TABLE; EXPLAIN examples: Access paths, Multi-index access, Nested queries.

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.

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; UNION; Case expressions; Union and case performance; Table expressions; Temporary tables; Table comparisons.

Locking & Concurrency

Locking overview; 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.

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.

Monitoring Application Performance

Monitoring overview; Db2 Performance Expert; Db2PE reports and traces; accounting short report; accounting long report; time distribution reports; times, suspensions & highlights report; SQL activity report; dynamic SQL caching report; other processing activity report; locking activity report; parallelism report; buffer pool and group buffer pool activity reports; statistics long report.

Performance Analysis

EXPLAIN information; Db2PM reports; short accounting reports; long accounting reports; report usage summary; a general approach to problem analysis in Db2.

Contact us for more detail about our trainings and for all other enquiries!