Chapter 3: DB2 Architecture — What's Running When DB2 Is Running

You type SELECT * FROM CUSTOMER and press Enter. In the next three milliseconds, your request will pass through at least six distinct software components, cross multiple memory pools, possibly traverse a network layer, acquire locks, read from a buffer pool or physical disk, and return a neatly formatted result set. You will never see any of this. But if you want to be more than a casual user of DB2 — if you want to tune it, troubleshoot it, or architect systems that depend on it — you need to understand every one of those steps. This chapter takes the lid off the machine.

Learning Objectives

After completing this chapter, you will be able to:

1. Describe the z/OS DB2 address space architecture (DBM1, MSTR, DIST, IRLM) and explain the role of each component.
2. Describe the LUW DB2 instance architecture (instance, database, database partition) and identify the key processes.
3. Explain buffer pools, EDM pool, and memory management on both platforms, and articulate why buffer pools are the single most important performance lever.
4. Trace the complete lifecycle of a SQL statement from application submission to result-set return.
5. Understand the logging and recovery architecture on both platforms, including write-ahead logging, the BSDS, and log archiving.
6. Map the Meridian National Bank system to concrete DB2 architectural components, making architecture tangible.

3.1 The Big Picture — DB2 as an Ecosystem of Components

When we say "DB2 is running," we are really saying that a collection of cooperating software components are running, each with a distinct responsibility. DB2 is not a single monolithic program. It is an ecosystem — a set of address spaces (on z/OS) or processes (on LUW) that communicate through shared memory, inter-process communication, and well-defined protocols.

This design is intentional. By separating concerns — the database engine from the lock manager, the system services from the distributed access facility — IBM achieves:

• Isolation: A failure in one component does not necessarily bring down the others.
• Scalability: Components can be tuned, sized, and in some cases restarted independently.
• Security: Different components can run with different authority levels.
• Serviceability: IBM can deliver fixes to one component without replacing the others.

Think of it like a hospital. There is an emergency department, a pharmacy, a radiology suite, an operating theater, and an administrative office. Each has its own staff, its own space, and its own procedures. They cooperate to treat patients, but they are not one undifferentiated mass. DB2 works the same way.

Before we dive into platform-specific details, here is the conceptual model that applies to both z/OS and LUW:

+------------------------------------------------------------------+
|                        APPLICATION                                |
|   (Your program, SPUFI, CLP, JDBC client, etc.)                  |
+------------------------------|-----------------------------------+
                               |  SQL request
                               v
+------------------------------------------------------------------+
|                     CONNECTION MANAGEMENT                         |
|   (Accepts connections, authenticates, routes work)               |
+------------------------------|-----------------------------------+
                               |
                               v
+------------------------------------------------------------------+
|                       SQL ENGINE                                  |
|   (Parses SQL, optimizes, builds access plans)                    |
+------------------------------|-----------------------------------+
                               |  Data requests
                               v
+------------------------------------------------------------------+
|                     BUFFER MANAGER                                |
|   (Buffer pools — the in-memory cache of data and index pages)    |
+------------------------------|-----------------------------------+
                               |  Read/Write if not in memory
                               v
+------------------------------------------------------------------+
|                     STORAGE MANAGER                               |
|   (Tablespaces, data sets, containers, physical I/O)              |
+------------------------------|-----------------------------------+
                               |
                               v
+------------------------------------------------------------------+
|                        DISK / STORAGE                             |
|   (DASD, SAN, SSD, cloud storage)                                |
+------------------------------------------------------------------+

        PARALLEL SUBSYSTEMS:
        - LOCK MANAGER (controls concurrent access)
        - LOG MANAGER  (records changes for recovery)
        - CATALOG      (stores metadata about everything)

Every SQL statement you run passes through this pipeline. The details differ dramatically between z/OS and LUW, but the conceptual flow is identical. Keep this diagram in your mind as we explore each platform in depth.

3.2 [z/OS] DB2 Address Spaces

On z/OS, DB2 runs as a subsystem — a named, system-recognized entity identified by a four-character subsystem ID (SSID), such as DB2A or DBPD. When you "start DB2," the z/OS operating system creates several distinct address spaces, each with its own virtual storage, its own dispatching priority, and its own set of responsibilities.

3.2.1 The Major Address Spaces

Here is the architecture at a glance:

z/OS LPAR (Logical Partition)
|
+-- DB2 Subsystem (e.g., SSID = DB2P)
|   |
|   +-- ssnmMSTR  (System Services Address Space)
|   |     - Command processing
|   |     - Logging and recovery
|   |     - Checkpoint processing
|   |     - BSDS management
|   |
|   +-- ssnmDBM1  (Database Services Address Space)
|   |     - SQL compilation and optimization
|   |     - Buffer pool management
|   |     - Data access and manipulation
|   |     - EDM pool, RID pool, sort pool
|   |     - Prefetch engines
|   |
|   +-- ssnmDIST  (Distributed Data Facility - DDF)
|   |     - DRDA protocol handling
|   |     - TCP/IP connections from remote clients
|   |     - Distributed transaction coordination
|   |
|   +-- IRLM      (Internal Resource Lock Manager)
|   |     - Lock management for ALL DB2 subsystems
|   |     - Deadlock detection
|   |     - Lock escalation
|   |
|   +-- WLM-managed Address Spaces (optional)
|         - Stored procedures
|         - User-defined functions
|         - Java runtime environments

(In the naming convention, ssnm is the four-character subsystem name. So for a subsystem named DB2P, the address spaces would be DB2PMSTR, DB2PDBM1, and DB2PDIST.)

Let us examine each one.

3.2.2 MSTR — The System Services Address Space

MSTR is the first address space started and the last stopped. It is the administrative backbone. Its responsibilities include:

• Command processing: When an operator issues -DB2P START DATABASE, MSTR handles it.
• Logging: MSTR owns the log buffers and writes log records to the active log data sets. Every change made to any DB2 object is recorded here first (write-ahead logging — we will cover this in Section 3.8).
• Recovery: If DB2 crashes and restarts, MSTR drives the restart recovery process, reading the log to redo committed work and undo uncommitted work.
• BSDS management: The Bootstrap Data Set is DB2's "table of contents" for its logs. MSTR reads and writes the BSDS.
• Checkpoint processing: Periodically, MSTR triggers a checkpoint — a synchronization point that limits the amount of log that must be processed during recovery.

MSTR is relatively modest in its memory requirements compared to DBM1, but it is absolutely critical. If MSTR fails, DB2 is down.

3.2.3 DBM1 — The Database Services Address Space

DBM1 is where the real work happens. This is the engine room. Its responsibilities include:

• SQL compilation: Parsing SQL statements, checking authorization, resolving object names against the catalog.
• Optimization: The DB2 optimizer lives here, evaluating access paths, choosing join strategies, estimating costs.
• Buffer pool management: All buffer pools reside in DBM1's address space (or in data spaces/hiperpools for very large pools). This is where data pages and index pages are cached in memory.
• EDM pool: The Environmental Descriptor Manager pool caches compiled SQL plans (packages and DBRMs), the catalog information needed to run them, and dynamic statement caches.
• RID pool: Used during certain access operations (RID list processing for non-matching index access and multi-index access).
• Sort pool: Used when DB2 must sort result sets (ORDER BY, GROUP BY, DISTINCT, merge joins).
• Prefetch management: DB2's sequential and list prefetch engines run here, reading ahead to anticipate data needs.
• Data manager: The component that actually reads rows from pages, applies predicates, and builds result sets.

