Objectives

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

Audience

All those responsible for DB2 systems' performance.

Prerequisites

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

Duration

2 days

Outline for DB2 for z/OS: Monitoring & Tuning for Systems' Performance

1. 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.

2. DB2 Traces

  • Activating traces
  • Trace types
  • Trace destination
  • Trace classes
  • IFCID types
  • Other trace options
  • Controlling traces.

3. Performance Monitoring

  • Monitoring & tuning overview
  • DB2 Performance Monitor (DB2PM)
  • DB2 Performance Monitor reports
  • Accounting reports
  • Statistics reports
  • Monitoring response times
  • Suspensions.

4. DB2 System Parameters

  • DB2 system parameters
  • Modifying system parameters
  • DB2PM System Parameters report
  • Online parameter management
  • Online change parameter behaviour.

5. Tuning Buffer Pools

  • Buffer pools
  • 64-bit architecture
  • Buffer pool size
  • Buffer pool pages
  • Buffer page processing
  • I/O types
  • Buffer pool thresholds
  • Buffer page stealing
  • Buffer pool parameters
  • ALTER BUFFERPOOL command
  • DISPLAY BUFFERPOOL command
  • DISPLAY BUFFERPOOL output
  • Buffer pool hit ratio
  • Buffer pool writes
  • Buffer pool I/O
  • Buffer pool tuning.

6. Tuning the EDM Pool

  • The EDM pool
  • EDM pool storage
  • Modifying EDM pool size
  • Dynamic SQL caching
  • EDM pool & data spaces
  • EDM pool parameters
  • EDM pool storage usage
  • Monitoring the EDM pool
  • Tuning the EDM pool.

7. Tuning the Sort Pool

  • The sort pool
  • Sort pool storage
  • Sort processing
  • Sort pool size
  • Sort pool estimation
  • Sort pool parameters
  • Monitoring sorts
  • Sort performance
  • Sort tuning.

8. Tuning the RID Pool

  • Record Identifiers
  • RID pool storage
  • RID pool size
  • RID pool parameters
  • RID processing
  • Monitoring RID pool
  • Tuning the RID pool.

9. Tuning DB2 Logging

  • DB2 logging overview
  • Log writes
  • Log records
  • Logging parameters
  • Controlling the logs
  • Monitoring the logs
  • Tuning the log
  • Adding active log data sets.

10. Tuning Threads

  • Threads
  • Connection types
  • Controlling connections
  • Connection parameters
  • Connection processing
  • Controlling threads
  • Monitoring threads
  • Thread tuning -general
  • Thread tuning.

11. Tuning Locking & Concurrency

  • Locking overview
  • The Internal Resource Lock Manager (IRLM)
  • Reasons for locking - lost updates
  • Reasons for locking
  • Lock control
  • Locking parameters
  • Lock compatibility
  • Bind parameters
  • Lock avoidance
  • Monitoring locking
  • Tuning locking.

12. Tuning Data-Sharing

  • DB2 Data-sharing
  • Coupling Facility structures
  • Lock table entries
  • Lock Propagation
  • 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
  • Monitoring data-sharing locking
  • Tuning data-sharing.