Chapter 3 Exercises: DB2 Architecture

These exercises range from foundational recall to advanced analysis. Work through them in order — later exercises build on concepts from earlier ones.

Section A: Architecture Identification (Exercises 1-7)

Exercise 1: z/OS Address Space Identification

For each of the following DB2 activities, identify which z/OS address space (MSTR, DBM1, DIST, IRLM) is primarily responsible:

Exercise 2: LUW Process Identification

For each of the following DB2 LUW activities, identify which process is primarily responsible:

Exercise 3: Architecture Diagram Labeling

Study the following incomplete diagram of a z/OS DB2 subsystem. Fill in the blanks with the correct component names.

z/OS LPAR
|
+-- DB2 Subsystem (SSID: DB2P)
    |
    +-- DB2P____  (Address Space A)
    |     - Command processing
    |     - Log management
    |     - Recovery coordination
    |
    +-- DB2P____  (Address Space B)
    |     - SQL compilation
    |     - Buffer pool management
    |     - _______ pool (caches compiled plans)
    |     - _______ pool (used for RID list processing)
    |
    +-- DB2P____  (Address Space C)
    |     - Remote client connections
    |     - _______ protocol
    |
    +-- ________  (Address Space D)
          - Lock management
          - _______ detection

Exercise 4: Platform Comparison Matrix

Complete the following comparison table between z/OS and LUW:

Exercise 5: Memory Hierarchy Ordering

Arrange the following LUW memory areas from broadest scope to narrowest scope:

• Application (private) memory
• Instance shared memory
• Sort heap (within a connection)
• Database shared memory
• Buffer pool (within a database)

Exercise 6: Tablespace Type Selection

For each of the following Meridian Bank scenarios, recommend the appropriate tablespace type on z/OS. Choose from: PBR UTS, PBG UTS. Justify your choice.

a. The TRANSACTION table, which currently has 50 million rows and grows by 200,000 rows per day. Most queries filter by transaction date.

b. The BRANCH table, which has 250 rows and changes infrequently.

c. A TRANSACTION_ARCHIVE table that holds 5 years of historical transactions (over 300 million rows), queried only for regulatory audits.

d. A SESSION_LOG table used by the web application to track active user sessions. Row count fluctuates between 100 and 50,000 throughout the day.

Exercise 7: Buffer Pool Page Size Selection

For each table, recommend the appropriate page size (4K, 8K, 16K, 32K) and briefly explain why:

a. CUSTOMER table: CUSTOMER_ID (INTEGER), FIRST_NAME (VARCHAR(50)), LAST_NAME (VARCHAR(50)), EMAIL (VARCHAR(100)), PHONE (VARCHAR(20)), ADDRESS (VARCHAR(200)), CITY (VARCHAR(50)), STATE (CHAR(2)), ZIP (VARCHAR(10)), DATE_OF_BIRTH (DATE), SSN_ENCRYPTED (CHAR(64)). Average row size: ~400 bytes.

b. TRANSACTION table: TRANS_ID (BIGINT), ACCOUNT_ID (INTEGER), TRANS_TYPE (CHAR(2)), AMOUNT (DECIMAL(15,2)), TRANS_DATE (TIMESTAMP), DESCRIPTION (VARCHAR(100)), REFERENCE_NUM (VARCHAR(30)). Average row size: ~200 bytes.

c. DOCUMENT table: DOC_ID (INTEGER), CUSTOMER_ID (INTEGER), DOC_TYPE (VARCHAR(20)), DOC_CONTENT (CLOB(1M)), CREATED_DATE (TIMESTAMP). The CLOB column averages 50 KB.

d. BRANCH table: BRANCH_ID (INTEGER), BRANCH_NAME (VARCHAR(100)), ADDRESS (VARCHAR(200)), MANAGER_ID (INTEGER), PHONE (VARCHAR(20)). Average row size: ~250 bytes. Only 250 rows.

Section B: Buffer Pool Analysis (Exercises 8-14)

Exercise 8: Buffer Pool Hit Ratio Calculation

A DB2 system reports the following buffer pool statistics for a 24-hour period:

a. Calculate the overall buffer pool hit ratio. b. Calculate the synchronous hit ratio (excluding prefetch reads). c. Which metric is more relevant for OLTP response time, and why?

Exercise 9: Buffer Pool Sizing

Meridian Bank's CUSTOMER table has the following characteristics:

