Chapter 25 Exercises: Buffer Pool and Memory Tuning

Instructions: These exercises progress from buffer pool concepts through hands-on configuration and monitoring. Some exercises require access to a DB2 instance (LUW or z/OS); where that is not available, analyze the provided monitoring output and propose configurations based on the data.

Section A: Buffer Pool Fundamentals (Exercises 1-7)

Exercise 1 — Hit Ratio Calculation

A buffer pool processed 5,000,000 logical reads (getpages) and 75,000 physical reads during a one-hour monitoring interval. Calculate:

1. The hit ratio as a percentage.
2. The number of buffer pool hits.
3. If the average physical read takes 4 ms, the total I/O wait time during that hour.
4. If the buffer pool were doubled in size and the hit ratio improved to 99.5%, what would the new total I/O wait time be? How much time was saved?

Exercise 2 — Page Size Impact

You are designing buffer pools for a new database on LUW with 32 GB of memory available for buffer pools. The database has three types of tables:

For each table type: 1. Calculate the total data pages at 4 KB and 8 KB page sizes. 2. Recommend a page size and justify your choice. 3. Calculate the total buffer pool memory needed to cache 100% of each table type at your recommended page size.

Exercise 3 — Random vs. Sequential I/O

Explain the difference between synchronous reads and prefetch reads. For each of the following queries against Meridian Bank's schema, predict whether the predominant I/O pattern would be random or sequential, and explain why:

1. SELECT * FROM CUSTOMERS WHERE customer_id = 12345
2. SELECT * FROM TRANSACTIONS WHERE txn_date >= '2025-01-01' ORDER BY txn_date
3. SELECT c.customer_name FROM CUSTOMERS c WHERE EXISTS (SELECT 1 FROM ACCOUNTS a WHERE a.customer_id = c.customer_id AND a.current_balance > 100000)
4. A full RUNSTATS collection on the TRANSACTIONS table.
5. INSERT INTO AUDIT_LOG (event_id, event_time, ...) VALUES (?, CURRENT TIMESTAMP, ...)

Exercise 4 — LRU and Page Stealing

A buffer pool has 10,000 pages. The following events occur in sequence:

1. Application A reads 3,000 unique data pages (random OLTP access).
2. Application B begins a sequential scan that reads 12,000 unique pages.
3. Application A re-reads the same 3,000 pages it read in step 1.

Answer: 1. Without any VPSEQT protection, what happens to Application A's pages during Application B's scan? 2. What hit ratio does Application A experience in step 3? 3. How would setting VPSEQT to 30% change the outcome? Calculate the new hit ratio for Application A in step 3. 4. What VPSEQT value would you recommend for this pool if Application A's response time is business-critical?

Exercise 5 — Diminishing Returns

You have the following data from buffer pool simulation experiments:

1. For each size increase, calculate the number of physical reads saved per additional 10,000 pages of memory.
2. Identify the point of diminishing returns (where additional memory provides significantly less benefit).
3. If each physical read costs 4 ms and each 10,000 pages costs 40 MB of memory, what is the response time savings per MB for each size increment?
4. Recommend the optimal buffer pool size and justify your choice.

Exercise 6 — Synchronous Writes

Explain what a synchronous write is and why it is harmful to performance. Then answer:

1. What z/OS parameter controls when deferred (asynchronous) writes begin?
2. If you observe 500 synchronous writes per hour in a pool with DWQT set to 50%, what two changes would you consider?
3. On LUW, what configuration parameter controls the number of background threads that write dirty pages? What is a reasonable value for a server with 16 CPU cores?
4. Why might a synchronous write problem appear suddenly in a pool that has been running fine for months?

Exercise 7 — Buffer Pool Hit Ratio Misconceptions

A junior DBA reports: "All our buffer pools have a 99%+ hit ratio, so memory tuning is complete." Describe three scenarios where a 99% hit ratio could still indicate a performance problem. For each scenario, explain what additional metric you would check.

Section B: z/OS Buffer Pool Configuration (Exercises 8-12)

Exercise 8 — Buffer Pool Parameter Setting

Write the -ALTER BUFFERPOOL commands to configure the following pools on z/OS:

1. BP1: OLTP data pool. 250,000 buffers. Protect against sequential scans (limit sequential to 15%). Begin deferred writes at 30% dirty. Per-dataset write threshold at 10%. Page-fix in real memory.
2. BP2: Index pool. 100,000 buffers. No sequential tolerance (VPSEQT 0%). LRU page stealing. Page-fixed.
3. BP3: Batch pool. 150,000 buffers. Allow 95% sequential. FIFO page stealing. Begin deferred writes at 45%.
4. BP5: Reference table pool. 3,000 buffers. No page stealing (entire tables must always be cached). Page-fixed.
5. BP32K0: Temp pool for sort work. 8,000 32KB buffers. 90% sequential threshold.

Exercise 9 — DISPLAY BUFFERPOOL Analysis

Analyze the following -DISPLAY BUFFERPOOL output and identify all problems:

BUFFERPOOL NAME BP1, BUFFERPOOL ID 1
VP CURRENT SIZE = 30000 BUFFERS