DBM1 typically consumes the most virtual storage of any DB2 address space, primarily because of the buffer pools. A production DB2 subsystem might allocate 100 GB or more to buffer pools alone.

3.2.4 DIST — The Distributed Data Facility (DDF)

DIST handles all remote connectivity. If an application on another system — whether another z/OS LPAR, a Linux server, or a Windows workstation — connects to this DB2 subsystem, that connection comes through DIST.

• DRDA protocol: DB2 uses the Distributed Relational Database Architecture (DRDA) protocol for remote access. DIST implements the DRDA Application Server.
• TCP/IP: DIST listens on a configured TCP/IP port (often 446 or a site-specific port) for incoming connections.
• Connection management: DIST manages connection pooling, thread reuse, and workload balancing for remote clients.
• Two-phase commit: When a distributed transaction spans multiple DB2 subsystems, DIST coordinates the two-phase commit protocol.

If your shop has no remote access requirements — which is almost unheard of today — you could technically run DB2 without starting DDF. But in practice, DDF is always running.

3.2.5 IRLM — The Internal Resource Lock Manager

The IRLM is a separate address space, and in fact a separate licensed product, though it ships with DB2. Its sole job is managing locks.

• Lock management: Every time DB2 needs to read or modify a row, page, table, or tablespace, it requests a lock from the IRLM. The IRLM maintains a hash table of all currently held locks.
• Deadlock detection: The IRLM periodically scans for deadlocks — situations where two or more tasks are waiting for locks held by each other. When it finds one, it chooses a victim and rolls back that transaction.
• Lock escalation: When a single transaction holds too many row or page locks on a single tablespace, the IRLM escalates to a table or tablespace lock to reduce overhead.
• Shared across subsystems: In a data sharing environment (multiple DB2 subsystems accessing the same data), the IRLM on each member communicates with a Coupling Facility to coordinate inter-system locking.

The IRLM runs with a very high dispatching priority because every data access operation depends on it. If the IRLM is slow, every SQL statement is slow.

3.2.6 WLM-Managed Stored Procedure Address Spaces

When you create a stored procedure in DB2 for z/OS, you can specify that it runs in a WLM-managed (Workload Manager) address space. This means:

• The stored procedure code runs outside of DBM1, in its own protected environment.
• If the stored procedure ABENDs (abnormally terminates), it does not crash DBM1.
• WLM can manage the number of address spaces and their priority based on service goals.

This is a key architectural safety feature. You do not want a poorly written stored procedure to bring down the entire database engine.

Check Your Understanding

1. Which DB2 z/OS address space would you investigate if SQL statements are compiling slowly?
2. If a remote Java application cannot connect to DB2 on z/OS, which address space is the first place to look?
3. Why does the IRLM run as a separate address space rather than being embedded in DBM1?

(Answers: 1. DBM1 — it handles SQL compilation and the EDM pool where plans are cached. 2. DIST — it handles all remote/distributed connections. 3. Isolation and reliability — separating the lock manager means a lock manager issue does not crash the database engine, and vice versa. It also allows one IRLM to serve multiple DB2 subsystems.)

3.3 [LUW] Instance Architecture

On Linux, UNIX, and Windows (LUW), DB2's architecture follows a fundamentally different model rooted in the operating system concepts of processes and shared memory rather than z/OS address spaces.

3.3.1 The Instance Concept

A DB2 instance is the runtime environment in which databases exist. Think of it as a named container that owns:

• A set of database manager configuration parameters (dbm cfg).
• One or more databases.
• A set of operating system processes that do the work.
• Network listeners for client connections.

You can have multiple instances on a single server. Each instance is independent — it has its own port number, its own configuration, its own set of databases, and its own processes. This is useful for separating development, test, and production workloads, or for running different DB2 versions side by side.

An instance is created with the db2icrt command and is associated with an operating system user (the instance owner). On Linux, you might see:

$ db2icrt -u db2fenc1 db2inst1

This creates an instance named db2inst1 with db2fenc1 as the fenced user (more on fenced mode shortly).

3.3.2 Key Processes

When a DB2 LUW instance is started (db2start), several processes are created:

Operating System
|
+-- DB2 Instance (e.g., db2inst1)
    |
    +-- db2sysc    (Main system controller process)
    |     - Manages instance-level resources
    |     - Coordinates database activation
    |     - Handles configuration
    |
    +-- db2ipccm   (IPC communication manager)
    |     - Local (same-machine) connections
    |
    +-- db2tcpcm   (TCP/IP communication manager)
    |     - Remote connections via TCP/IP
    |
    +-- db2agent   (Agent processes — one per connection or pooled)
    |     - Executes SQL on behalf of a client
    |     - Performs compilation, optimization, data access
    |
    +-- db2pfchr   (Prefetcher processes)
    |     - Asynchronous read-ahead
    |
    +-- db2pclnr   (Page cleaners)
    |     - Write changed pages from buffer pool to disk
    |
    +-- db2loggr   (Logger process)
    |     - Writes transaction log records
    |
    +-- db2logts   (Log reader for rollback)
    |
    +-- db2dlock   (Deadlock detector)
    |
    +-- db2fmp     (Fenced Mode Processes)
          - Run user code (UDFs, stored procs) outside engine

3.3.3 Agent Model

The agent is the fundamental unit of work execution in DB2 LUW. When a client application connects and issues SQL, a db2agent process (or thread, depending on configuration) is assigned to handle that work.

DB2 LUW supports two models:

• Coordinating agents: Each active connection gets a coordinating agent that manages the work for that connection.
• Subagents: For intra-partition parallelism, a coordinating agent can spawn subagents to parallelize a single query across multiple processors.

The MAX_COORDAGENTS and NUM_POOLAGENTS configuration parameters control how agents are allocated and pooled. Agent pooling is important — creating a new operating system process or thread for every connection is expensive, so DB2 maintains a pool of idle agents that can be quickly assigned to new requests.

3.3.4 Fenced Mode

When user-written code — a stored procedure written in C, or a Java user-defined function — runs inside the DB2 engine process, a bug in that code could crash the engine itself. To prevent this, DB2 LUW offers fenced mode:

• Fenced routines run in a separate process (db2fmp — Fenced Mode Process).
• This process has its own memory space.
• If the fenced routine crashes, the db2fmp process dies, but the DB2 engine continues running.
• There is a performance cost — data must be passed between the engine and the fenced process — but the safety benefit is usually worth it.

Unfenced routines run directly in the engine's address space. They are faster but riskier. Only thoroughly tested, trusted code should be registered as unfenced.

3.3.5 Database Partition Feature (DPF)

For very large databases, DB2 LUW supports partitioning across multiple physical or logical nodes. This is the Database Partition Feature (DPF), sometimes called "multi-partition" or historically "MPP" mode.

