Audience
This course is intended for DBAs and systems programmers.
Prerequisites
Students should ideally have attended the Db2 for z/OS Administration and Db2 for z/OS SQL Performance and Tuning courses or have a similar level of knowledge prior to attending.
Duration
4 Days.
Course Objectives
The course provides detailed coverage of System Performance Tuning for Db2 for z/OS. It is applicable to all recent versions of Db2 up to and including Db2 13. The course includes labs and demonstrations using online Db2 performance monitors and workshops. The course also covers general subsystem topics.
Course Benefits
It is commonly accepted that the performance of any Db2 application is largely dependent on efficient database design and application coding. Having said that, a poorly tuned Db2 system can undermine the efforts of the development team. This course will help production DBAs and Systems Programmers ensure they provide an optimally tuned environment for their Db2 workload. It will guide them in collaborating with colleagues working with z/OS, CICS, IMS, WebSphere etc. to achieve the right balance between throughput and response times. They will be confident in interpreting Db2 Statistics trace data in order to carry out the appropriate tuning actions.
Course Content
Introduction
Factors determining performance.
Performance Objectives.
Db2 Architecture
Db2 Address Spaces.
Db2 Resource Managers.
Connections and Threads.
Serialisation Mechanisms.
Resource Accounting.
Parallel Sysplex.
Db2 Data Sharing.
CFRM Policy.
Protecting Data Consistency.
Group Buffer Pools.
Global Locking.
System Resource Factors
Virtual Storage Requirements.
Db2 System Parameters.
Allied Address Spaces.
Db2 and z/OS Workload Manager.
Costs of Data Sharing.
CPU Costs.
CF Links.
Sizing CF Structures.
CF v ICF.
Monitoring & Reporting
Db2 Instrumentation Facility.
Statistics, Accounting, and Performance Traces.
Db2PE Statistics Report.
Monitoring Storage Use.
Db2 Commands.
MVS XCF Commands.
Retained Locks.
RMF Reports.
Buffer Pool Processes
Buffer Pool Basics.
Getpage Processing.
Sequential, Dynamic, and List Prefetch.
Parallelism.
Db2 Sort.
Buffer Pool Thresholds.
Group Buffer Pools.
Pageset P-Locks.
Page Registration.
Directory Reclaims.
GBP Writes.
Page P-Locks.
Castout.
GBP Duplexing.
Monitoring & Tuning the Buffer Pools
Buffer Pool Assignments.
Buffer Pool Settings.
Prefetch and Parallelism.
Monitoring and Tuning the Local and Group Buffer Pools.
Controlling GBPs.
Initial Sizing of the GBPs.
Group Buffer Pool Tuning.
Reducing Inter-Db2 R/W Interest.
Monitoring Group Buffer Pools.
GBP Tuning Recommendations.
Monitoring Using Commands.
The EDM Pools
EDM Pool and DBD Cache.
Dynamic Statement Caching.
EDM Pools Monitoring and Tuning.
Log Buffers
Commit Processing.
Forward Recovery and Rollback.
Active Log, Archive Log, and BSDS.
Checkpoints.
Fast Log Apply.
Monitoring and Tuning Db2 Logging.
Transaction Locking
Lock Size, Mode, and Duration.
Tablespace Locks.
Page and Row Locks.
Data Sharing Global Locking.
Page Latching, C_LSN, and PUNC.
IRLM.
Locking Parameters.
LOB Locking.
Global Lock Structure.
Lock Processing.
False, XES, and Real Contention.
Lock Structure Duplexing.
Initial Sizing of LOCK1.
Lock Structure Tuning.
Lock Avoidance.
Monitoring and Tuning Locking.
SCA Tuning
Application Issues
Controlling GBP Caching.
Reducing Page P-Locks.
Reducing Pageset L-Lock Activity.
Reducing L-Lock Propagation.
Sysplex Query Parallelism.
Db2 Accounting Trace.
Other Serialisation Mechanisms
Partition Independence.
Claim and Drain.
Restrictive States.
Monitoring Claim and Drain.
Db2 Attachments
The TSO, CICS, IMS, and Call Attaches.
Two Phase Commit.
Monitoring and Tuning the Attaches.
High Performance Database Access Threads.
DDF, SPs, and UDFs
Utilities and Special Processes
Utility Performance Factors.
Utility Serialisation.
Load, Reorg, Copy, Recover, and Runstats.
Monitoring Utilities.
Db2 Catalog and Directory.
Utility History.
Using RMF for System Monitoring