GETPAGE REQUESTS =    85,432,100
PAGES READ       =     8,213,456
HIT RATIO        =         90.39%

SYNC READ I/O    =     6,543,210
SEQ PREFETCH REQ =       234,567
PAGES PREFETCHED =     1,670,246

SYNC WRITES      =        45,678
DEFERRED WRITES  =       892,345

VPSEQT REACHED   =       123,456
DWQT REACHED     =        89,012

For each issue identified: 1. Calculate the relevant ratio or metric. 2. Explain why it is a problem. 3. Recommend a specific configuration change with the -ALTER BUFFERPOOL command.

Exercise 10 — Hiperpool Migration

Your z/OS LPAR has 96 GB of real memory. DB2 currently uses 12 GB for virtual buffer pools plus 4 GB of hiperpool across five pools. The system is running DB2 13 on z/OS 2.5. Create a migration plan:

1. Should you disable hiperpool? Justify your answer.
2. For each pool, calculate the new VPSIZE after absorbing the hiperpool allocation.
3. Write the ALTER BUFFERPOOL commands.
4. What monitoring would you perform after the migration?
5. What is the rollback plan?

Exercise 11 — EDM Pool Sizing

Meridian Bank's z/OS DB2 subsystem has: - 1,200 unique bound packages, averaging 60 KB each - 200 databases with DBDs averaging 250 KB each - Peak concurrent package usage: 600 packages - 15,000 unique dynamic SQL statements in the workload

Calculate: 1. The minimum EDM_SKELETON_POOL size (include 30% overhead for fragmentation). 2. The minimum EDMDBDC size (include 30% overhead). 3. The recommended EDMSTMTC size, assuming an average cached statement is 8 KB. 4. What symptoms would indicate each component is undersized?

Exercise 12 — RID Pool and Sort Pool

The z/OS DB2 statistics for a one-hour interval show:

RID pool failures (QXRIDPFL):     47
RID pool high water mark:          3,800 KB (of 4,096 KB allocated)
Sort pool overflows to work file:  89
Total sorts:                       4,500
Average sort size:                 1.2 MB

1. Are these metrics acceptable? State the target values.
2. Calculate the current sort overflow percentage.
3. Recommend new sizes for both the RID pool (MAXRBLK) and sort pool (SRTPOOL).
4. Explain why a RID pool failure is especially harmful — what access path change does it cause?

Section C: LUW Buffer Pool Management (Exercises 13-18)

Exercise 13 — Buffer Pool Creation

Write the SQL statements to create the complete buffer pool configuration for a new LUW database:

1. Default pool: 4 KB, 25,000 pages (catalog only).
2. OLTP data pool: 8 KB, initial 500,000 pages, fixed size.
3. OLTP index pool: 8 KB, initial 200,000 pages, fixed size.
4. Batch data pool: 8 KB, STMM-managed, with block-based I/O (30% block reservation, blocksize 32).
5. Temporary pool: 32 KB, STMM-managed.
6. LOB pool: 32 KB, initial 5,000 pages, fixed size.

For each pool, explain whether you chose fixed or AUTOMATIC sizing and why.

Exercise 14 — Tablespace-to-Buffer-Pool Assignment

Given the buffer pools from Exercise 13, write the CREATE TABLESPACE or ALTER TABLESPACE statements to assign Meridian's key objects:

• CUSTOMERS table (hot OLTP, 500K rows, 80-byte rows)
• ACCOUNTS table (hot OLTP, 2M rows, 200-byte rows)
• TRANSACTIONS table (mixed OLTP + batch, 50M rows, 300-byte rows)
• AUDIT_LOG table (write-heavy, rarely read, 100M rows, 500-byte rows)
• Temp table space for sorts
• LOB storage for document images

Specify the page size, extent size, and prefetch size for each table space.

Exercise 15 — Memory Configuration Parameters

Write the UPDATE DBM CFG and UPDATE DB CFG commands to configure Meridian's LUW instance:

• 64 GB server, dedicated to DB2
• STMM enabled for all tunable consumers
• Instance memory at 75% of physical RAM
• Database memory: AUTOMATIC
• Sort heap: AUTOMATIC, with shared threshold at 4 GB
• Package cache: AUTOMATIC
• 12 page cleaners, 16 I/O servers
• Log buffer: 2048 pages

Calculate the exact values in 4 KB pages where the parameter requires it.

Exercise 16 — STMM Monitoring

Write the SQL queries to answer each of the following questions:

1. What is the current size of each buffer pool, in MB, and is each STMM-managed?
2. What is the current DATABASE_MEMORY allocation?
3. What is the sort overflow rate over the lifetime of the database?
4. What is the package cache hit ratio?
5. Has STMM recently increased or decreased any buffer pool? (Hint: compare current size to configured size.)

Exercise 17 — Block I/O Configuration

A batch reporting workload scans three large tables (500K, 2M, and 10M pages) with mostly sequential access. Design the buffer pool:

