OverviewThis course examines and explains the critical factors that affect Db2 for z/OS performance from a systems perspective. 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.<br><br>This course is only available for one-company, on-site presentations.
PrerequisitesA sound understanding of Db2 and z/OS concepts is required.
Delegates will learn how to
- 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.
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.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.Performance Monitoring
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
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.Tuning Buffer Pools
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.Tuning the EDM Pool
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.Tuning the Sort 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.Tuning the RID Pool
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 Application Performance
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.Tuning Threads
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.Tuning Locking & Concurrency
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.Tuning Data-Sharing
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.