With DPF:

• A single database is spread across multiple database partitions.
• Each partition has its own buffer pools, log files, and tablespace containers.
• Data is distributed across partitions using a distribution key (hash partitioning).
• A query against a partitioned table is automatically parallelized, with each partition processing its local data and a coordinator assembling the final result.

DPF is a major architectural feature used by large data warehouse installations. For OLTP workloads like Meridian Bank, single-partition deployments are more common, but it is important to know DPF exists because it profoundly affects architecture decisions.

DPF Architecture (simplified):

  Client Application
        |
        v
  Coordinator Partition (Partition 0)
        |
   +---------+---------+
   |         |         |
   v         v         v
Partition  Partition  Partition
   0         1         2
  [data]    [data]    [data]
  [logs]    [logs]    [logs]
  [BPs]     [BPs]     [BPs]

Check Your Understanding

1. What is the DB2 LUW equivalent of the z/OS DIST address space?
2. Why would you choose fenced mode over unfenced mode for a stored procedure?
3. If you need to run two completely independent sets of databases on one Linux server, how would you organize this?

(Answers: 1. The db2tcpcm process handles incoming TCP/IP connections, analogous to DIST/DDF. 2. Fenced mode protects the engine from crashes in user code — if the stored procedure has a bug, only the fenced process fails. 3. Create two separate instances using db2icrt, each with its own configuration, port, and databases.)

3.4 Buffer Pools — DB2's Most Important Performance Feature

If you remember only one thing from this chapter, let it be this: buffer pools are the single most important performance feature in DB2 on any platform. Everything else — optimizer improvements, index strategies, hardware upgrades — is secondary to having a properly sized and configured buffer pool.

3.4.1 What Is a Buffer Pool?

A buffer pool is a region of memory that DB2 uses to cache pages of data and index information. When DB2 needs to read a row from a table, it does not go directly to disk. Instead:

1. DB2 checks the buffer pool to see if the page containing that row is already in memory.
2. If yes (buffer pool hit), DB2 reads the data directly from memory — nanoseconds.
3. If no (buffer pool miss), DB2 reads the page from disk into the buffer pool, then reads the data from memory — milliseconds.

The difference between a nanosecond and a millisecond is roughly a factor of one million. This is why buffer pools matter so much.

Threshold Concept: The Buffer Pool

The buffer pool is a threshold concept because it changes how you think about database performance forever. Before understanding buffer pools, students tend to think of databases as "reading from disk." After understanding buffer pools, you realize that a well-tuned DB2 system reads from disk as rarely as possible. The goal is to keep the most frequently and recently accessed data in memory at all times.

This single insight reframes every performance discussion. When someone says "this query is slow," your first question should be: "What is the buffer pool hit ratio?" If it is below 95%, you have your answer before you even look at the SQL.

3.4.2 Page Sizes

DB2 supports buffer pools with four page sizes:

A tablespace must be assigned to a buffer pool of matching page size. You cannot put 8K tablespace pages into a 4K buffer pool.

On z/OS, you define buffer pools with names like BP0 (4K), BP8K0 (8K), BP16K0 (16K), and BP32K (32K). Each has a fixed naming convention.

On LUW, buffer pool names are user-defined (e.g., MYBP_8K), and you specify the page size at creation:

-- LUW example
CREATE BUFFERPOOL MERIDIAN_BP_8K
  SIZE 25000        -- 25,000 pages
  PAGESIZE 8K;

3.4.3 Buffer Pool Hit Ratio

The buffer pool hit ratio (BPHR) is the percentage of page requests satisfied from the buffer pool without requiring physical I/O:

BPHR = (1 - (Physical Reads / Logical Reads)) * 100

Industry targets:

For Meridian Bank's OLTP workload — customer lookups, account inquiries, transaction posting — we would target a buffer pool hit ratio of at least 98%. This means that 98 out of every 100 page requests are satisfied from memory.

3.4.4 How the Buffer Pool Works Internally

The buffer pool uses a Least Recently Used (LRU) algorithm, with some important refinements:

1. Pages are read in: When a page is needed and is not in the buffer pool, it is read from disk and placed in the buffer pool.
2. Pages are aged out: When the buffer pool is full and a new page is needed, the least recently used page is a candidate for replacement.
3. Dirty pages: If a page has been modified (an UPDATE or INSERT changed data on it), it is marked dirty. Dirty pages must be written to disk before they can be reused. This is done by page cleaners (asynchronous write processes).
4. Steal/No-Force: DB2 uses a steal policy (dirty pages can be written to disk before the transaction commits) and a no-force policy (dirty pages do NOT have to be written to disk at commit time). This combination provides the best performance, and write-ahead logging guarantees recovery.

On z/OS, DB2 uses parallel sequential LRU chains (also called FIFO and LRU chains) with a threshold system. Pages move from the FIFO chain to the LRU chain based on reference patterns. There are also virtual pool and data space options for very large buffer pools.

On LUW, the buffer pool manager uses a similar LRU-based approach, with the option of block-based I/O for sequential prefetch operations.

3.4.5 Multiple Buffer Pools — Why and How

A single buffer pool for everything is simple but suboptimal. Here is why you use multiple buffer pools:

• Isolate workloads: Keep OLTP data separate from batch scan data. A large batch scan can "flood" a shared buffer pool, pushing out the frequently accessed OLTP pages.
• Match page sizes: Different tables may benefit from different page sizes.
• Tune independently: You can make the customer-account buffer pool very large (because those tables are accessed constantly) and the audit-log buffer pool smaller (because audit rows are written and rarely re-read).
• Monitor independently: Separate buffer pools give you separate hit ratios, making it easier to identify performance problems.

For Meridian Bank, we might configure:

Buffer Pool Plan:
  BP_CUST_8K   (8K pages, 50,000 pages = ~400 MB)  -- CUSTOMER, ACCOUNT tables
  BP_TRANS_8K  (8K pages, 30,000 pages = ~240 MB)  -- TRANSACTION table
  BP_IDX_4K   (4K pages, 20,000 pages = ~80 MB)    -- Indexes
  BP_TEMP_32K  (32K pages, 5,000 pages = ~160 MB)  -- Temp tablespace for sorts
  BP_CATALOG   (4K pages, 10,000 pages = ~40 MB)   -- Catalog tables

Check Your Understanding

1. If your buffer pool hit ratio is 80% for an OLTP workload, what is your most likely first action?
2. Can you assign a tablespace with 16K pages to a buffer pool configured for 8K pages?
3. Why is separating indexes into their own buffer pool sometimes beneficial?

(Answers: 1. Increase the buffer pool size — 80% is too low for OLTP and means 20% of page requests are going to disk. 2. No — page sizes must match between the tablespace and its assigned buffer pool. 3. Index pages are typically accessed much more frequently than data pages and tend to be smaller. Putting them in their own pool prevents data page scans from pushing index pages out of memory.)

3.5 [z/OS] EDM Pool, RID Pool, and Sort Pool

Beyond buffer pools, DB2 on z/OS maintains several other critical memory pools within the DBM1 address space.

