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.
DB2 for z/OS: Monitoring & Tuning Applications' Performance
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.
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.
Face-to-face learning in the comfort of our quality nationwide centres, with free refreshments and Wi-Fi.
Find dates and prices
We have 4 courses available across 2 locations on 4 different dates
EastCall us on 01753 898320 to discuss availability in this region.Contact us
London1 Location / 1 courseView dates
MidlandsCall us on 01753 898320 to discuss availability in this region.Contact us
NorthCall us on 01753 898320 to discuss availability in this region.Contact us
ScotlandCall us on 01753 898320 to discuss availability in this region.Contact us
South1 Location / 3 coursesView dates
Attend from AnywhereCall us on 01753 898320 to discuss availability in this region.Contact us
All locations2 Locations / 4 coursesView dates
Fully accredited to ensure we provide the highest possible standards in learning