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.

Classroom dates (High Wycombe) - click to book!

25 April 2018 8 August 2018 17 September 2018

Virtual Classroom Environment dates - click to book!

18 June 2018

What is a 'Virtual Classroom Environment'?

 

What do I need?

  • webcam
  • headphones with microphone
  • sufficient bandwidth, at least 1.5 Mb/s in each direction.

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)

£1475 (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 2018