3.5.1 The Environmental Descriptor Manager (EDM) Pool

The EDM pool caches:

• Compiled SQL plans and packages: When DB2 compiles a SQL statement (either statically via BIND or dynamically via PREPARE), the resulting access plan is stored in the EDM pool. Subsequent executions of the same statement can reuse the cached plan without recompilation.
• Database descriptors (DBDs): These are internal descriptions of database objects — table structures, column definitions, index metadata. DB2 reads these from the DB2 catalog on first reference and caches them in the EDM pool.
• Dynamic statement cache: Dynamically prepared SQL statements are cached so that identical SQL text can reuse a previously optimized plan.
• Authorization cache: Cached authorization information to avoid repeated catalog lookups.

If the EDM pool is too small, DB2 must repeatedly read plans and descriptors from the catalog (which itself requires I/O and buffer pool access), causing significant overhead. The symptom is high catalog I/O and frequent "plan not in pool" conditions.

Sizing the EDM pool depends on the number of distinct plans, packages, and dynamic SQL statements your workload uses. A large enterprise system with thousands of packages and heavy dynamic SQL might need an EDM pool of several hundred megabytes.

3.5.2 The RID Pool

The RID (Record Identifier) Pool is used during certain access strategies:

• RID list processing: When DB2 uses a non-matching index to access data, it may collect a list of RIDs (pointers to rows) from the index, sort them by page number, and then access the data pages in sequence. This avoids random I/O. The RID list is stored in the RID pool.
• Multi-index access: When DB2 combines results from multiple indexes (e.g., one index on CITY and another on STATE), it collects RID lists from each index and performs set operations (intersection, union) in the RID pool.

If the RID pool is too small, DB2 falls back to less efficient access methods (typically a tablespace scan), which can dramatically hurt performance. This is called RID pool failure and is a known performance anti-pattern.

The z/OS DB2 command DISPLAY BUFFERPOOL and performance monitoring tools report RID pool usage. Sizing is typically based on the expected number of concurrent RID list operations and their average size.

3.5.3 The Sort Pool

When DB2 must sort data — for ORDER BY, GROUP BY, DISTINCT, or merge join operations — it uses memory in the sort pool. Key considerations:

• Sort pool size (SRTPOOL ZPARM): Determines the maximum memory available for a single sort operation.
• Sort overflow: If the data to be sorted exceeds the sort pool size, DB2 spills to disk (using a work file tablespace), which is dramatically slower.
• Multiple concurrent sorts: Each concurrent sort uses its own allocation from available sort memory.

For Meridian Bank, where customer-facing queries often include ORDER BY clauses on transaction dates or account balances, properly sizing the sort pool is essential to avoid disk-based sorting.

3.6 [LUW] Memory Architecture

DB2 LUW organizes memory into a layered hierarchy, and offers an automated tuning capability that z/OS does not have.

3.6.1 The Memory Hierarchy

+-----------------------------------------------+
|           Instance Shared Memory               |
|  (monitor heap, FCM buffers for DPF, etc.)     |
+-----------------------------------------------+
         |                    |
+------------------+  +------------------+
| Database Memory  |  | Database Memory  |
| (Database A)     |  | (Database B)     |
|                  |  |                  |
| - Buffer Pools   |  | - Buffer Pools   |
| - Lock List      |  | - Lock List      |
| - Package Cache  |  | - Package Cache  |
| - Catalog Cache  |  | - Catalog Cache  |
| - Sort Heap      |  | - Sort Heap      |
| - Utility Heap   |  | - Utility Heap   |
+------------------+  +------------------+
         |
+------------------+
| Application      |
| Memory           |
| (per connection) |
|                  |
| - Sort Heap      |
|   (private sort) |
| - Statement Heap |
| - Application    |
|   Heap           |
+------------------+

Instance Shared Memory is allocated when the instance starts (db2start). It holds resources shared across all databases in the instance.

Database Shared Memory is allocated when a database is first connected to (or explicitly activated with ACTIVATE DATABASE). Each database has its own set of buffer pools, its own lock list, its own package cache, and so on.

Application (Private) Memory is allocated per connection. Each agent gets its own private memory for sort operations, statement compilation, and application variables.

3.6.2 Key Memory Areas (LUW)

3.6.3 Self-Tuning Memory Manager (STMM)

One of DB2 LUW's most significant architectural advantages is the Self-Tuning Memory Manager (STMM). When enabled, STMM automatically adjusts the sizes of memory areas based on workload demands:

-- Enable STMM at the database level
UPDATE DB CFG FOR MERIDIANDB USING SELF_TUNING_MEM ON;

-- Enable automatic tuning for specific memory areas
ALTER BUFFERPOOL MERIDIAN_BP_8K SIZE AUTOMATIC;
UPDATE DB CFG FOR MERIDIANDB USING SORTHEAP AUTOMATIC;
UPDATE DB CFG FOR MERIDIANDB USING LOCKLIST AUTOMATIC;
UPDATE DB CFG FOR MERIDIANDB USING PCKCACHESZ AUTOMATIC;

STMM monitors memory demand and redistributes memory from areas with excess capacity to areas under pressure. For example, if the sort heap is rarely used but the buffer pools are overflowing, STMM will shrink the sort heap and grow the buffer pools.

This does not mean you can ignore memory configuration entirely — you still need to set a reasonable DATABASE_MEMORY total and ensure the server has enough physical RAM. But STMM significantly reduces the ongoing tuning burden.

Productive Struggle: z/OS vs. LUW Memory

Here is a challenge for you. Study Sections 3.5 and 3.6 carefully, then try to map each z/OS memory concept to its LUW equivalent. Some mappings are straightforward (EDM pool maps roughly to package cache + catalog cache). Some are less obvious. Try to fill in this table before looking at the answer:

z/OS Concept	LUW Equivalent
EDM Pool (plans/packages)	?
EDM Pool (DBDs)	?
RID Pool	?
Sort Pool	?
Buffer Pools	?
IRLM lock structures	?

Answers: EDM Pool (plans) = Package Cache (PCKCACHESZ). EDM Pool (DBDs) = Catalog Cache (CATALOGCACHE_SZ). RID Pool = No direct equivalent — LUW handles list prefetch differently, using sort heap memory. Sort Pool = Sort Heap (SORTHEAP). Buffer Pools = Buffer Pools (same concept, different management). IRLM lock structures = Lock List (LOCKLIST) and lock heap memory.

3.7 Storage Architecture — Tablespaces and Data Sets

Memory is where DB2 does its work. Disk is where DB2 keeps its data persistently. The bridge between the two is the tablespace — DB2's logical unit of storage.

3.7.1 The Tablespace Concept

A tablespace is a logical container that holds one or more tables. Every table in DB2 must reside in a tablespace. The tablespace, in turn, maps to physical storage — data sets on z/OS, or containers (files or directories) on LUW.

Why not just put tables directly on disk? Because the tablespace provides an abstraction layer that enables:

• Buffer pool assignment: Each tablespace is assigned to a buffer pool.
• Space management: DB2 manages free space within the tablespace.
• Locking granularity: Tablespace-level, table-level, page-level, or row-level locking.
• Backup granularity: You can back up at the tablespace level.
• Storage allocation: You control where and how physical storage is allocated.

3.7.2 [z/OS] Storage Objects

On z/OS, DB2 uses VSAM Linear Data Sets (LDS) as the physical storage for tablespaces and index spaces:

Database (logical grouping)
  |
  +-- Tablespace (e.g., TSCUST)
  |     |
  |     +-- VSAM Linear Data Set(s)
  |     |     (e.g., DSNDB06.TSCUST.I0001.A001)
  |     |
  |     +-- Table(s) within the tablespace
  |
  +-- Index Space (e.g., IXCUST01)
        |
        +-- VSAM Linear Data Set(s)

Types of tablespaces on z/OS (historical and current):

• Simple tablespace (deprecated): Multiple tables share space in any order. Difficult to manage. Do not use for new development.
• Segmented tablespace (legacy): Space is divided into fixed-size segments, with each segment belonging to a single table. Better than simple, but superseded.
• Partitioned tablespace (classic): A single table is divided into partitions based on key ranges. Each partition has its own data set. Essential for very large tables.
• Universal Table Space (UTS): The modern standard. Comes in two sub-types:
• Partition-by-range (PBR): Like classic partitioned, but with improved manageability and support for up to 4,096 partitions.
• Partition-by-growth (PBG): Automatically adds partitions as data grows. Simpler to manage for tables where range partitioning is not needed.

For new development, IBM recommends UTS exclusively. Meridian Bank should use PBR for large tables like TRANSACTION (partitioned by date) and PBG for smaller reference tables.

3.7.3 [LUW] Storage Objects

On LUW, the storage model uses containers — files, directories, or raw devices — to hold tablespace data:

Database
  |
  +-- Tablespace (e.g., TS_CUSTOMER)
  |     |
  |     +-- Container(s)
  |     |     (files or directories on filesystem)
  |     |
  |     +-- Table(s) within the tablespace
  |
  +-- Tablespace for indexes (or indexes in same TS)

Types of tablespaces on LUW:

• System Managed Space (SMS): The operating system manages space allocation. DB2 creates files within a directory. Simple but limited control.
• Database Managed Space (DMS): DB2 manages space allocation within pre-allocated files or raw devices. Better performance, more control.
• Automatic Storage: The modern standard (default since DB2 9). DB2 manages container creation and extension automatically based on storage paths defined for the database. Combines the ease of SMS with the performance of DMS.

-- LUW: Create a database with automatic storage paths
CREATE DATABASE MERIDIANDB ON '/db2data/path1', '/db2data/path2';

-- Tablespace using automatic storage (the default)
CREATE TABLESPACE TS_CUSTOMER
  PAGESIZE 8K
  MANAGED BY AUTOMATIC STORAGE
  BUFFERPOOL MERIDIAN_BP_8K;

Automatic storage is the recommended approach for most new LUW deployments, including Meridian Bank.

3.7.4 Data Organization Within a Tablespace

Within a tablespace, data is organized into pages (the smallest unit of I/O) and extents (contiguous groups of pages that form the smallest unit of space allocation):

Tablespace
  |
  +-- Extent 0 (e.g., 32 pages)
  |     +-- Page 0 (header)
  |     +-- Page 1 (data rows)
  |     +-- Page 2 (data rows)
  |     +-- ...
  |
  +-- Extent 1 (32 pages)
  |     +-- Page 32
  |     +-- Page 33
  |     +-- ...
  |
  +-- ...

Each data page contains a header, row data, and a free space area. The PCTFREE parameter controls how much free space to leave on each page for future updates.

3.8 The Logging System — DB2's Insurance Policy

If the buffer pool is DB2's most important performance feature, the log is DB2's most important reliability feature. The log ensures that committed transactions are never lost and that incomplete transactions are cleanly rolled back, even in the event of a crash.

3.8.1 Write-Ahead Logging (WAL)

DB2 uses a protocol called Write-Ahead Logging (WAL). The rule is simple and absolute:

Before any changed data page is written from the buffer pool to disk, the log records describing those changes must be written to the log first.

This guarantees that if DB2 crashes after writing a log record but before writing the data page, DB2 can redo the change during recovery by reading the log record and reapplying the change. And if a transaction's log records are never followed by a COMMIT log record, DB2 can undo the changes by reading the log records in reverse.

The WAL protocol is what makes the steal/no-force buffer pool policy safe. Without WAL, you would have to force all dirty pages to disk at every commit (catastrophic for performance) or risk losing committed data in a crash.

Threshold Concept: Write-Ahead Logging

Write-ahead logging is a threshold concept because once you understand it, you understand why DB2 can be both fast and reliable at the same time. It resolves what seems like a paradox: "How can DB2 NOT write data pages to disk at commit time and still guarantee that committed data survives a crash?" The answer is that the log records are written at commit time, and the log records contain enough information to reconstruct any change. The actual data pages can be written later, at DB2's leisure.

This insight also explains why log performance is so critical. If the log is slow, every COMMIT is slow, and therefore every transaction is slow.

3.8.2 Log Components

Both z/OS and LUW share the same conceptual log architecture:

Transaction Processing
       |
       v
  Log Buffers (in memory)
       |
       v (forced at COMMIT or when buffer fills)
  Active Log (on disk)
       |
       v (when active log fills up)
  Archive Log (on disk or tape)

• Log buffers: An in-memory area where log records are first written. This avoids a physical I/O for every single log record. Log buffers are flushed to the active log when a COMMIT occurs or when the buffer reaches a threshold.
• Active log: A set of log data sets (z/OS) or log files (LUW) that hold recent log records. This is a circular structure — when one data set fills, logging continues in the next, and when they are all full, the oldest is reused (after being archived).
• Archive log: When an active log data set is full, its contents are copied to an archive log. Archive logs are kept for as long as your recovery requirements dictate — days, weeks, or years.

3.8.3 [z/OS] Logging Architecture

On z/OS, the logging architecture includes:

Active log data sets: Typically 2 to 93 data sets, each a VSAM linear data set. DB2 writes to them in rotation.

Dual active logging: For critical systems, DB2 supports dual active logging — every active log data set has a copy. If one copy is damaged, DB2 can use the other. This is standard practice in production environments.

Archive log data sets: When an active log fills up, DB2 copies it to an archive data set. Archives can be on DASD or on tape (increasingly, on virtual tape).

The Bootstrap Data Set (BSDS): This is a critical data set that contains: - The inventory of all active log data sets and archive log data sets (a mapping of log RBA ranges to data set names). - System timestamps and checkpoint records. - The DDF communication record (containing the location name and port).

The BSDS is also duplexed (two copies). If both copies of the BSDS are lost, recovering DB2 becomes extremely difficult. Protect the BSDS.

z/OS Logging Architecture:

  Log Buffers (in MSTR address space)
       |
       v
  Active Log Data Sets (dual)
  +--------+  +--------+
  | COPY 1 |  | COPY 2 |
  | Log A  |  | Log A  |
  | Log B  |  | Log B  |
  | Log C  |  | Log C  |
  +--------+  +--------+
       |
       v  (offload/archive)
  Archive Logs (DASD or tape)

  BSDS (dual copies)
  +--------+  +--------+
  | BSDS1  |  | BSDS2  |
  +--------+  +--------+

