DB2 13 for z/OS System Performance and Tuning

The course provides detailed coverage of System Performance Tuning for DB2 for z/OS.

This course is intended for DBAs and systems programmers.

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.

The course provides detailed coverage of System Performance Tuning for Db2 for z/OS. The course includes labs and demonstrations using online Db2 performance monitors and workshops. The course also covers general subsystem topics.

It is applicable to all recent versions of Db2 up to and including Db2 13.

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