• 500,000 rows
• Average row size: 400 bytes
• Page size: 8 KB (8,192 bytes)
• Usable space per page: approximately 7,800 bytes (after page header and overhead)
• Rows per page: approximately 19

a. How many data pages does the CUSTOMER table occupy? b. If Meridian wants to cache the ENTIRE CUSTOMER table in the buffer pool, how much memory is needed? c. The CUSTOMER table also has three indexes totaling 8,000 index pages (4K pages). How much additional buffer pool memory is needed for indexes? d. Is caching the entire table realistic? What is the tradeoff?

Exercise 10: Multiple Buffer Pool Design

You are the DBA for Meridian Bank. Currently, all tables and indexes share a single buffer pool (BP0, 4K, 500 MB). Performance monitoring shows:

• Customer lookup queries (CUSTOMER, ACCOUNT tables): 99.5% hit ratio
• End-of-day batch reporting (TRANSACTION table full scans): 45% hit ratio
• After batch reporting starts, customer lookup hit ratio drops to 82%

a. Explain what is happening and why customer lookup performance degrades during batch. b. Propose a buffer pool redesign with specific pool names, page sizes, and approximate sizes. c. What is the expected improvement?

Exercise 11: Buffer Pool Hit Ratio Targets

Explain why a buffer pool hit ratio of 80% might be acceptable for a data warehouse workload but unacceptable for an OLTP workload. In your answer, address:

a. The difference in access patterns (random vs. sequential). b. The impact on individual query response time. c. The impact on throughput (number of transactions per second).

Exercise 12: Page Size Tradeoffs

A colleague suggests using 32K pages for all tablespaces in Meridian Bank to "maximize I/O efficiency." Write a response explaining why this is not a good idea. Address at least three specific drawbacks.

Exercise 13: Prefetch and Buffer Pool Interaction

Explain the difference between synchronous I/O and asynchronous prefetch I/O in the context of buffer pools. Then answer:

a. When does DB2 trigger sequential prefetch? b. When does DB2 trigger list prefetch? c. How does prefetch interact with the LRU (Least Recently Used) replacement algorithm? Why might prefetched pages be treated differently from demand-read pages?

Exercise 14: STMM Analysis

Meridian Bank's LUW system has STMM enabled. The following memory allocations were observed at three different times:

a. Explain the shift from 8 AM to 12 PM in terms of workload characteristics. b. Explain the shift from 12 PM to 11 PM. c. Why did the total database memory increase by 11 PM? d. Is STMM making good decisions here? How would you verify?

Section C: Logging and Recovery (Exercises 15-19)

Exercise 15: Write-Ahead Logging Walkthrough

Consider the following sequence of events:

1. Application issues: UPDATE ACCOUNT SET BALANCE = BALANCE - 500 WHERE ACCOUNT_ID = 1001;
2. DB2 modifies the data page in the buffer pool and writes a log record to the log buffer.
3. Application issues: COMMIT;
4. DB2 forces the log buffer to the active log on disk.
5. DB2 returns "commit successful" to the application.
6. (Some time later) The page cleaner writes the dirty data page to disk.

Questions: a. At step 5, has the modified data page been written to disk? Why or why not? b. If DB2 crashes between steps 5 and 6, is the update lost? Explain. c. If DB2 crashes between steps 2 and 3, what happens to the update? d. What would happen if DB2 wrote the data page to disk BEFORE writing the log record (violating WAL)?

Exercise 16: Log Sizing Calculation

Meridian Bank processes approximately 200,000 transactions per day. Each transaction generates an average of 500 bytes of log data (including the update log record, commit record, and overhead). The bank requires the ability to recover from the last 7 days of archive logs.

a. Calculate the daily log volume. b. Calculate the weekly archive log storage requirement. c. If each active log data set (z/OS) or log file (LUW) is 40 MB, how many are needed to hold 2 hours of peak-period log data? (Assume peak volume is 3x average.) d. Why is it important to size active logs to hold several hours of data?

Exercise 17: BSDS and Recovery

Explain the role of the BSDS (Bootstrap Data Set) on z/OS. Then answer:

a. What information does the BSDS contain? b. Why is the BSDS duplexed (two copies)? c. What happens if BOTH copies of the BSDS are lost? d. Does DB2 LUW have an equivalent to the BSDS? If so, what is it?

Exercise 18: Circular vs. Archive Logging

Meridian Bank's development team has set up a new LUW test database using the default configuration (circular logging). A developer accidentally runs a DELETE without a WHERE clause on the CUSTOMER table. They want to recover the data.

