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


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.
This course is appropriate for DB2 for z/OS environments up to and including DB2 11 for z/OS.

This course is also available for one-company, on-site presentations and for live presentation over the Internet, via the Virtual Classroom Environment service.

Public dates - click to book!

30 August 2017

Objectives

On successful completion of this course you will be able to:

  • 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.

Who Should Attend

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

Prerequisites

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

Duration

3 days

Fee (per attendee)

£1425 (ex VAT)

Course Code

DPTA

Contents

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.

DB2 EXPLAIN

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.


© RSM Technology 2017