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

Objectives
On successful completion of this course, attendees 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.

Course Code
DPTA
Contents
Performance Overview
Performance objectives; Workload categories; SLAs; Performance factors; DB2 traces.


DB2 Traces
DB2 trace types and classes; Controlling traces; Trace options.


DB2 Optimizer
Optimizer input; Catalog statistics; Filter factors; Influencing the Optimizer; Optimization hints; RUNSTATS.


DB2 EXPLAIN
Use of EXPLAIN as a first step in performance tuning; Contents of PLAN_TABLE, DSN_STATEMNT_TABLE and DSN_FUNCTION_TABLE; EXPLAIN examples..


Database Design Factors
Single and composite key indexes; clustering and non-clustering indexes.


Application Design Factors
DB2 access paths; indexable and non-indexable predicates; stage 1 and stage 2 predicates; Preftech; use of subqueries, joins and unions, temporary tables, et cetera.


Locking and Concurrency
Lock management; Lock compatability; Locking options; Deadlocks; Claims and drains; Partition independence; Lock avoidance.


Application Programs versus DB2 Utilities
Use of DB2 utilities in place of application programs.