a. Can they recover using ROLLFORWARD DATABASE? Why or why not? b. What recovery options do they have with circular logging? c. What change would you recommend for the test database to prevent this situation? d. Even with archive logging, what additional step is required before ROLLFORWARD can be used?

Exercise 19: Checkpoint and Recovery Time

Explain the relationship between checkpoint frequency and recovery time. Specifically:

a. What happens during a DB2 restart after a crash? b. Why does the checkpoint position matter for recovery? c. If checkpoints occur every 500,000 log records on z/OS, and your system writes 10,000 log records per second at peak, approximately how far back might DB2 need to process the log during recovery? d. What is the tradeoff of more frequent checkpoints?

Section D: SQL Lifecycle and Catalog (Exercises 20-25)

Exercise 20: SQL Lifecycle Ordering

Place the following steps in the correct order for processing a dynamic SQL statement:

• [ ] Optimizer generates access plan
• [ ] Agent/thread is assigned to the connection
• [ ] Data pages are read from buffer pool
• [ ] Authorization check against catalog
• [ ] Result set returned to application
• [ ] Syntax checking (parse)
• [ ] Lock acquired on the data resource
• [ ] Application connects to DB2
• [ ] Semantic check (do objects exist?)
• [ ] Check dynamic statement cache for existing plan

Exercise 21: Catalog Queries

Write SQL queries against the DB2 catalog to answer the following questions for the Meridian Bank schema. Provide both z/OS and LUW versions.

a. List all tables in the MERIDIAN schema with their row counts (cardinality). b. Find all indexes on the ACCOUNT table, showing the index name and whether it is unique. c. Find the column names and data types for the TRANSACTION table.

SELECT NAME, TYPE, CARDF
  FROM SYSIBM.SYSTABLES
 WHERE CREATOR = 'MERIDIAN'
   AND TYPE = 'T'
 ORDER BY NAME;

SELECT TABNAME, TYPE, CARD
  FROM SYSCAT.TABLES
 WHERE TABSCHEMA = 'MERIDIAN'
   AND TYPE = 'T'
 ORDER BY TABNAME;

SELECT NAME, UNIQUERULE, COLCOUNT
  FROM SYSIBM.SYSINDEXES
 WHERE TBCREATOR = 'MERIDIAN'
   AND TBNAME = 'ACCOUNT';

SELECT INDNAME, UNIQUERULE, COLCOUNT
  FROM SYSCAT.INDEXES
 WHERE TABSCHEMA = 'MERIDIAN'
   AND TABNAME = 'ACCOUNT';

SELECT NAME, COLTYPE, LENGTH, SCALE, NULLS
  FROM SYSIBM.SYSCOLUMNS
 WHERE TBCREATOR = 'MERIDIAN'
   AND TBNAME = 'TRANSACTION'
 ORDER BY COLNO;

SELECT COLNAME, TYPENAME, LENGTH, SCALE, NULLS
  FROM SYSCAT.COLUMNS
 WHERE TABSCHEMA = 'MERIDIAN'
   AND TABNAME = 'TRANSACTION'
 ORDER BY COLNO;

Exercise 22: Static vs. Dynamic SQL

Explain the difference between static and dynamic SQL in the context of DB2's architecture. Address:

a. When is the access plan created for each type? b. Where is the plan stored? (Differentiate z/OS and LUW.) c. What are the performance implications of each? d. Which type is more common in modern Java applications using JDBC? Why?

Exercise 23: RUNSTATS Impact

Meridian Bank's DBA forgot to run RUNSTATS on the TRANSACTION table for three months. During that time, the table grew from 30 million to 50 million rows, and a new index was added. What are the likely consequences? Discuss:

a. Impact on the optimizer's access path choices. b. Specific scenarios where the optimizer would make poor decisions. c. How to fix the situation.

Exercise 24: Access Path Analysis

For the following query on the Meridian Bank TRANSACTION table:

SELECT TRANS_ID, AMOUNT, TRANS_DATE
  FROM TRANSACTION
 WHERE ACCOUNT_ID = 5001
   AND TRANS_DATE BETWEEN '2025-01-01' AND '2025-03-31'
 ORDER BY TRANS_DATE;

Assume the following indexes exist: - IX_TRANS_PK on TRANS_ID (unique) - IX_TRANS_ACCT on ACCOUNT_ID - IX_TRANS_DATE on TRANS_DATE - IX_TRANS_ACCT_DATE on (ACCOUNT_ID, TRANS_DATE)

