IBM Db2 13 for z/OS System and Data Sharing Performance Tuning

• This course is intended for DBAs and systems programmers.

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.

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 vs 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 &amp 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

Students should have attended:

• the Db2 for z/OS Administration
• Db2 for z/OS SQL Performance and Tuning courses.

• None

• None recommended