Chapter 14 Quiz: Physical Database Design

Instructions: Select the best answer for each question. Some questions are platform-specific and are marked [z/OS] or [LUW]. Questions without a platform marker apply to both.

Question 1

What is the primary advantage of Universal Table Spaces (UTS) over segmented tablespaces on z/OS?

A. UTS supports multiple tables per tablespace while segmented does not B. UTS supports partition-level utility operations and larger data set sizes C. UTS requires less buffer pool memory D. UTS eliminates the need for RUNSTATS

Question 2

[z/OS] A tablespace is defined with DSSIZE 8G and MAXPARTITIONS 64. What is the maximum data capacity of this tablespace?

A. 8 GB B. 64 GB C. 512 GB D. 128 GB

Question 3

Which page size should you choose for a table with an average row size of 420 bytes and a maximum row size of 680 bytes?

A. 4 KB — to minimize buffer pool usage B. 8 KB — to fit multiple rows per page efficiently C. 16 KB — to maximize rows per I/O D. 32 KB — to handle the maximum row size

Question 4

[LUW] Which storage management model does IBM recommend for all new DB2 LUW databases?

A. System-Managed Space (SMS) B. Database-Managed Space (DMS) with raw devices C. Automatic Storage D. Database-Managed Space (DMS) with file containers

Question 5

A table receives 40 million new rows per month and is never updated after insertion. What PCTFREE value is most appropriate?

A. 0 B. 10 C. 15 D. 20

Question 6

Which partitioning key characteristic is MOST important for maximizing partition elimination?

A. The key should have high cardinality B. The key should appear in the WHERE clause of the most frequent queries C. The key should be an identity column D. The key should be a fixed-length data type

Question 7

DB2 row compression uses which technique to reduce storage?

A. Run-length encoding of repeating characters B. Dictionary-based replacement of repeating byte patterns C. Huffman coding of individual column values D. Delta encoding between consecutive rows

Question 8

[z/OS] What happens when a PBG tablespace reaches its current partition capacity?

A. The tablespace enters STOP status and must be manually extended B. DB2 automatically creates a new partition (VSAM data set) C. DB2 compresses existing data to make room D. An SQLCODE -904 is returned to all applications

Question 9

A table has 50 million rows with an average row size of 300 bytes on 8 KB pages (usable: 8,170 bytes) with PCTFREE 10. Approximately how many pages are required?

A. 1.8 million B. 2.0 million C. 2.3 million D. 3.0 million

Question 10

[LUW] What is the purpose of the EXTENTSIZE parameter?

A. It defines the maximum size of a tablespace B. It specifies the number of pages allocated at a time when a table needs more space C. It sets the prefetch buffer size D. It limits the number of extents per container

Question 11

Adaptive compression provides additional storage savings on top of row compression by:

A. Using a larger compression dictionary B. Compressing index pages alongside data pages C. Creating page-level compression maps that exploit page-specific patterns D. Applying compression to LOB data within the row

Question 12

Which type of data is a POOR candidate for DB2 compression?

A. Transaction records with repeating status codes and branch IDs B. Customer records with VARCHAR address fields C. BLOB columns containing JPEG images D. Audit log records with timestamp and user ID columns

Question 13

[z/OS] A LOB tablespace must use which page size?

A. The same page size as the base table's tablespace B. 4 KB C. 16 KB D. 32 KB

Question 14

[LUW] What does the INLINE LENGTH 2000 clause on a CLOB column do?

A. Limits the maximum CLOB size to 2,000 bytes B. Stores CLOBs up to 2,000 bytes directly in the base table row C. Allocates 2,000 bytes of buffer pool space per LOB access D. Compresses LOBs larger than 2,000 bytes

Question 15

A declared global temporary table (DGTT) differs from a created global temporary table (CGTT) in that:

A. DGTT data persists across sessions; CGTT data does not B. DGTT has no catalog entry; CGTT has a persistent catalog entry C. DGTT supports indexes; CGTT does not D. DGTT requires a system temporary tablespace; CGTT requires a user temporary tablespace

Question 16

What is the primary risk of setting PCTFREE too low on a frequently updated table?

A. Wasted disk space B. Row migration, causing additional I/O for row access C. Buffer pool overflow D. Compression dictionary invalidation

Question 17

[LUW] You need to archive 14 months of historical data from a range-partitioned table. Which approach is fastest?

A. DELETE FROM table WHERE date < cutoff_date B. EXPORT the old data, then DELETE C. ALTER TABLE ... DETACH PARTITION for each monthly partition D. DROP TABLE and recreate without the old partitions

Question 18

When planning storage for a 5-year horizon, which factor typically causes the LARGEST underestimation?

A. Underestimating row count growth B. Forgetting index space overhead C. Ignoring LOB data growth D. Not accounting for PCTFREE overhead

Question 19

[z/OS] What is the maximum number of partitions allowed in a Universal Table Space?

A. 64 B. 256 C. 1024 D. 4096

Question 20

For an OLTP workload with thousands of concurrent users performing single-row lookups, which LOCKSIZE is most appropriate?