a. Which index would the optimizer most likely choose? Why? b. Trace the data access steps using that index. c. Does DB2 need to perform a sort for the ORDER BY clause? Explain.

Exercise 25: Catalog Exploration Challenge

Using only catalog queries (no application-level queries), determine the following for a DB2 database. Write the SQL for either z/OS or LUW (your choice):

a. The total number of tables in the database. b. The five largest tables by row count. c. All tables that have NO indexes defined. d. All indexes that are not unique. e. The total number of columns across all tables in the MERIDIAN schema.

SELECT COUNT(*) AS TOTAL_TABLES
  FROM SYSCAT.TABLES
 WHERE TYPE = 'T'
   AND TABSCHEMA NOT LIKE 'SYS%';

SELECT TABSCHEMA, TABNAME, CARD
  FROM SYSCAT.TABLES
 WHERE TYPE = 'T'
   AND CARD > 0
 ORDER BY CARD DESC
 FETCH FIRST 5 ROWS ONLY;

SELECT T.TABSCHEMA, T.TABNAME
  FROM SYSCAT.TABLES T
 WHERE T.TYPE = 'T'
   AND T.TABSCHEMA = 'MERIDIAN'
   AND NOT EXISTS (
     SELECT 1 FROM SYSCAT.INDEXES I
      WHERE I.TABSCHEMA = T.TABSCHEMA
        AND I.TABNAME = T.TABNAME
   );

SELECT INDSCHEMA, INDNAME, TABNAME, UNIQUERULE
  FROM SYSCAT.INDEXES
 WHERE TABSCHEMA = 'MERIDIAN'
   AND UNIQUERULE = 'D';  -- D = Duplicates allowed (non-unique)

SELECT COUNT(*) AS TOTAL_COLUMNS
  FROM SYSCAT.COLUMNS
 WHERE TABSCHEMA = 'MERIDIAN';

Section E: Architecture Design and Analysis (Exercises 26-30)

Exercise 26: Full Architecture Design

You are tasked with designing a DB2 LUW architecture for a new Meridian Bank branch-operations database. The requirements are:

• 10 tables, largest will grow to 20 million rows
• 50 concurrent users during business hours
• OLTP workload with occasional reports
• Server has 32 GB RAM, 8 cores
• Must support point-in-time recovery

Provide: a. Instance and database names. b. Buffer pool configuration (names, page sizes, sizes). c. Tablespace layout. d. Logging configuration (type, sizing). e. Key database configuration parameters.

Exercise 27: Failure Analysis

For each of the following failure scenarios in a z/OS DB2 environment, describe what happens and how DB2 recovers:

a. The IRLM address space ABENDs. b. A single active log data set becomes unavailable (with dual logging enabled). c. DBM1 ABENDs but MSTR remains running. d. The entire z/OS LPAR crashes and is IPLed. e. A VSAM data set underlying a tablespace gets a media error.

Exercise 28: Cross-Platform Migration Analysis

Meridian Bank is considering migrating its core banking application from DB2 for z/OS to DB2 for LUW (or running on both). For each architectural feature, describe what changes would be needed:

a. Buffer pool configuration b. Stored procedure execution environment c. Logging and recovery setup d. Connection management for application servers e. Catalog queries used by the monitoring team

Exercise 29: Performance Troubleshooting Scenario

Meridian Bank's online banking application suddenly shows response time degradation. The average customer-lookup query went from 2ms to 45ms. You check the buffer pool statistics and find:

• Buffer pool hit ratio for BP_CUST: dropped from 99.2% to 76%
• Buffer pool hit ratio for BP_TRANS: 99.8% (unchanged)
• No change in application code or query patterns
• Physical I/O rate has tripled

Walk through your investigation: a. What is the most likely cause based on the symptoms? b. What specific metrics would you check next? c. Name three possible root causes. d. For each root cause, describe the fix.

Exercise 30: Comprehensive Architecture Diagram

Draw (or describe in text) a complete architecture diagram for Meridian Bank on DB2 LUW that includes ALL of the following components. For each, indicate the approximate memory or storage size.

• DB2 instance and database
• All buffer pools (with tablespace assignments)
• Package cache and catalog cache
• Sort heap
• Lock list
• Active log files
• Archive log destination
• Storage paths for automatic storage
• Application connections (50 OLTP users, 2 batch jobs)
• Fenced mode processes for stored procedures