3.8.4 [LUW] Logging Architecture

On LUW, the logging architecture is similar in concept but different in implementation:

Log files: Active log files are stored in the file system. You configure the number of primary log files (LOGPRIMARY), secondary log files (LOGSECOND), and the size of each file (LOGFILSIZ).

Circular vs. archive logging: - Circular logging (default): Log files are reused in a circular fashion. Once a log file's transactions are all committed, it can be overwritten. This supports crash recovery and rollback, but NOT point-in-time recovery (because old log records are overwritten). - Archive logging (LOGARCHMETH1): Old log files are archived before being reused. This enables point-in-time recovery (using ROLLFORWARD DATABASE). Essential for production systems.

Log archive methods: LUW supports multiple archive destinations including disk, TSM (Tivoli Storage Manager / IBM Spectrum Protect), and vendor-specific media.

-- LUW: Configure archive logging
UPDATE DB CFG FOR MERIDIANDB USING LOGARCHMETH1 'DISK:/db2archlog/';
UPDATE DB CFG FOR MERIDIANDB USING LOGPRIMARY 20;
UPDATE DB CFG FOR MERIDIANDB USING LOGSECOND 10;
UPDATE DB CFG FOR MERIDIANDB USING LOGFILSIZ 10000;  -- 10,000 4K pages = 40MB per log file

For Meridian Bank, archive logging is mandatory. The banking regulators require the ability to recover to any point in time, which is only possible with archive logging.

3.8.5 Checkpoint Processing

A checkpoint is a synchronization point where DB2 records which log position corresponds to a consistent state. During recovery, DB2 only needs to process log records from the last checkpoint forward, rather than scanning the entire log from the beginning of time.

On z/OS, checkpoints are triggered by LOGLOAD (number of log records written) or by time interval. On LUW, checkpoints (called soft checkpoints) are triggered periodically to limit recovery time.

3.9 The SQL Statement Lifecycle — From Parse to Result Set

Now let us trace what happens when an application submits a SQL statement to DB2. We will follow a simple query from Meridian Bank:

SELECT CUSTOMER_ID, FIRST_NAME, LAST_NAME, EMAIL
  FROM CUSTOMER
 WHERE CUSTOMER_ID = 10042;

3.9.1 Phase 1: Connection and Authentication

Before any SQL can execute, the application must connect to DB2:

• [z/OS]: The application (e.g., a CICS transaction or a JDBC client) establishes a connection through a thread. If the connection is remote, it comes through DDF (DIST). DB2 authenticates the user (often via RACF on z/OS) and creates a thread structure to track this session.
• [LUW]: The application connects through db2tcpcm (remote) or db2ipccm (local). An agent is assigned from the agent pool. Authentication is performed against the operating system, LDAP, or a security plugin.

3.9.2 Phase 2: SQL Parsing and Validation

When the SQL statement arrives at the DB2 engine:

1. Syntax check: Is the SQL syntactically valid? (SELECT and FROM are present, keywords are correct, parentheses balance, etc.)
2. Semantic check: Do the referenced objects exist? Is CUSTOMER a real table? Does it have columns called CUSTOMER_ID, FIRST_NAME, LAST_NAME, EMAIL?
3. Authorization check: Does this user have SELECT authority on the CUSTOMER table?

These checks require reading metadata from the DB2 catalog. On z/OS, this information may be cached in the EDM pool. On LUW, it may be cached in the catalog cache.

3.9.3 Phase 3: Optimization

If the statement passes validation, DB2's optimizer takes over. The optimizer's job is to determine the most efficient way to retrieve the requested data. For our query, the optimizer considers:

• Table scan: Read every page of the CUSTOMER table and check each row's CUSTOMER_ID. Simple but slow for large tables.
• Index access: If there is an index on CUSTOMER_ID (and there certainly should be — it is the primary key), use the index to go directly to the row. Fast.

The optimizer evaluates the cost of each option using statistics about the table (number of rows, number of distinct values, data distribution) stored in the catalog. It generates an access plan — a tree of operations that describes exactly how to execute the query.

For our query, the access plan almost certainly looks like:

Access Plan:
  1. INDEX SCAN on PK_CUSTOMER (matching predicate: CUSTOMER_ID = 10042)
  2. FETCH from CUSTOMER table using RID from index
  3. RETURN result row

3.9.4 Phase 4: Access Plan Execution

The data manager executes the access plan:

1. Index access: DB2 navigates the B-tree index on CUSTOMER_ID. Starting at the root page, it follows pointers through intermediate pages to the leaf page containing the entry for CUSTOMER_ID = 10042. Each page access is a buffer pool request.
2. RID retrieval: The index leaf page contains a Record Identifier (RID) pointing to the specific page and slot in the CUSTOMER tablespace where row 10042 is stored.
3. Data page access: DB2 requests the data page from the buffer pool. If the page is in the buffer pool (hit), it reads the row immediately. If not (miss), DB2 reads the page from disk into the buffer pool, then reads the row.
4. Lock acquisition: Before reading the row, DB2 acquires a lock from the lock manager (IRLM on z/OS, internal lock manager on LUW). For a simple SELECT, this is typically a shared (S) lock, which allows other readers but blocks writers.

3.9.5 Phase 5: Result Return

DB2 formats the result row (CUSTOMER_ID, FIRST_NAME, LAST_NAME, EMAIL) and returns it to the application through the same communication path (thread/agent) that brought the request.

If this were a multi-row result set, DB2 would use a cursor model — the application fetches one row at a time (or a block of rows with block fetch / multi-row fetch).

3.9.6 The Complete Picture

Here is the full lifecycle in one diagram:

Application: SELECT ... FROM CUSTOMER WHERE CUSTOMER_ID = 10042
       |
       v
  [Connection Layer]  -- DIST/DDF (z/OS) or db2tcpcm (LUW)
       |
       v
  [SQL Parser]  -- Syntax check, semantic check
       |                              +-------------+
       +---- Catalog lookup --------->| Catalog     |
       |     (EDM pool / cat cache)   | (metadata)  |
       v                              +-------------+
  [Optimizer]  -- Generate access plan
       |         Uses catalog statistics
       v
  [Data Manager]  -- Execute access plan
       |
       +---- Lock request ----------->+-------------+
       |                              | Lock Mgr    |
       |                              | (IRLM/      |
       |                              |  internal)  |
       +---- Buffer pool request ---->+-------------+
       |                              | Buffer Pool |
       |                              | (check for  |
       |    (if miss)                 |  page)      |
       +---- Physical I/O ----------->+-------------+
       |                              | Storage     |
       |                              | (disk)      |
       v                              +-------------+
  [Result Formatter]
       |
       v
  Application receives result set

The entire process for a simple indexed lookup on a well-tuned system takes 1-5 milliseconds. For a buffer-pool-resident indexed lookup, it can be under 0.5 milliseconds.

Check Your Understanding