1. Calculate the total buffer pool size (you cannot cache all three tables; size for throughput, not caching).
2. Set NUMBLOCKPAGES and BLOCKSIZE. Explain how BLOCKSIZE relates to the table space EXTENTSIZE.
3. What percentage of the pool should be reserved for block I/O? Why?
4. Write the CREATE BUFFERPOOL statement.

Exercise 18 — Memory Audit Script

Write a comprehensive SQL script for LUW that produces a "memory health report" showing:

1. Total buffer pool memory allocated (sum across all pools).
2. Per-pool: name, page size, current size in MB, data hit ratio, index hit ratio.
3. Sort heap configuration and current overflow rate.
4. Package cache size and hit ratio.
5. Any buffer pool with a data hit ratio below 90% (flagged as "INVESTIGATE").
6. Any sort overflow rate above 3% (flagged as "INVESTIGATE").

Section D: Monitoring and Analysis (Exercises 19-24)

Exercise 19 — Table-Level I/O Analysis

Write a query using MON_GET_TABLE that identifies the top 20 tables by total rows read. For each table, show:

• Schema and table name
• Total rows read and rows written
• The table space name and its buffer pool assignment
• Whether the table is a candidate for a dedicated buffer pool (flag tables with > 1M rows read)

Exercise 20 — z/OS Monitoring Procedure

Write a z/OS monitoring procedure (using DB2 commands and SQL) that:

1. Displays all active buffer pool hit ratios.
2. Identifies any pool with a hit ratio below 95%.
3. Shows the top 5 table spaces by getpage count from SYSIBM.SYSTABLESPACESTATS.
4. Checks for synchronous write activity (indicates buffer pool pressure).
5. Checks the dynamic statement cache hit ratio from the statistics trace.

Exercise 21 — Correlation Analysis

You observe the following during a performance investigation on a system with separate OLTP and batch buffer pools:

1. What is happening to the OLTP buffer pool during the batch job, even though they are in separate pools?
2. At what phase does the problem become critical?
3. Propose three specific solutions, ordered from simplest to most complex.
4. What additional monitoring data would you collect to confirm your diagnosis?

Exercise 22 — Memory Pressure Investigation

A LUW database administrator reports that buffer pool hit ratios have been declining over the past three weeks:

The buffer pool size has not changed (500,000 8 KB pages = ~3.8 GB).

1. What is the most likely cause?
2. Calculate the working set growth implied by the hit ratio decline.
3. Recommend a new buffer pool size to restore a 98%+ hit ratio.
4. What longer-term strategy would prevent this from recurring?

Exercise 23 — Multi-Workload Buffer Pool Design

Design a complete buffer pool layout for a database that supports three concurrent workloads on a z/OS system with 64 GB available for DB2:

1. OLTP: 3,000 concurrent users, sub-second response required, 20 hot tables, 500,000 total data pages, 150,000 total index pages.
2. Nightly batch: 2 AM - 5 AM, full scans of 5 tables totaling 8,000,000 pages.
3. Ad-hoc reporting: 8 AM - 5 PM, complex queries by 50 analysts, moderate sort activity.

Specify for each buffer pool: - Pool name, purpose, and assigned objects - VPSIZE (with calculation) - VPSEQT, DWQT, VDWQT - PGSTEAL algorithm - PGFIX setting

Also specify: EDM pool size, RID pool size, and sort pool size.

Exercise 24 — Capacity Planning

Meridian Bank expects 30% growth in transaction volume over the next 12 months. Current buffer pool utilization on z/OS:

1. Which pools will need more memory first? Calculate when each pool's working set will exceed its VPSIZE at 30% annual growth.
2. Estimate the total additional buffer pool memory required in 12 months.
3. At what point will the current 128 GB LPAR need a memory upgrade?
4. Design a quarterly review process with specific metrics and thresholds that trigger capacity actions.

Section E: Integration Challenge (Exercise 25)

Exercise 25 — Complete System Tuning

You inherit a DB2 LUW system with these characteristics:

• Server: 128 GB RAM, 32 cores
• Single database, IBMDEFAULTBP only (4 KB pages), SIZE 2,000,000 (~7.8 GB)
• STMM: OFF
• DATABASE_MEMORY: 10,000,000 pages (~38 GB, but only 7.8 GB in buffer pools)
• SORTHEAP: 256 pages (1 MB)
• PCKCACHESZ: 5,000 pages (~20 MB)
• Hit ratio: 82%
• Sort overflow rate: 22%
• Package cache hit ratio: 71%
• Average response time: 85 ms (target: < 20 ms)

Design a complete memory reconfiguration:

1. How many buffer pools should you create? Name them and specify their purpose.
2. What page size(s) will you use?
3. Size each buffer pool (in pages and MB).
4. Set all relevant configuration parameters (DATABASE_MEMORY, SORTHEAP, SHEAPTHRES_SHR, PCKCACHESZ, etc.).
5. Enable STMM selectively — which consumers should be AUTOMATIC and which fixed?
6. Write the complete set of DDL and configuration commands.
7. Define the monitoring plan for the first week after implementation.
8. Predict the post-tuning hit ratios and response time improvement.