Db2 for z/OS: Monitoring & Tuning Systems' Performance Training

• identify the Db2 and MVS factors that affect performance
• plan for Db2 subsystem monitoring and tuning
• detect poor performance using various techniques
• identify tuning opportunities
• carry out effective problem detection through the use of an established performance and monitoring policy
• Implement and interpret Db2 related trace outputs covering I/O tuning, buffer pool tuning, EDM pool tuning, IRLM tuning, thread tuning.

Performance Overview

What is 'performance'?; Performance objectives; Workload categories; Service Level Agreements; Performance factors; System parameters; EDM pool size; Buffer pool sizes; Bufferpool thresholds; Database design factors; Application design factors; Db2 traces.

Activating traces; Trace types; Trace destination; Accounting trace classes; Audit trace classes; Statistics trace classes; Performance trace classes; Monitor trace classes; Global trace classes; IFCIDs activated; Other trace options; Controlling traces; Auditing tables; Audit policies.

Monitoring & tuning overview; Db2 monitoring overview; Db2 Performance Expert (Db2PE); Db2PE report sets & traces; Statistics report example; Accounting trace classes and report details; Suspensions.

Db2 system parameters; Modifying system parameters; Specifying start-up parameters; Online parameter management; Changing parameters using commands; Producing a system parameters report; System parameters report.

Buffer pools; 64-bit architecture; Buffer pool size; Buffer pool pages; Buffer page processing; I/O types; Buffer pool thresholds; Fixed thresholds; Variable thresholds; Buffer page stealing; Buffer pool parameters; DISPLAY BUFFERPOOL command; DISPLAY BUFFERPOOL output; Buffer pool hit ratio; Hit ratio determination - DISPLAY BUFFERPOOL command; Hit ratio determination - DB2PE reports; Monitoring buffer pools (write activity); Monitoring buffer pools (I/O); Buffer pool tuning: number and size, thresholds; ALTER BUFFERPOOL command.

The EDM pool; Default EDM pool size; Modifying EDM pool size; Dynamic SQL caching; EDM pool parameters; EDM pool storage usage; Monitoring the EDM pool: general, dynamic SQL, I/O activity; Tuning the EDM pool.

The sort pool; Sort processing: sort phase, sort tree, merge phase; Sort pool size; Sort pool estimation; Sort pool parameters; Monitoring sorts; Sort performance; Sort tuning.

Record Identifiers; RID pool storage; RID pool size; RID pool parameters; RID processing: list prefetch, multiple indexes, hybrid join; Monitoring RID pool; Tuning the RID pool.

Monitoring overview; Db2 Performance Expert (Db2PE); Db2PE reports and traces; Db2PE accounting report: short, long; Accounting report: time distribution, application times, suspensions & highlights; Accounting report; Dynamic SQL caching; Other processing activity; Locking activity; Parallelism; Buffer pool & group buffer pool activity; Db2PE statistics reports.

Threads; Connection types; Controlling connections; Connection parameters; Connection processing; TSO connections; CAF connections; CICS connection control; IMS connections; IMS connection control; Monitoring threads (Db2 command); Monitoring threads (Db2PE); Thread tuning: general, TSO, CICS, IMS.

Locking overview; The Internal Resource Lock Manager (IRLM); Reasons for locking: lost updates, reading uncommitted data, repeatable read: Lock control; Lock options - installation; Locking parameters; Lock options - application development; Locks used by SQL statements; Lock compatibility: row or page locks, tablespace locks; Bind - ACQUIRE and RELEASE parameters; Bind - ISOLATION and CURRENTDATA parameters; Lock avoidance; Lock avoidance example; Monitoring locking: DB2 command, LOCKINFO, EXPLAIN, DB2PE; Tuning locking.

Db2 data-sharing; Coupling Facility structures: cache structure, lock structure; Lock table entries; Using the lock structure; Lock propagation; Coupling Facility structures - list structure; Installing data-sharing; Data-sharing parameters; Group Buffer Pool parameters; DISPLAY GROUP command; DISPLAY GROUPBUFFERPOOL command; DISPLAY GROUPBUFFERPOOL output; ALTER GROUPBUFFERPOOL command; Monitoring Group Buffer Pool activity (Db2PE); Monitoring data-sharing locking; Tuning data-sharing: Group Buffer Pool size, lock structure size, parallelism.