On successful completion of this course you will be able 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.

• 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
• 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

Live instructor-led training, with plenty of opportunities for hands-on exercises and discussion.

Delivered as a live, interactive online training.

A sound understanding of Db2 and z/OS concepts is required.

All those responsible for Db2 systems' performance.