1. At which phase does DB2 decide whether to use an index or a table scan?
2. What happens if the data page needed for the query is not in the buffer pool?
3. What type of lock does a simple SELECT statement typically acquire?

(Answers: 1. Phase 3 — Optimization. The optimizer evaluates access paths and chooses the cheapest plan. 2. A buffer pool miss occurs — DB2 reads the page from physical disk into the buffer pool, then reads the row from the buffer pool page. This is much slower than a buffer pool hit. 3. A shared (S) lock, which allows other concurrent readers but blocks writers from modifying the row until the reader is done.)

3.10 The Catalog and Directory — DB2's Brain

Every DB2 system maintains a catalog — a set of tables that describe every object in the database. The catalog is DB2's metadata repository. Without it, DB2 would not know what tables exist, what columns they contain, what indexes are available, or who has authority to access what.

3.10.1 [z/OS] The Catalog and Directory

On z/OS, DB2 has two metadata repositories:

The DB2 Catalog (database DSNDB06): - A set of DB2 tables that you can query with SQL. - Contains information about tables, columns, indexes, views, tablespaces, plans, packages, authorities, and more. - Key tables include: SYSIBM.SYSTABLES, SYSIBM.SYSCOLUMNS, SYSIBM.SYSINDEXES, SYSIBM.SYSTABLESPACE, SYSIBM.SYSPACKAGE. - This is the "public" metadata — you can SELECT from it (with appropriate authority).

The DB2 Directory (database DSNDB01): - An internal structure used only by DB2 itself. - Contains the Skeleton Cursor Table (SKCTs) and Cursor Table (CTs) — internal representations of compiled plans and packages. - Contains the Database Descriptor (DBD) — the internal description of the physical database structure. - You cannot query the directory with SQL. DB2 manages it internally.

The distinction matters because the catalog can be queried for troubleshooting and analysis:

-- z/OS: Find all tables in the MERIDIAN schema
SELECT NAME, TYPE, COLCOUNT, CARDF
  FROM SYSIBM.SYSTABLES
 WHERE CREATOR = 'MERIDIAN'
 ORDER BY NAME;

-- z/OS: Find all indexes on the CUSTOMER table
SELECT NAME, TBNAME, UNIQUERULE, COLCOUNT, CLUSTERING
  FROM SYSIBM.SYSINDEXES
 WHERE TBCREATOR = 'MERIDIAN'
   AND TBNAME = 'CUSTOMER';

3.10.2 [LUW] The System Catalog

On LUW, there is no separate "directory." All metadata is in the system catalog, accessed through SYSCAT views:

-- LUW: Find all tables in the MERIDIAN schema
SELECT TABNAME, TYPE, COLCOUNT, CARD
  FROM SYSCAT.TABLES
 WHERE TABSCHEMA = 'MERIDIAN'
 ORDER BY TABNAME;

-- LUW: Find all indexes on the CUSTOMER table
SELECT INDNAME, TABNAME, UNIQUERULE, COLCOUNT
  FROM SYSCAT.INDEXES
 WHERE TABSCHEMA = 'MERIDIAN'
   AND TABNAME = 'CUSTOMER';

Key SYSCAT views include:

3.10.3 Why the Catalog Matters for Performance

The catalog is not just a documentation convenience — it is central to performance. The optimizer reads statistics from the catalog to make access path decisions. These statistics include:

• CARD (cardinality): The number of rows in a table.
• COLCARD: The number of distinct values in a column.
• HIGH2KEY / LOW2KEY: The second-highest and second-lowest values in a column.
• CLUSTERRATIO: How well the physical order of data matches the index order.
• NLEAF / NLEVELS: The number of leaf pages and levels in an index.

If these statistics are stale or missing, the optimizer makes poor decisions. Running RUNSTATS (z/OS) or RUNSTATS (LUW) regularly is essential.

-- z/OS: Collect statistics
RUNSTATS TABLESPACE MERIDIANDB.TSCUST
  TABLE(MERIDIAN.CUSTOMER) INDEX(ALL)
  SHRLEVEL CHANGE;

-- LUW: Collect statistics
RUNSTATS ON TABLE MERIDIAN.CUSTOMER WITH DISTRIBUTION AND DETAILED INDEXES ALL;

For Meridian Bank, a nightly RUNSTATS job on all major tables is a baseline requirement. Without current statistics, the optimizer is flying blind.

3.11 Mapping Meridian Bank to DB2 Architecture

Let us now take everything we have learned and make it concrete by designing the DB2 architecture for Meridian National Bank. We will show both a z/OS deployment and a LUW deployment.

3.11.1 The Meridian Bank Environment

Recall from Chapter 2 that Meridian Bank has these core tables:

• CUSTOMER: Customer information (~500,000 rows)
• ACCOUNT: Bank accounts (~1,200,000 rows)
• TRANSACTION: Financial transactions (~50,000,000 rows and growing by 200,000/day)
• BRANCH: Bank branches (~250 rows)
• EMPLOYEE: Bank employees (~3,000 rows)

The workload is mixed OLTP: high-volume customer lookups, account inquiries, and transaction posting during the day, with batch reporting and statement generation overnight.

3.11.2 [z/OS] Meridian Bank Architecture

z/OS LPAR: MBNKPROD
|
+-- DB2 Subsystem: MBPD (Meridian Bank Production DB2)
    |
    +-- MBPDMSTR (System Services)
    |     - Dual active logging (6 active log data sets, each 2 GB)
    |     - Archive logging to virtual tape
    |     - BSDS dual copies
    |     - Log checkpoint every 500,000 log records
    |
    +-- MBPDDBM1 (Database Services)
    |     Buffer Pools:
    |     - BP2  (8K, 60,000 pages = 480 MB) -- CUSTOMER, ACCOUNT data
    |     - BP3  (8K, 40,000 pages = 320 MB) -- TRANSACTION data
    |     - BP4  (4K, 30,000 pages = 120 MB) -- Indexes
    |     - BP5  (32K, 5,000 pages = 160 MB) -- Temp/work
    |     - BP0  (4K, 15,000 pages = 60 MB)  -- Catalog, small ref tables
    |     Total buffer pool memory: ~1.14 GB
    |
    |     EDM Pool: 200 MB
    |     RID Pool: 50 MB
    |     Sort Pool: 64 MB
    |
    +-- MBPDDIST (DDF)
    |     - Port 5040
    |     - Location name: MERIDIANPROD
    |     - Supports JDBC from web application servers
    |
    +-- IRLM
    |     - Lock escalation threshold: 5,000 locks per tablespace
    |
    +-- WLM-managed address spaces
          - Stored procedures for transaction posting
          - Java UDFs for account validation

Storage Layout:
  Database MERIDIANDB:
    Tablespace TSCUST    (PBR UTS, 8K pages, BP2)
      - CUSTOMER table, partitioned by CUSTOMER_ID range
    Tablespace TSACCT    (PBR UTS, 8K pages, BP2)
      - ACCOUNT table, partitioned by ACCOUNT_ID range
    Tablespace TSTRANS   (PBR UTS, 8K pages, BP3)
      - TRANSACTION table, partitioned by TRANS_DATE
      - 12 partitions (monthly), rotating with REORG
    Tablespace TSREF     (PBG UTS, 4K pages, BP0)
      - BRANCH, EMPLOYEE tables
    Index spaces:
      - All indexes in BP4