Server: mbnk-prod-db01 (64 GB RAM, 16 cores, Linux)
|
+-- DB2 Instance: db2mbnk
|   |
|   +-- Instance Shared Memory (~200 MB)
|   |     - FCM buffers, monitor heap
|   |
|   +-- db2sysc (system controller)
|   +-- db2tcpcm (TCP listener, port 50000)
|   +-- db2ipccm (local IPC)
|   |
|   +-- Database: MERIDIANDB
|   |   |
|   |   +-- Database Shared Memory (~12 GB total, STMM managed)
|   |   |   |
|   |   |   +-- Buffer Pools:
|   |   |   |     BP_CUSTACCT (8K, ~4 GB) --> TS_CUSTOMER, TS_ACCOUNT
|   |   |   |     BP_TRANS    (8K, ~3 GB) --> TS_TRANSACTION
|   |   |   |     BP_INDEX    (4K, ~2 GB) --> TS_INDEX_CUST, TS_INDEX_TRANS
|   |   |   |     BP_TEMP     (32K, ~500 MB) --> TEMPSPACE1
|   |   |   |     IBMDEFAULTBP (4K, ~200 MB) --> SYSCATSPACE, TS_REFERENCE
|   |   |   |
|   |   |   +-- Package Cache: ~500 MB (AUTOMATIC)
|   |   |   +-- Catalog Cache: ~200 MB (AUTOMATIC)
|   |   |   +-- Lock List: ~300 MB (AUTOMATIC)
|   |   |   +-- Shared Sort Heap: ~1 GB threshold
|   |   |
|   |   +-- Active Logs: /db2logs/
|   |   |     25 primary files x 40 MB = 1 GB
|   |   |     15 secondary files x 40 MB = 600 MB (if needed)
|   |   |
|   |   +-- Archive Logs: /db2archlog/
|   |   |     ~100 MB/day, retained 30 days = ~3 GB
|   |   |
|   |   +-- Automatic Storage Paths:
|   |         /db2data/path1 (SAN, 500 GB)
|   |         /db2data/path2 (SAN, 500 GB)
|   |
|   +-- Agents:
|   |     50 x db2agent (OLTP users, ~50 MB each private memory)
|   |     2 x db2agent (batch jobs, ~200 MB each for large sorts)
|   |
|   +-- Prefetchers: 6 x db2pfchr
|   +-- Page Cleaners: 4 x db2pclnr
|   +-- Logger: db2loggr
|   +-- Deadlock Detector: db2dlock
|   +-- Fenced Mode: 3 x db2fmp (for stored procedures)
|
+-- Operating System and Other:
      ~4 GB for OS
      ~4 GB for file system cache
      Remaining: headroom for spikes

Section F: Reflection and Synthesis (Exercises 31-35)

Exercise 31: Architecture Metaphor

The chapter compared DB2 to a hospital with separate departments. Create your own metaphor for DB2's architecture using a different real-world system (a factory, a restaurant, a city, etc.). Map at least five DB2 components to their equivalents in your metaphor. Explain why each mapping works.

(No single correct answer — evaluate based on accuracy of mappings and clarity of explanation.)

Exercise 32: Design Tradeoff Analysis

For each pair of design choices, explain the tradeoff and recommend a choice for Meridian Bank:

a. One large buffer pool vs. multiple specialized buffer pools. b. Fenced vs. unfenced stored procedures. c. Circular logging vs. archive logging. d. 4K page size vs. 8K page size for the TRANSACTION table. e. DPF (multi-partition) vs. single-partition for the core banking database.

Exercise 33: Historical Perspective

Research question: DB2 for z/OS was originally designed in the early 1980s. Explain how at least two architectural decisions were influenced by the hardware constraints of that era (e.g., limited memory, slow disk, expensive CPU). How have those decisions aged?

Exercise 34: Monitoring Plan

Create a monitoring plan for Meridian Bank that specifies which metrics to check for each architectural component. For each metric, define a threshold that should trigger investigation.

Exercise 35: Architecture Presentation

Prepare a 5-minute presentation outline (bullet points) that explains DB2's architecture to a non-technical bank manager. Your goal is to convince them that proper architecture investment (memory, storage, monitoring) is essential for the bank's operations. Use analogies, avoid jargon, and focus on business impact (response time, reliability, recovery).

(This exercise develops communication skills essential for DBAs who must justify infrastructure investments.)