Workshop - DB2 Buffer Pools Print

Buffer pool settings have a great effect on the overall performance of DB2. Optimal settings save CPU time, postpone memory upgrades and accelerate applications. Improper settings waste resources, and, at the worst, cause an outage of an entire Data Sharing Group.

 

Advisor

 Frank Backer

Type of Seminar

 Lecture and demonstration of case scenarios

Target Audience

 DB2 DBAs, DB2 System Programmers Performance Specialists, Tuning Experts

Prerequisites

 Knowledge and experience in JCL, TSO, DB2 Database Administration

Length of Seminar

 13 hours on 2 Days

Course

First Day - Theory Part

  • Bufferpool basics: definitions, types, sizes, thresholds, ditto Group Bufferpools,
  • The general idea of bufferpool roles: dedicated pools for frequently/infrequently used objects, randomly/sequentially accessed objects, large/small objects.
  • Importance of object separation into pools with particular roles that is along access pattern
  • Common key figures and their meaning: hitrate, getpage, page residency,
  • Real indicators for 'good' or 'bad' bufferpool behavior hence IO performance: Number of ReReads, ReRead Percentage, Number of Distinct GetPage Requests.
  • Measurement and calculation of ReReads and Distinct Getpage. How can I determine ReReads and Distinct Getpage using DB2 instruments? IFCIDs, Trace, Sort, Load, Queries.
  • What else is needed
  • to simplify the evaluation of bufferpool performance?
  • to identify tuning potential?
  • to determine tuning measures?
  • to recognize problematic advancement and to arrange for it?
  • to decide for tuning measures?

Second Day – Case Studies based on measurements that the attendees delivered

  • Inspection of the measurements and application of the lessons learned the first day.
  • Does the measurement reveal tuning potential? Hence is improvement of the io performance possible?
  • Reveals the measurement urgent problems?
  • Do the pools have proper sizes according to the present measurement?
  • Are the objects properly allocated to the pools, in the sense that randomly accessed objects are allocated to a pool playing that role of hosting randomly accessed objects? Analogous for sequentially accessed objects.
  • Are the thresholds adequately set?
  • Analogous for Group Bufferpools.
  • Development of Tuning Measures and Remedy of Defects
  • Correct Sizing of the Pools
  • Setting Thresholds properly
  • Shifting of misplaced objects
  • Ditto for Group Bufferpools
  • Implementing a robust ongoing evaluation procedure
  • to measure permanently and automatically
  • to run the evaluation routinely
  • to receive notification on particularities, problems and performance degradation.

Handout

 Seminar Script of about 70 pages text

Seminar Language

 German, English

Topics and Educational Objectives

DB2 for z/OS: Buffer Pool Setup

  • Purpose and goal of Bufferpool Tuning
  • Measurement, Analysis and Assessment
  • Ways to optimize
  • Grouping of Objects to Pools
  • Pool Sizing
  • Adjustment of Thresholds

Capturing of Measurement Data

  • IBM Utilities
  • SQL Queries
  • SMF Data
  • Trace Data
  • Market Review: IBM und Third Party Tools

Bufferpool Grouping Proposals

  • Sample Patterns: OLTP, ERP(SAP).

Analysis of Measurement Data

  • What do Statistic Traces provide?
  • What is to expect from common monitors? For example DB2PM, Mainview, Omegamon?
  • What offers RMF?
  • What is the current setting of the thresholds, are the settings adequate?

Best Practice

  • Measure - Analyse - Understand - Define Actions - ALTER - Verify - Keep the Systems tuned over time.
  • Criteria for ongoing checks.
  • Program based checks.

Fee

1,180 Euro, per attendee plus VAT, the fee for an inhouse seminar is depending on the number of attendees and is at least 4,500 Euro, plus VAT.