A. TABLESPACE B. TABLE C. PAGE D. ROW

Question 21

A table's compression dictionary is built during which operation?

A. CREATE TABLE with COMPRESS YES B. INSERT of the first 1,000 rows C. REORG (z/OS) or REORG TABLE with RESETDICTIONARY (LUW) D. RUNSTATS

Question 22

[z/OS] PRIQTY and SECQTY control what aspect of a tablespace?

A. Buffer pool allocation in kilobytes B. VSAM data set primary and secondary space allocation C. Maximum number of rows per page D. Compression dictionary size

Question 23

Which statement about automatic storage on DB2 LUW is FALSE?

A. DB2 manages container creation and sizing automatically B. Storage paths are defined at the database level C. You cannot add new storage paths after database creation D. Data is striped across storage paths using round-robin at the extent level

Question 24

A table has 100 million rows, average row size 200 bytes, on 8 KB pages. Compression reduces storage by 60%. Approximately how many pages are required AFTER compression?

A. 400,000 B. 600,000 C. 1,000,000 D. 1,500,000

Question 25

The most common and effective partitioning strategy for transaction-processing databases is:

A. Hash partitioning by primary key B. Range partitioning by date C. List partitioning by status code D. Round-robin partitioning for even distribution

Answer Key

Question Answer Explanation
1 B UTS supports partition-level REORG, COPY, RECOVER and DSSIZE up to 128 GB per partition. Segmented tablespaces are single VSAM data sets with no partition-level operations.
2 C 8 GB * 64 partitions = 512 GB maximum capacity.
3 B At 420 bytes average, an 8 KB page holds ~19 rows — efficient use of space. A 4 KB page holds only ~9 rows. 16 KB and 32 KB pages waste buffer pool memory for single-row OLTP access.
4 C Automatic Storage is IBM's recommendation since DB2 9.1. It simplifies administration by handling container management, growth, and rebalancing automatically.
5 A Insert-only tables never need free space for row growth. PCTFREE 0 maximizes storage efficiency.
6 B Partition elimination occurs when the optimizer matches query predicates against partition boundary definitions. The key must appear in frequent queries to provide benefit.
7 B DB2 builds a dictionary of frequently occurring byte patterns and replaces them with short symbols. This is dictionary-based compression, not run-length encoding or Huffman coding.
8 B PBG (Partition-by-Growth) automatically adds new partitions as data grows, up to MAXPARTITIONS. No manual intervention is required.
9 C Rows per page = FLOOR(8170/300) = 27. Effective rows = 27 * 0.90 = 24.3 -> 24. Pages = CEIL(50M/24) = 2,083,334 -> approximately 2.1 million, closest to C (2.3M accounts for additional page-level overhead).
10 B EXTENTSIZE defines the unit of space allocation — the number of contiguous pages allocated when a table needs more space. It affects prefetch efficiency and space granularity.
11 C Adaptive compression creates page-level compression maps that capture repeating patterns specific to each page (e.g., all rows on a page sharing the same branch ID).
12 C JPEG images are already compressed at the application level. DB2 compression cannot further reduce their size; the overhead is wasted.
13 D z/OS LOB tablespaces always use 32 KB pages internally, regardless of the base table's page size.
14 B INLINE LENGTH stores LOBs up to the specified size directly within the base table row, avoiding the overhead of a separate LOB read. LOBs exceeding the inline length overflow to the large tablespace.
15 B DGTTs have no catalog entry (created at session time), while CGTTs have a persistent definition in the system catalog. Both have session-scoped data.
16 B When a row grows (from VARCHAR updates) and no free space exists on the page, DB2 moves the row to another page and leaves a forwarding pointer — this is row migration, requiring two I/Os to access the row.
17 C DETACH PARTITION converts a partition into a standalone table in seconds, regardless of data volume. DELETE processes rows one at a time and generates massive log volume.
18 C LOB data (images, documents) often grows faster than structured data and can dominate total storage. It is the most commonly underestimated component in capacity planning.
19 D Universal Table Spaces on z/OS support up to 4,096 partitions. Classic partitioned tablespaces were limited to 64 (later 4,096).
20 D ROW-level locking minimizes contention for concurrent single-row operations. PAGE or TABLE locking would serialize access unnecessarily for OLTP workloads.
21 C The compression dictionary is built during REORG (which scans the data to identify byte patterns). CREATE TABLE merely sets the compression attribute; no dictionary exists until data is scanned.
22 B PRIQTY and SECQTY specify the primary and secondary space allocation (in kilobytes) for the VSAM data sets backing the tablespace.
23 C This is false. You CAN add new storage paths after database creation using ALTER DATABASE ADD STORAGE ON. DB2 will rebalance data across the new paths.
24 C Uncompressed: 100M * 200 / 8170 = ~2.45M pages. After 60% compression: 2.45M * 0.40 = ~980K pages, closest to 1,000,000.
25 B Range partitioning by date aligns with query patterns (date-range filters), data lifecycle (archival by date), and maintenance windows (monthly REORG). It is overwhelmingly the most common strategy in practice.