3.11.3 [LUW] Meridian Bank Architecture

Linux Server: mbnk-prod-db01 (64 GB RAM, 16 cores)
|
+-- DB2 Instance: db2mbnk
    |
    +-- Database: MERIDIANDB
    |     |
    |     +-- Database Memory: 40 GB (SELF_TUNING_MEM ON)
    |     |
    |     +-- Buffer Pools:
    |     |     MBNK_BP_CUSTACCT (8K, AUTOMATIC, initial 50,000 pages)
    |     |     MBNK_BP_TRANS    (8K, AUTOMATIC, initial 30,000 pages)
    |     |     MBNK_BP_IDX      (4K, AUTOMATIC, initial 25,000 pages)
    |     |     MBNK_BP_TEMP     (32K, AUTOMATIC, initial 5,000 pages)
    |     |     IBMDEFAULTBP     (4K, AUTOMATIC) -- catalog, defaults
    |     |
    |     +-- Tablespaces (Automatic Storage):
    |     |     TS_CUSTOMER  (8K, MBNK_BP_CUSTACCT)
    |     |     TS_ACCOUNT   (8K, MBNK_BP_CUSTACCT)
    |     |     TS_TRANS     (8K, MBNK_BP_TRANS)
    |     |       - Range partitioned by TRANS_DATE (monthly)
    |     |     TS_REFERENCE (4K, IBMDEFAULTBP) -- BRANCH, EMPLOYEE
    |     |     TS_INDEX     (4K, MBNK_BP_IDX) -- All indexes
    |     |     TEMPSPACE1   (32K, MBNK_BP_TEMP)
    |     |
    |     +-- Logging:
    |     |     LOGARCHMETH1 = DISK:/db2archlog/
    |     |     LOGPRIMARY = 25
    |     |     LOGSECOND = 15
    |     |     LOGFILSIZ = 10000 (40 MB per log file)
    |     |     LOGBUFSZ = 1024 (4 MB log buffer)
    |     |
    |     +-- Other Configuration:
    |           PCKCACHESZ = AUTOMATIC
    |           LOCKLIST = AUTOMATIC
    |           SORTHEAP = AUTOMATIC
    |           CATALOGCACHE_SZ = AUTOMATIC
    |
    +-- Storage Paths:
          /db2data/path1  (SAN, RAID 10)
          /db2data/path2  (SAN, RAID 10)
          /db2logs/       (SAN, RAID 1, separate spindles)
          /db2archlog/    (NAS or tape)

3.11.4 Design Decisions Explained

Why separate buffer pools for CUSTOMER/ACCOUNT vs. TRANSACTION? The TRANSACTION table is much larger and has a different access pattern (mostly sequential inserts for new transactions, with some lookups by date). Customer and account data is accessed randomly and frequently. Separating them prevents transaction batch scans from flushing customer pages out of the buffer pool.

Why partition TRANSACTION by date? Most transaction queries include a date predicate ("show me transactions for this month"). Partitioning by date means DB2 can eliminate entire partitions that do not match the date range, dramatically reducing I/O. Old partitions can also be archived or purged efficiently.

Why put indexes in their own buffer pool? Index pages are small and frequently accessed. In the critical path of every indexed lookup, the index root and intermediate pages are read. By giving indexes their own buffer pool, we ensure these hot pages stay in memory even when the data buffer pools are under pressure.

Why archive logging? Banking regulations require point-in-time recovery. If a data corruption is discovered at 3:00 PM, Meridian Bank must be able to restore to 2:59 PM. This requires archive logs.

Why separate log storage from data storage? Log writes are sequential and latency-sensitive (every COMMIT waits for the log write). Data writes are more random. Putting logs on separate, fast storage (dedicated RAID 1 or SSD) prevents contention between log writes and data I/O.

Check Your Understanding

1. In the z/OS architecture, why does the TRANSACTION tablespace use BP3 instead of BP2?
2. In the LUW architecture, what does SELF_TUNING_MEM ON accomplish?
3. Why are log files on separate physical storage from data files?

(Answers: 1. To isolate transaction data from customer/account data in the buffer pool, preventing batch scans on transactions from pushing out frequently accessed customer pages. 2. It enables the Self-Tuning Memory Manager, which automatically adjusts buffer pool, sort heap, lock list, and package cache sizes based on workload demand. 3. To prevent I/O contention between latency-sensitive sequential log writes and random data page reads/writes.)

3.12 Spaced Review — Chapters 1 and 2

Before moving on, let us reinforce key concepts from earlier chapters. Try to answer these from memory before checking.

From Chapter 1: 1. In what decade was DB2 first released, and what research project was it based on? 2. Name two differences between DB2 for z/OS and DB2 for LUW beyond the operating system. 3. What does "ACID" stand for in the context of transaction processing?

From Chapter 2: 4. What is the difference between a candidate key and a primary key? 5. What normal form eliminates transitive dependencies? 6. In the Meridian Bank schema, why does the ACCOUNT table have a foreign key to CUSTOMER?

(Take a moment to recall before reading on. Retrieval practice strengthens long-term memory.)

Chapter Summary

In this chapter, we took the lid off DB2 and examined its internal architecture on both z/OS and LUW. Here is what we covered:

z/OS architecture consists of multiple address spaces — MSTR for system services and logging, DBM1 for the database engine and buffer pools, DIST for distributed/remote access, and the IRLM for lock management. Each has a distinct role, and their separation provides isolation, scalability, and serviceability.

LUW architecture uses operating system processes — db2sysc as the main controller, db2agent processes for client work, prefetchers, page cleaners, and fenced-mode processes for user code. Multiple instances can coexist on a single server.

Buffer pools are the single most important performance feature. They cache data and index pages in memory, avoiding expensive physical I/O. A well-tuned OLTP system achieves a 98%+ buffer pool hit ratio. Multiple buffer pools allow workload isolation and independent tuning.

Memory management extends beyond buffer pools to include the EDM pool (z/OS) or package/catalog caches (LUW), RID pool, sort pool, lock structures, and more. LUW's STMM provides automated memory tuning.

Storage is organized through tablespaces, which map to VSAM data sets (z/OS) or file containers (LUW). Modern DB2 recommends Universal Table Spaces (z/OS) and automatic storage (LUW).

Write-ahead logging ensures both performance and reliability. Log records are written before data pages, enabling fast commits while guaranteeing crash recovery. Both platforms support dual/archive logging for production resilience.

The SQL lifecycle traverses connection management, parsing, optimization, lock acquisition, buffer pool access, and result return — a multi-component pipeline that completes in milliseconds when properly tuned.

The catalog stores all metadata and is essential for both database management and optimizer performance. Regular RUNSTATS collection keeps the optimizer making good decisions.

In Chapter 4, we will set up actual DB2 environments — both z/OS and LUW — and begin creating the Meridian Bank database for real.

Key Terms