Audience

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

Outline for 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.

4. DB2 EXPLAIN

  • EXPLAIN
  • PLAN_TABLE
  • DSN_STATEMNT_TABLE
  • DSN_FUNCTION_TABLE
  • 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
  • UNION
  • 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