Chapter 3 Quiz: DB2 Architecture
Test your understanding of DB2 architecture concepts. Try to answer each question before revealing the answer.
Question 1
On z/OS, which DB2 address space is responsible for buffer pool management and SQL compilation?
- A) MSTR
- B) DBM1
- C) DIST
- D) IRLM
Answer
**B) DBM1** — The Database Services address space (DBM1) handles SQL compilation, optimization, buffer pool management, and all data access operations. MSTR handles logging and system services. DIST handles remote connections. IRLM handles locking.Question 2
Which of the following is the FIRST address space started when DB2 on z/OS is initialized?
- A) DBM1
- B) DIST
- C) MSTR
- D) IRLM
Answer
**C) MSTR** — The System Services address space is the first started and the last stopped. It must be running before the other DB2 address spaces can initialize.Question 3
In DB2 for LUW, what is the purpose of the db2fmp process?
- A) To manage the buffer pool page cleaners
- B) To execute user-written code (stored procedures, UDFs) in an isolated process
- C) To coordinate communication between database partitions
- D) To write log records to the active log files
Answer
**B)** — `db2fmp` is the Fenced Mode Process. It runs user-written code (stored procedures, UDFs) in a separate process so that a crash in user code does not bring down the DB2 engine.Question 4
A buffer pool has the following statistics: 10,000,000 logical reads and 150,000 physical reads. What is the buffer pool hit ratio?
- A) 1.5%
- B) 85.0%
- C) 98.5%
- D) 99.85%
Answer
**C) 98.5%** — BPHR = (1 - 150,000 / 10,000,000) * 100 = (1 - 0.015) * 100 = 98.5%. This means 98.5% of page requests were satisfied from the buffer pool without disk I/O.Question 5
Which page size is NOT supported by DB2 buffer pools?
- A) 4 KB
- B) 8 KB
- C) 12 KB
- D) 32 KB
Answer
**C) 12 KB** — DB2 supports 4K, 8K, 16K, and 32K page sizes only. There is no 12K option.Question 6
What is the primary reason for using multiple buffer pools instead of a single large buffer pool?
- A) DB2 requires at least four buffer pools to function
- B) Multiple buffer pools use less total memory than a single pool
- C) Workload isolation — preventing one type of access from evicting another's cached pages
- D) Multiple buffer pools are required for different page sizes only
Answer
**C)** — The primary reason is workload isolation. A batch scan on one table can push out frequently accessed pages for another workload (buffer pool pollution). Separate pools prevent this. While different page sizes also require separate pools (D), that is a technical requirement, not the primary design motivation.Question 7
On z/OS, the EDM pool caches which of the following? (Select all that apply.)
- A) Data pages from tablespaces
- B) Compiled SQL plans and packages
- C) Database descriptors (DBDs)
- D) Dynamic SQL statement cache
- E) Index pages
Answer
**B, C, and D** — The EDM pool caches compiled plans/packages, database descriptors, and the dynamic statement cache. Data pages (A) and index pages (E) are cached in buffer pools, not the EDM pool.Question 8
What does the Self-Tuning Memory Manager (STMM) do in DB2 LUW?
- A) Automatically tunes SQL queries for better performance
- B) Automatically adjusts the sizes of memory areas based on workload demand
- C) Automatically adds more physical RAM to the server when needed
- D) Automatically restarts DB2 when memory errors occur
Answer
**B)** — STMM monitors memory demand across buffer pools, sort heaps, lock lists, package cache, and other memory areas, and redistributes memory from under-utilized areas to areas under pressure. It does not tune SQL (A), add physical RAM (C), or restart DB2 (D).Question 9
What type of tablespace does IBM recommend for new development on z/OS?
- A) Simple tablespace
- B) Segmented tablespace
- C) Universal Table Space (UTS)
- D) Partitioned tablespace (classic)
Answer
**C) Universal Table Space (UTS)** — UTS is the modern standard, available in partition-by-range (PBR) and partition-by-growth (PBG) variants. Simple tablespaces are deprecated. Segmented tablespaces are legacy. Classic partitioned tablespaces are superseded by PBR UTS.Question 10
Write-ahead logging (WAL) guarantees which of the following?
- A) Data pages are always written to disk before the transaction commits
- B) Log records describing changes are written to disk before the changed data pages are written to disk
- C) Archive logs are created before active logs are reused
- D) The BSDS is updated before any log record is written
Answer
**B)** — WAL requires that log records be written to persistent storage BEFORE the dirty data pages they describe. This ensures that in a crash, DB2 can always recover by reading the log. Option A describes a "force" policy, which is the OPPOSITE of what DB2 does. Options C and D are separate mechanisms.Question 11
On z/OS, the BSDS (Bootstrap Data Set) contains:
- A) All customer data for the database
- B) An inventory of log data sets, checkpoint records, and the DDF communication record
- C) Compiled SQL packages and plans
- D) Buffer pool configuration parameters
Answer
**B)** — The BSDS is DB2's "table of contents" for the logging system. It maps log RBA ranges to data set names, stores checkpoint records, and contains the DDF location information. Customer data is in tablespaces (A). Plans are in the directory/EDM pool (C). Buffer pool config is in ZPARMs (D).Question 12
A DB2 LUW database is configured with circular logging (the default). Which of the following recovery operations is NOT possible?
- A) Crash recovery (automatic restart after a crash)
- B) Transaction rollback (ROLLBACK statement)
- C) Point-in-time recovery (ROLLFORWARD DATABASE to a specific timestamp)
- D) Version recovery (RESTORE DATABASE from a backup)
Answer
**C)** — Point-in-time recovery requires archive logging because it needs to replay log records from a backup to a specific point in time. With circular logging, old log records are overwritten and unavailable. Crash recovery (A), rollback (B), and version recovery from backup (D) all work with circular logging.Question 13
During the SQL lifecycle, at which phase does the optimizer choose between an index scan and a table scan?
- A) Parsing (syntax check)
- B) Semantic validation
- C) Access path selection (optimization)
- D) Data retrieval (execution)
Answer
**C) Access path selection** — The optimizer evaluates different access strategies, estimates their costs using catalog statistics, and chooses the least expensive plan. This happens after parsing (A) and validation (B), and before execution (D).Question 14
Which catalog view on DB2 LUW would you query to find the number of rows in a table?
- A) SYSCAT.COLUMNS
- B) SYSCAT.INDEXES
- C) SYSCAT.TABLES
- D) SYSCAT.TABLESPACES
Answer
**C) SYSCAT.TABLES** — The `CARD` column in SYSCAT.TABLES contains the cardinality (row count) as recorded by the last RUNSTATS execution. On z/OS, the equivalent is the `CARDF` column in SYSIBM.SYSTABLES.Question 15
What happens when the RID pool is too small on z/OS?
- A) DB2 switches to row-level locking
- B) DB2 falls back to less efficient access methods, often a tablespace scan
- C) DB2 allocates additional memory from the sort pool
- D) DB2 writes RIDs to a temporary tablespace on disk
Answer
**B)** — When the RID pool is exhausted, DB2 cannot perform RID list processing or multi-index access. It falls back to a tablespace scan or a less efficient single-index access path. This is called RID pool failure and can cause significant performance degradation.Question 16
In a DB2 LUW Database Partition Feature (DPF) environment, data is distributed across partitions using:
- A) Round-robin distribution
- B) A distribution key (hash partitioning)
- C) Date-based range partitioning only
- D) Random assignment
Answer
**B)** — DPF uses a distribution key. DB2 applies a hashing function to the distribution key column(s) to determine which partition each row belongs to. This enables parallel processing where each partition handles its local data.Question 17
A z/OS DB2 subsystem named DB2P has four main address spaces. What is the name of the address space that handles remote JDBC connections?
- A) DB2PMSTR
- B) DB2PDBM1
- C) DB2PDIST
- D) DB2PIRLM
Answer
**C) DB2PDIST** — The DDF (Distributed Data Facility) address space handles all remote connections, including JDBC over TCP/IP. The naming convention is the four-character subsystem ID followed by the address space type.Question 18
Which of the following statements about DB2 LUW instances is TRUE?
- A) A server can have only one DB2 instance
- B) An instance can contain databases from different DB2 versions
- C) Multiple independent instances can coexist on a single server
- D) An instance is created automatically when DB2 is installed
Answer
**C)** — Multiple instances can coexist on a single server, each with its own port, configuration, and set of databases. This is useful for separating environments or running different workloads. A default instance IS created during installation on most platforms, but additional instances must be explicitly created with `db2icrt`.Question 19
Why is it critical to run RUNSTATS regularly on DB2 tables?
- A) RUNSTATS defragments table data pages
- B) RUNSTATS updates catalog statistics used by the optimizer for access path decisions
- C) RUNSTATS compresses table data to save disk space
- D) RUNSTATS rebuilds indexes to maintain their efficiency
Answer
**B)** — RUNSTATS collects statistics about tables (row count, data distribution, etc.) and indexes (number of levels, leaf pages, etc.) and updates the catalog. The optimizer uses these statistics to estimate the cost of different access paths. Stale statistics lead to poor optimizer decisions. REORG defragments data (A). COMPRESS handles compression (C). REBUILD INDEX or REORG INDEX handles index maintenance (D).Question 20
In the steal/no-force buffer pool policy used by DB2, "no-force" means:
- A) DB2 never writes dirty pages to disk
- B) Dirty pages do NOT have to be written to disk at commit time
- C) DB2 does not force applications to use buffer pools
- D) Log records are not forced to disk at commit time
Answer
**B)** — "No-force" means DB2 does not require dirty data pages to be written to disk when a transaction commits. The commit is acknowledged after the log records are forced to disk (ensuring recoverability via WAL). The dirty data pages are written later by page cleaners. This policy maximizes performance because it avoids synchronous data page writes at commit time.Question 21
A new DBA sees that the buffer pool for the TRANSACTION tablespace has a hit ratio of only 40%. Before increasing the buffer pool size, what should they investigate FIRST?
- A) Whether the server needs more physical RAM
- B) Whether the workload pattern actually benefits from caching (e.g., large sequential scans may naturally have low hit ratios)
- C) Whether to switch to 32K pages
- D) Whether to drop all indexes on the TRANSACTION table
Answer
**B)** — A 40% hit ratio might be expected and acceptable if the workload involves large sequential scans of the TRANSACTION table (e.g., batch reporting). Throwing more memory at a sequential scan only helps if the entire table can fit in the buffer pool. The DBA should understand the workload pattern before allocating more resources. If the workload is truly scan-heavy, the solution might be workload isolation (separate buffer pool) rather than a larger pool.Question 22
On z/OS, which address space would be affected if there is a sudden spike in deadlock occurrences?
- A) MSTR
- B) DBM1
- C) DIST
- D) IRLM
Answer
**D) IRLM** — The Internal Resource Lock Manager handles all lock management including deadlock detection. A spike in deadlocks increases IRLM workload. However, the performance impact would also be felt in DBM1 (where the SQL waits for locks) and potentially in the applications.Question 23
What is the relationship between a tablespace and a buffer pool?
- A) A tablespace can be assigned to multiple buffer pools simultaneously
- B) A buffer pool can contain pages from only one tablespace
- C) Each tablespace is assigned to exactly one buffer pool, and the page sizes must match
- D) Buffer pools and tablespaces are independent and unrelated
Answer
**C)** — Each tablespace is assigned to exactly one buffer pool, and the page size of the tablespace must match the page size of the buffer pool. However, a single buffer pool can serve multiple tablespaces (they share the pool). A tablespace cannot span multiple buffer pools.Question 24
Which of the following correctly describes the checkpoint process?
- A) All dirty buffer pool pages are written to disk at every checkpoint
- B) A checkpoint records the current log position to limit the amount of log processed during recovery
- C) Checkpoints create a full backup of the database
- D) Checkpoints close and reopen all active log data sets
Answer
**B)** — A checkpoint records a synchronization point in the log. During recovery, DB2 starts processing from the last checkpoint position rather than the beginning of the log. This limits recovery time. Checkpoints do NOT force all dirty pages to disk (A) — that would be too expensive. They are not backups (C) and do not affect log data set allocation (D).Question 25
You are designing a DB2 architecture for Meridian Bank. The TRANSACTION table has 50 million rows and grows daily. Most queries include a date filter. Which of the following architectural decisions would provide the MOST benefit?
- A) Using a 32K page size for the TRANSACTION tablespace
- B) Placing the TRANSACTION table in the default buffer pool
- C) Using a partition-by-range tablespace partitioned by TRANS_DATE
- D) Disabling logging for the TRANSACTION tablespace to improve insert performance