Chapter 14: Key Takeaways

The Ten Commandments of Physical Database Design

1. Physical Design Is Where Performance Is Born or Killed

A perfectly normalized logical model in the wrong tablespace type, with the wrong page size, without compression, and without partitioning will underperform — and no amount of SQL tuning will fix it. Get the physical design right first.

2. Use Universal Table Spaces on z/OS

Segmented and classic partitioned tablespaces are legacy. Universal Table Spaces (UTS) in either Partition-by-Growth (PBG) or Partition-by-Range (PBR) mode are the modern standard. PBG for unpredictable growth; PBR for large tables needing partition-level operations.

3. Use Automatic Storage on LUW

Let DB2 manage container creation, sizing, and growth. Define storage paths at the database level, use storage groups for tiered placement, and set MAXSIZE as a safety valve. Manual DMS management is rarely justified for new databases.

4. Range-Partition Large Tables by Date

Date-based range partitioning is the dominant strategy in transaction processing because it aligns with query patterns (date-range filters), data lifecycle (archival by age), and maintenance windows (monthly operations). If your table exceeds 10 GB and has a date column, partition it by date.

5. Enable Compression on Large Tables

For tables larger than a few thousand pages, compression almost always pays for itself. Dictionary-based row compression typically saves 50-70% of storage, reduces I/O operations proportionally, and improves buffer pool hit ratios. The 2-5% CPU overhead is offset by I/O savings in all but the most CPU-saturated environments.

6. Choose Page Size Based on Row Length and Access Pattern

• 4 KB: Short rows (< 200 bytes), small reference tables.
• 8 KB: Medium rows (200-500 bytes), general-purpose OLTP.
• 16 KB: Longer rows (500-1,500 bytes), mixed workloads.
• 32 KB: Very long rows, LOB tablespaces (mandatory on z/OS), sort-heavy workloads.

7. Set PCTFREE Based on Update Behavior, Not Convention

• Insert-only tables (transaction logs, audit trails): PCTFREE 0.
• Light-update tables (occasional address changes): PCTFREE 5-10.
• Heavy-update tables (growing VARCHARs, frequent modifications): PCTFREE 15-20.
• Never use PCTFREE 10 as a universal default without analyzing the workload.

8. Separate Hot and Cold Data

Put active partitions and frequently accessed tables on fast storage (SSD/NVMe). Put archival partitions on cost-effective storage (HDD). Use storage groups (z/OS STOGROUP, LUW STOGROUP) to map tablespaces to physical storage tiers.

9. Plan Capacity with Precision — Then Add Margin

Calculate storage requirements using: row size, rows per page, PCTFREE, page overhead, index overhead (typically 20-30% of data), LOB storage, and multi-year growth rates. Present 5-year projections. Add 25-50% margin for the unexpected. Running out of space in production is a career-defining event.

10. Document Every Decision

Create a physical design decision log. For each decision (page size, tablespace type, partitioning strategy, compression, storage placement), record: the choice, the rationale, alternatives considered, and assumptions. Future DBAs will inherit your design — give them the context they need.

Quick Reference: Physical Design Decision Matrix

The Numbers That Matter