Chapter 30 Key Takeaways

Core Concepts

  1. Partitioning transforms manageability: A partitioned table is a collection of independently manageable pieces. REORG, BACKUP, RECOVER, and RUNSTATS can target a single partition, reducing maintenance windows from hours to minutes.

  2. The partition key is the most important design decision: Choose a column that appears in the majority of WHERE clauses (typically a date column for transactional tables). The partition key determines whether partition elimination can occur.

  3. Partition elimination is the primary performance benefit: When the optimizer can exclude irrelevant partitions based on WHERE clause predicates, query I/O is reduced proportionally to the number of eliminated partitions. A query against 1 of 84 partitions scans ~1.2% of the data.

  4. Functions on the partition key defeat elimination: Never apply functions (YEAR(), CAST(), SUBSTR()) to the partition key in WHERE clauses. Use range predicates on the raw column instead.

  5. The rolling window pattern replaces DELETE with DDL: Detach the oldest partition and attach a new one — seconds of metadata operations replace hours of DELETE, log generation, and index maintenance.

Platform-Specific Points

z/OS

  • ENDING AT defines the limit key (upper boundary) for each partition.
  • ROTATE PARTITION FIRST TO LAST implements the rolling window in a single DDL statement.
  • DPSI (Data-Partitioned Secondary Index) preserves partition independence at the cost of multi-probe scans.
  • NPSI (Non-Partitioned Secondary Index) provides fast single-probe lookups but breaks partition independence.
  • DSSIZE controls the maximum size of each partition's VSAM data set. Size it for growth headroom.

LUW

  • STARTING / ENDING with INCLUSIVE/EXCLUSIVE define explicit partition boundaries.
  • DETACH PARTITION converts a partition into a standalone table (no data movement).
  • ATTACH PARTITION adds an existing table as a partition, followed by SET INTEGRITY validation.
  • DPF (Database Partitioning Feature) distributes data across database partitions using hash distribution — orthogonal to range partitioning.
  • DISTRIBUTE BY HASH assigns rows to database partitions; PARTITION BY RANGE assigns rows to table partitions within each database partition.

Design Guidelines

Guideline Recommendation
Partition key Date/timestamp column for transactional tables
Granularity Monthly for 3-10 year retention; daily only for very high volume with short retention
Total partition count Keep under 500 for operational simplicity
Individual partition size 1-50 GB on z/OS; 1-100 GB on LUW
Primary key Include the partition key to enable partitioned unique index
Secondary indexes Prefer DPSI (z/OS) / partitioned indexes (LUW) for partition independence
Rolling window Automate monthly rotation with monitoring and alerting
Monitoring Track partition sizes and flag imbalances exceeding 3:1 ratio

Anti-Patterns to Avoid

  1. Too many partitions: Daily partitions for 10-year retention = 3,650 partitions. Use monthly instead.
  2. Wrong partition key: Choosing a column not used in most queries eliminates the performance benefit.
  3. Functions on partition key: WHERE YEAR(trans_date) = 2026 defeats elimination. Use WHERE trans_date BETWEEN ....
  4. Unbalanced partitions: A catch-all first partition or seasonal skew can create hotspots.
  5. Too many global indexes: Each DETACH/ATTACH must maintain all global (non-partitioned) indexes.

Meridian Bank Application

Monthly range partitioning on TRANS_DATE with an 84-partition, 7-year rolling window delivers: - Partition elimination for 80% of queries (date-filtered). - 12-minute partition-level REORG vs. multi-hour full-table REORG. - Seconds-long monthly archival via ROTATE (z/OS) or DETACH/ATTACH (LUW). - Independent recovery of any single month's data within minutes.