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