Chapter 30: Partitioning Strategies — Distributing Data for Scale and Manageability

"The table that cannot be partitioned will, sooner or later, become the table that cannot be maintained." — Overheard at an IBM DB2 Technical Conference, 2014

Learning Objectives

After completing this chapter you will be able to:

1. Implement range partitioning for large tables on both z/OS and LUW platforms.
2. Design partition key selection strategies that achieve optimal partition elimination.
3. Apply rolling window patterns for time-series data lifecycle management.
4. Understand hash partitioning and the Database Partitioning Feature (DPF) on LUW.
5. Leverage partition independence to perform maintenance without full-table impact.
6. Partition the Meridian National Bank transaction history table using a production-ready design.

30.1 Why Partition?

There comes a moment in the life of every large database when a single, monolithic table becomes an operational burden. At Meridian National Bank, that moment arrived when the TRANSACTION_HISTORY table crossed the 2-billion-row mark. Full-table REORG operations consumed an entire weekend maintenance window. Image copies took hours. A single corrupted page in a 400 GB table space meant recovering the entire object. The query optimizer, faced with scanning an ever-growing index tree, produced plans that degraded quarter after quarter.

Partitioning is the answer to all four of these pressures — and several more besides.

30.1.1 Manageability

A partitioned table is, from the perspective of the storage subsystem and the utility infrastructure, a collection of smaller, independent pieces. On z/OS, each partition resides in its own data set. On LUW, each partition occupies its own data object within the table space. This independence is the foundation of every manageability benefit:

• REORG can operate on a single partition while the rest of the table remains available for read and write operations.
• BACKUP (or COPY on z/OS) can target a single partition, dramatically reducing the volume of data that must move to tape or disk.
• RECOVER can restore a single partition from its most recent image copy without touching the remaining partitions.
• RUNSTATS can refresh statistics for a single partition, particularly useful after a bulk load that affected only the newest partition.

For a table with 84 monthly partitions spanning seven years, the ability to maintain one partition at a time reduces the operational window by roughly two orders of magnitude compared to full-table operations.

30.1.2 Performance

Partitioning delivers performance benefits primarily through partition elimination (also called partition pruning). When the optimizer can determine from the WHERE clause that only a subset of partitions contains qualifying rows, it skips the remaining partitions entirely. For a table with 84 monthly partitions, a query against the current month's data scans roughly 1/84th of the total data — a dramatic reduction in I/O.

Secondary performance benefits include:

• Parallel I/O across partitions during queries that must touch multiple partitions.
• Smaller indexes per partition (when using partitioned indexes), which reduces index tree depth and improves access time.
• Reduced contention because concurrent transactions targeting different partitions operate against different physical storage objects.

30.1.3 Availability

Partition independence means that a failure in one partition does not render the entire table unavailable. If partition 47 (representing March 2023 data) suffers a media failure, all other partitions remain accessible. Applications querying data outside the affected month continue to operate normally while recovery proceeds.

On z/OS, this independence extends to the DBAT (Database Access Thread) level: a thread accessing partition 1 holds no claim on partition 84. On LUW with DPF, partitions can reside on entirely different physical servers, providing an additional layer of fault isolation.

30.1.4 Archival and Data Lifecycle

Partitioning aligns naturally with data retention policies. Meridian National Bank must retain seven years of transaction history for regulatory compliance but rarely queries data older than 90 days for operational purposes. With monthly partitioning, the archival workflow becomes:

1. Detach the oldest partition (month 85 from the start).
2. Archive the detached partition's data to cold storage or a separate archive table.
3. Drop the detached partition or its underlying storage.
4. Attach a new empty partition for the upcoming month.

This "rolling window" pattern eliminates the need for DELETE operations — which generate enormous volumes of log data and trigger cascading index maintenance — replacing them with metadata-only DDL operations that complete in seconds.

30.2 Range Partitioning on z/OS

DB2 for z/OS has supported range partitioning since the earliest versions of the product, predating the relational standard's partitioning extensions by decades. The z/OS implementation is deeply integrated with the VSAM storage layer and the DB2 utility infrastructure.

30.2.1 Partition-by-Range Universal Table Spaces

Since DB2 12 for z/OS, IBM recommends partition-by-range universal table spaces (UTS) as the standard for partitioned tables. The older partitioned table space (classic partitioning) is deprecated, and new objects should always use the UTS model.

A partition-by-range UTS is created implicitly when you define a table with PARTITION BY RANGE:

CREATE TABLE MERIDIAN.TRANSACTION_HISTORY (
    TRANS_ID          BIGINT        NOT NULL GENERATED ALWAYS AS IDENTITY,
    ACCOUNT_ID        BIGINT        NOT NULL,
    TRANS_DATE        DATE          NOT NULL,
    TRANS_TYPE        CHAR(3)       NOT NULL,
    AMOUNT            DECIMAL(15,2) NOT NULL,
    CURRENCY_CODE     CHAR(3)       NOT NULL DEFAULT 'USD',
    BRANCH_ID         INTEGER,
    TELLER_ID         INTEGER,
    DESCRIPTION       VARCHAR(200),
    CREATED_TIMESTAMP TIMESTAMP     NOT NULL WITH DEFAULT
)
IN MERIDIANDB.TSHIST
PARTITION BY RANGE (TRANS_DATE)
(
    PARTITION 1  ENDING AT ('2019-12-31'),
    PARTITION 2  ENDING AT ('2020-01-31'),
    PARTITION 3  ENDING AT ('2020-02-29'),
    PARTITION 4  ENDING AT ('2020-03-31'),
    -- ... additional partitions ...
    PARTITION 84 ENDING AT ('2026-11-30')
);

30.2.2 The ENDING AT Clause and Limit Keys

Each partition is defined by its limit key — the highest value that the partition can contain. The ENDING AT clause specifies this upper boundary. DB2 assigns rows to partitions based on the partitioning key value:

• A row with TRANS_DATE = '2020-02-15' is placed in Partition 3 (which ends at '2020-02-29').
• A row with TRANS_DATE = '2020-03-01' is placed in Partition 4 (which ends at '2020-03-31').

Critical rule: Limit keys must be specified in ascending order. DB2 will reject a CREATE TABLE or ALTER TABLE ADD PARTITION statement that violates this ordering.

For the first partition, the implicit lower bound is the lowest possible value of the partitioning column's data type. For DATE columns, this is '0001-01-01'. Any row with a TRANS_DATE value less than or equal to '2019-12-31' will be assigned to Partition 1, including very old dates that might result from data conversion errors.

Production tip: Always define Partition 1 with a meaningful starting range, and consider adding a "catch-all" first partition for unexpected values:

PARTITION 1  ENDING AT ('2018-12-31'),  -- Catch-all for pre-history
PARTITION 2  ENDING AT ('2019-01-31'),  -- First real month

30.2.3 DSSIZE and Partition Sizing

Each partition in a partition-by-range UTS is backed by a separate VSAM linear data set. The DSSIZE parameter controls the maximum size of each partition's data set:

CREATE TABLESPACE TSHIST
    IN MERIDIANDB
    USING STOGROUP SYSDEFLT
    DSSIZE 16G
    BUFFERPOOL BP32K
    SEGSIZE 64
    MAXPARTITIONS 128;

Common DSSIZE values and their implications:

With DB2 12 for z/OS, MAXPARTITIONS can be as high as 4096, and DSSIZE can reach 256 GB, allowing a single partitioned table to hold petabytes of data.

30.2.4 Partition-Level Operations on z/OS

The z/OS utility infrastructure is deeply aware of partitions. Every major utility supports a PART keyword:

REORG TABLESPACE MERIDIANDB.TSHIST PART 84
COPY  TABLESPACE MERIDIANDB.TSHIST PART 84 FULL YES
RUNSTATS TABLESPACE MERIDIANDB.TSHIST PART 84
         TABLE(MERIDIAN.TRANSACTION_HISTORY)
         INDEX(ALL)
RECOVER TABLESPACE MERIDIANDB.TSHIST PART 47

These partition-level operations hold locks only on the specified partition. Other partitions remain fully available for read and write operations. This is the single most important operational benefit of partitioning on z/OS.

30.2.5 Adding and Rotating Partitions on z/OS

To add a new partition at the end of the range:

ALTER TABLE MERIDIAN.TRANSACTION_HISTORY
    ADD PARTITION ENDING AT ('2026-12-31');

To rotate a partition (remove the oldest, add a new one), z/OS uses a combination of ALTER TABLE ... ROTATE PARTITION:

ALTER TABLE MERIDIAN.TRANSACTION_HISTORY
    ROTATE PARTITION FIRST TO LAST
    ENDING AT ('2027-01-31');

The ROTATE PARTITION FIRST TO LAST operation: 1. Logically detaches the first partition's data. 2. Empties the partition. 3. Moves the now-empty partition to the last position with a new limit key.

This is a metadata-only operation — the data in the rotated partition is discarded. If you need to preserve the data before rotation, you must first copy it to an archive table or unload it.

30.3 Table Partitioning on LUW

DB2 LUW introduced table partitioning (also called range partitioning) in version 9.1. The LUW syntax is similar to z/OS but includes additional features such as hash partitioning and the ATTACH/DETACH operations.

30.3.1 Range Partitioning Syntax

CREATE TABLE meridian.transaction_history (
    trans_id          BIGINT        NOT NULL GENERATED ALWAYS AS IDENTITY,
    account_id        BIGINT        NOT NULL,
    trans_date        DATE          NOT NULL,
    trans_type        CHAR(3)       NOT NULL,
    amount            DECIMAL(15,2) NOT NULL,
    currency_code     CHAR(3)       NOT NULL DEFAULT 'USD',
    branch_id         INTEGER,
    teller_id         INTEGER,
    description       VARCHAR(200),
    created_timestamp TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP
)
PARTITION BY RANGE (trans_date)
(
    PARTITION p_2019    STARTING ('2019-01-01') ENDING ('2019-12-31') INCLUSIVE,
    PARTITION p_2020_01 STARTING ('2020-01-01') ENDING ('2020-01-31') INCLUSIVE,
    PARTITION p_2020_02 STARTING ('2020-02-01') ENDING ('2020-02-29') INCLUSIVE,
    PARTITION p_2020_03 STARTING ('2020-03-01') ENDING ('2020-03-31') INCLUSIVE,
    -- ... additional partitions ...
    PARTITION p_2026_11 STARTING ('2026-11-01') ENDING ('2026-11-30') INCLUSIVE
);

Key differences from z/OS: - LUW uses STARTING and ENDING to define both lower and upper bounds explicitly. - The INCLUSIVE / EXCLUSIVE keywords control whether boundary values are included. - Partition names are user-defined identifiers (e.g., p_2020_01) rather than partition numbers. - On z/OS, only ENDING AT is specified; the lower bound is implicitly the previous partition's limit key + 1.

30.3.2 Hash Partitioning

DB2 LUW (version 10.5+) supports hash partitioning, which distributes rows across partitions based on a hash function applied to the partitioning key:

CREATE TABLE meridian.session_log (
    session_id    BIGINT NOT NULL,
    user_id       BIGINT NOT NULL,
    login_time    TIMESTAMP NOT NULL,
    logout_time   TIMESTAMP,
    ip_address    VARCHAR(45),
    device_type   VARCHAR(50)
)
PARTITION BY HASH (session_id)
INTO 16 PARTITIONS;

Hash partitioning is useful when: - There is no natural range key (no date column or no meaningful ordering). - The goal is even distribution of rows across partitions for parallel processing. - You want to avoid the skew that can occur with range partitioning when certain date ranges contain more data than others.

Hash partitioning does not support partition elimination for range queries — only equality predicates on the hash key column can eliminate partitions.

30.3.3 The ATTACH and DETACH Operations

LUW provides ALTER TABLE ... ATTACH PARTITION and ALTER TABLE ... DETACH PARTITION for efficient partition rotation:

Detaching a partition converts it into a standalone table:

ALTER TABLE meridian.transaction_history
    DETACH PARTITION p_2019
    INTO meridian.transaction_archive_2019;

After detaching: - The data is immediately accessible in the new table meridian.transaction_archive_2019. - No data movement occurs — this is a metadata operation. - The original partition is removed from the partitioned table.

Attaching a partition adds an existing table as a new partition:

-- First, create and populate the table to attach
CREATE TABLE meridian.transaction_2027_01 LIKE meridian.transaction_history;
-- Load data into meridian.transaction_2027_01 ...

ALTER TABLE meridian.transaction_history
    ATTACH PARTITION p_2027_01
    STARTING ('2027-01-01') ENDING ('2027-01-31') INCLUSIVE
    FROM meridian.transaction_2027_01;

-- After attach, run SET INTEGRITY to validate
SET INTEGRITY FOR meridian.transaction_history IMMEDIATE CHECKED;

Important: After an ATTACH, the table is placed in SET INTEGRITY PENDING state. You must run SET INTEGRITY ... IMMEDIATE CHECKED to validate the attached data and update indexes. This validation can be expensive for large partitions — plan for it during maintenance windows.

30.4 Database Partitioning Feature (DPF) on LUW

While table partitioning divides a table into ranges within a single database server, the Database Partitioning Feature (DPF) distributes data across multiple database servers (or "database partitions"). DPF is a shared-nothing architecture where each partition runs its own DB2 engine, manages its own buffer pool, and stores its own slice of the data.

30.4.1 Architecture Overview

In a DPF environment:

• The database is divided into multiple database partitions (numbered 0, 1, 2, ... N).
• Each partition runs on a separate physical server or logical partition.
• Tables are distributed across partitions using a distribution key (hash-based).
• A coordinator partition receives SQL requests from applications and orchestrates execution across all relevant partitions.
• Collocated joins occur when two tables are distributed on the same key and the join is on that key — no data movement is needed.

Application
    |
    v
Coordinator Partition (Partition 0)
    |           |           |
    v           v           v
Partition 1  Partition 2  Partition 3
[Data Slice] [Data Slice] [Data Slice]
[Buffer Pool][Buffer Pool][Buffer Pool]
[Log Files]  [Log Files]  [Log Files]

30.4.2 Distribution Keys

The distribution key determines which partition stores each row. DB2 applies a hashing function to the distribution key columns and maps the result to a partition:

CREATE TABLE meridian.transaction_history (
    trans_id      BIGINT NOT NULL,
    account_id    BIGINT NOT NULL,
    trans_date    DATE   NOT NULL,
    amount        DECIMAL(15,2),
    -- ...
)
DISTRIBUTE BY HASH (account_id);

Choosing the distribution key is critical: - High cardinality: The key should have many distinct values to ensure even distribution. - Join collocation: If two tables are frequently joined, they should share the same distribution key column. - Query patterns: Queries that filter on the distribution key can be directed to a single partition; queries without such filters must fan out to all partitions.

For Meridian National Bank, distributing TRANSACTION_HISTORY by ACCOUNT_ID ensures that all transactions for a given account reside on the same partition. A query like SELECT * FROM TRANSACTION_HISTORY WHERE ACCOUNT_ID = 12345 hits only one partition. Meanwhile, account-to-transaction joins are collocated if the ACCOUNT table is also distributed by ACCOUNT_ID.

30.4.3 Partition Groups and Buffer Pools

In a DPF environment, partition groups define which database partitions participate in storing a given table space:

CREATE DATABASE PARTITION GROUP pg_transaction
    ON DBPARTITIONNUMS (0, 1, 2, 3);

CREATE TABLESPACE ts_transaction
    IN DATABASE PARTITION GROUP pg_transaction
    MANAGED BY AUTOMATIC STORAGE;

Each database partition maintains its own buffer pool instance. Buffer pool configuration applies globally, but each partition allocates its own memory:

CREATE BUFFERPOOL bp_transaction
    SIZE 50000   -- 50,000 pages per partition
    PAGESIZE 32K;

On a 4-partition DPF system, this buffer pool consumes 50,000 * 32 KB * 4 = 6.25 GB of total memory across all partitions.

30.4.4 DPF Combined with Table Partitioning

DPF (hash distribution across servers) and table partitioning (range partitioning within a server) are orthogonal and can be combined:

CREATE TABLE meridian.transaction_history (
    trans_id      BIGINT NOT NULL,
    account_id    BIGINT NOT NULL,
    trans_date    DATE   NOT NULL,
    amount        DECIMAL(15,2)
)
DISTRIBUTE BY HASH (account_id)
PARTITION BY RANGE (trans_date)
(
    PARTITION p_2025_01 STARTING ('2025-01-01') ENDING ('2025-01-31'),
    PARTITION p_2025_02 STARTING ('2025-02-01') ENDING ('2025-02-28'),
    -- ...
);

In this configuration: - Each row is first assigned to a database partition based on the hash of ACCOUNT_ID. - Within that database partition, the row is placed in the appropriate range partition based on TRANS_DATE. - A query filtering on both ACCOUNT_ID and TRANS_DATE benefits from both distribution pruning (single database partition) and partition elimination (single range partition).

30.4.5 Inter-Partition Communication

When a query requires data from multiple partitions — for example, a non-collocated join or an aggregation across all partitions — DB2 uses table queues (TQ) to move data between partitions:

• Directed table queue: Rows are sent to a specific partition based on a hash value (used for non-collocated joins).
• Broadcast table queue: Rows are sent to all partitions (used when a small table must be joined with a large distributed table).
• Merge table queue: Results from all partitions are merged at the coordinator for final assembly.

Table queue overhead is visible in the EXPLAIN output and in the MON_GET_PKG_CACHE_STMT monitoring data. Excessive inter-partition communication (indicated by high TQ row counts) is a signal that the distribution key may need revision or that the query requires optimization.

30.4.6 DPF Operational Considerations

Operating a DPF environment introduces several unique operational concerns that single-partition deployments do not face:

Adding and removing database partitions: You can add a new database partition to an existing DPF cluster to increase capacity. When you add a partition, existing data is not automatically redistributed — only new rows are hashed to the new partition. To redistribute existing data, you must run the REDISTRIBUTE DATABASE PARTITION GROUP command, which is an expensive online operation that moves rows between partitions.

-- Add a new database partition
-- (Requires db2nodes.cfg update and instance restart)
-- db2start DBPARTITIONNUM 4 ADD DBPARTITIONNUM
-- Then redistribute:
REDISTRIBUTE DATABASE PARTITION GROUP pg_transaction UNIFORM;

Skew detection: Uneven data distribution across database partitions degrades parallel query performance because the slowest partition determines overall response time. Monitor partition-level row counts:

SELECT DBPARTITIONNUM,
       COUNT(*) AS ROW_COUNT
FROM meridian.transaction_history
GROUP BY DBPARTITIONNUM
ORDER BY DBPARTITIONNUM;

If any partition's row count exceeds 130% of the average, consider changing the distribution key to a higher-cardinality column.

Coordinator partition overhead: All queries enter through the coordinator partition, which can become a bottleneck for connection management and result assembly. For high-throughput workloads, configure the MAX_CONNECTIONS parameter on each partition and use client-side load balancing to distribute connections across partitions.

Backup and recovery in DPF: Each database partition must be backed up individually or simultaneously. A restore operation must restore all partitions to a consistent point in time. The db2_all command utility simplifies running commands across all partitions:

# Run backup on all partitions simultaneously
db2_all "db2 backup database MERIDIAN online to /backup"

30.5 Partition Key Selection

The choice of partition key is the single most important design decision in a partitioning strategy. A well-chosen key enables partition elimination, balances data across partitions, and aligns with the table's lifecycle management requirements. A poorly chosen key creates performance problems that are difficult to resolve without reorganizing the entire table.

30.5.1 Date Columns — The Most Common Choice

For transactional and event-driven tables, a date or timestamp column is almost always the best partition key. The reasons are compelling:

1. Natural ordering: Transactions arrive in chronological order. New data always flows into the most recent partition.
2. Query affinity: Most queries against transactional data include a date range predicate ("show me transactions from last month"). This predicate enables partition elimination.
3. Lifecycle alignment: Data retention policies are typically time-based ("retain seven years"). Monthly partitions map directly to the retention period.
4. Even distribution: Assuming relatively consistent transaction volumes, monthly partitions will be approximately the same size.

For Meridian National Bank, TRANS_DATE is the natural partition key for TRANSACTION_HISTORY. The bank processes roughly 15 million transactions per month, yielding monthly partitions of approximately 5 GB each — a manageable size for utilities and recovery.

30.5.2 Composite Partition Keys

In some cases, a single column does not provide sufficient granularity or does not appear in enough queries. DB2 supports composite partition keys — multiple columns that together define the partitioning scheme:

-- z/OS composite key example
CREATE TABLE MERIDIAN.BRANCH_TRANSACTIONS (
    BRANCH_ID   INTEGER   NOT NULL,
    TRANS_DATE   DATE      NOT NULL,
    TRANS_ID     BIGINT    NOT NULL,
    AMOUNT       DECIMAL(15,2)
)
PARTITION BY RANGE (BRANCH_ID, TRANS_DATE)
(
    PARTITION 1  ENDING AT (100,  '2025-12-31'),
    PARTITION 2  ENDING AT (200,  '2025-12-31'),
    PARTITION 3  ENDING AT (300,  '2025-12-31'),
    PARTITION 4  ENDING AT (9999, '2025-12-31')
);

Composite keys add complexity and should be used only when a strong business case exists — for example, when regulatory requirements mandate physical separation of data by region, or when query patterns consistently filter on both columns.

30.5.3 Choosing Granularity

The granularity of partitioning — daily, monthly, quarterly, or yearly — depends on several factors:

Meridian National Bank's choice: Monthly partitioning with a 7-year rolling window produces 84 active partitions — well within DB2's limits and small enough for efficient partition-level maintenance.

Rule of thumb: If the total number of partitions exceeds 500, consider coarser granularity. If individual partitions exceed 50 GB on z/OS (or 100 GB on LUW), consider finer granularity.

30.6 Partition Elimination

Partition elimination (also called partition pruning) is the optimizer's ability to exclude irrelevant partitions from a query's access path. It is the primary performance benefit of range partitioning and the reason why partition key selection matters so much.

30.6.1 How the Optimizer Eliminates Partitions

When the optimizer encounters a query against a partitioned table, it examines the WHERE clause predicates that reference the partition key column. If these predicates define a range that falls within a subset of partitions, the optimizer generates an access path that scans only those partitions.

Consider this query against Meridian's monthly-partitioned TRANSACTION_HISTORY:

SELECT ACCOUNT_ID, SUM(AMOUNT) AS TOTAL
FROM MERIDIAN.TRANSACTION_HISTORY
WHERE TRANS_DATE BETWEEN '2026-01-01' AND '2026-01-31'
GROUP BY ACCOUNT_ID;

The optimizer determines: - The partition key is TRANS_DATE. - The predicate TRANS_DATE BETWEEN '2026-01-01' AND '2026-01-31' maps entirely to partition p_2026_01. - Only that single partition is scanned; the remaining 83 partitions are eliminated.

The EXPLAIN output will show a partition range in the access plan, indicating which partitions are scanned. On z/OS, this appears as ACCESSTYPE = I with partition qualifications in the PLAN_TABLE. On LUW, the db2exfmt output includes a PARTITION RANGE section.

30.6.2 WHERE Clause Patterns That Enable Elimination

Partition elimination works with the following predicate patterns on the partition key:

Critical anti-pattern: Applying a function to the partition key column defeats elimination. The optimizer cannot determine the partition range when the column is wrapped in a function. Instead of WHERE YEAR(trans_date) = 2026, use WHERE trans_date BETWEEN '2026-01-01' AND '2026-12-31'.

30.6.3 Multi-Partition Scans

When a query spans multiple partitions, DB2 can scan them in parallel on both platforms:

• z/OS: Uses Parallel Sysplex query parallelism (CP parallelism) to scan multiple partitions concurrently. The degree of parallelism is controlled by CURRENT DEGREE and the RID pool size.
• LUW: Uses intra-partition parallelism to scan multiple partitions across available CPU cores. The INTRA_PARALLEL database manager configuration parameter controls this behavior.

A multi-partition scan that touches 3 out of 84 partitions is still dramatically faster than a full-table scan, even without parallelism: the I/O volume is reduced by 96%.

30.6.4 Verifying Partition Elimination

Always verify that partition elimination is occurring for critical queries:

z/OS — Check the PLAN_TABLE after EXPLAIN:

EXPLAIN PLAN SET QUERYNO = 100 FOR
    SELECT * FROM MERIDIAN.TRANSACTION_HISTORY
    WHERE TRANS_DATE = '2026-01-15';

SELECT QUERYNO, TNAME, PARTITION_START, PARTITION_STOP
FROM PLAN_TABLE
WHERE QUERYNO = 100;

If PARTITION_START and PARTITION_STOP indicate a subset (e.g., both equal 74), partition elimination is working. If both are 0, no elimination occurred.

LUW — Use db2exfmt or the EXPLAIN tables:

EXPLAIN PLAN FOR
    SELECT * FROM meridian.transaction_history
    WHERE trans_date = '2026-01-15';

-- Then examine the EXPLAIN output for partition range indicators

30.7 Rolling Window Pattern

The rolling window pattern is a lifecycle management technique that uses partition detach and attach operations to maintain a fixed-size window of active data. As new data arrives in new partitions, the oldest partitions are detached and archived. This pattern eliminates the need for large-scale DELETE operations.

30.7.1 The Problem with DELETE

Without partitioning, removing old data requires DELETE statements:

DELETE FROM MERIDIAN.TRANSACTION_HISTORY
WHERE TRANS_DATE < '2019-04-01';

For a table with 15 million rows per month, this DELETE must: 1. Scan the table to find qualifying rows (potentially a full table scan). 2. Delete each row individually, generating a log record for each deletion. 3. Update every index entry that references the deleted rows. 4. Acquire row-level or page-level locks on the deleted data.

The log volume alone can overwhelm the recovery infrastructure. A DELETE of 15 million rows generates roughly 3-5 GB of log data. The operation may take hours and creates significant contention with concurrent workloads.

30.7.2 Rolling Window on z/OS

On z/OS, the rolling window uses ROTATE PARTITION:

-- Step 1: Archive the oldest partition's data (if needed)
INSERT INTO MERIDIAN.TRANSACTION_ARCHIVE
    SELECT * FROM MERIDIAN.TRANSACTION_HISTORY
    WHERE TRANS_DATE <= '2019-03-31';

-- Step 2: Rotate the first partition to the last position
ALTER TABLE MERIDIAN.TRANSACTION_HISTORY
    ROTATE PARTITION FIRST TO LAST
    ENDING AT ('2027-01-31');

-- Step 3: Run REORG on the new (empty) last partition
-- (optional, but recommended to reset the partition)
-- REORG TABLESPACE MERIDIANDB.TSHIST PART 84

The ROTATE operation empties the first partition and repositions it at the end of the partition range with a new limit key. This is a catalog-only operation — it completes in seconds regardless of the partition's data volume.

30.7.3 Rolling Window on LUW

On LUW, the rolling window uses DETACH and ATTACH:

-- Step 1: Detach the oldest partition
ALTER TABLE meridian.transaction_history
    DETACH PARTITION p_2019_01
    INTO meridian.archive_2019_01;

-- Step 2: Archive the detached data (now a standalone table)
-- Export, compress, move to cold storage, etc.
EXPORT TO '/archive/trans_2019_01.ixf' OF IXF
    SELECT * FROM meridian.archive_2019_01;

-- Step 3: Drop the archive staging table
DROP TABLE meridian.archive_2019_01;

-- Step 4: Create and attach the new partition
CREATE TABLE meridian.trans_staging (
    -- same columns as transaction_history
) IN ts_transaction;

ALTER TABLE meridian.transaction_history
    ATTACH PARTITION p_2027_01
    STARTING ('2027-01-01') ENDING ('2027-01-31') INCLUSIVE
    FROM meridian.trans_staging;

-- Step 5: Validate the attached partition
SET INTEGRITY FOR meridian.transaction_history IMMEDIATE CHECKED;

30.7.4 LOAD Directly into a Partition

For high-volume data ingestion, you can LOAD data directly into a specific partition, bypassing the normal INSERT path:

z/OS:

LOAD DATA INDDN SYSREC
    RESUME YES
    INTO TABLE MERIDIAN.TRANSACTION_HISTORY PART 84
    ( TRANS_ID     POSITION(1:10)   INTEGER,
      ACCOUNT_ID   POSITION(11:20)  INTEGER,
      TRANS_DATE   POSITION(21:30)  DATE EXTERNAL,
      ...
    )

LUW:

-- Load into a staging table, then attach as a partition
LOAD FROM '/data/trans_2027_01.del' OF DEL
    INSERT INTO meridian.trans_staging_2027_01;

ALTER TABLE meridian.transaction_history
    ATTACH PARTITION p_2027_01
    STARTING ('2027-01-01') ENDING ('2027-01-31')
    FROM meridian.trans_staging_2027_01;

SET INTEGRITY FOR meridian.transaction_history IMMEDIATE CHECKED;

Loading into a staging table and then attaching it is faster than loading directly into the partitioned table because the staging table has no indexes from the partitioned table — the SET INTEGRITY phase builds only the necessary index entries.

30.7.5 Automating the Rolling Window

In production, the rolling window should be fully automated. On z/OS, this is typically implemented as a scheduled JCL job stream controlled by a workload automation tool (e.g., IBM TWS, CA7). On LUW, a stored procedure combined with the DB2 Task Scheduler or a cron job handles the automation:

CREATE OR REPLACE PROCEDURE meridian.rolling_window_maintenance()
LANGUAGE SQL
BEGIN
    DECLARE v_oldest_part VARCHAR(30);
    DECLARE v_new_start DATE;
    DECLARE v_new_end DATE;
    DECLARE v_new_name VARCHAR(30);

    -- Calculate the oldest partition to detach
    -- and the new partition to attach
    SET v_new_start = (SELECT MAX(DATAPARTITIONRANGEEND)
                       FROM SYSCAT.DATAPARTITIONS
                       WHERE TABNAME = 'TRANSACTION_HISTORY'
                         AND TABSCHEMA = 'MERIDIAN') + 1 DAY;
    SET v_new_end = v_new_start + 1 MONTH - 1 DAY;
    SET v_new_name = 'P_' || VARCHAR_FORMAT(v_new_start, 'YYYY_MM');

    -- Detach oldest partition
    -- Attach new partition
    -- (Dynamic SQL for DDL operations)
    -- ...
END;

30.8 Partition-Level Operations

One of the most compelling reasons to partition a table is the ability to perform maintenance operations — REORG, image copy, RUNSTATS, recovery — on individual partitions without affecting the availability of the rest of the table.

30.8.1 Partition-Level REORG

A REORG reclaims fragmented space, reorders rows according to the clustering index, and rebuilds indexes. On a 400 GB table, a full REORG can take hours and requires the table to be unavailable (or at best, available in read-only mode during the UNLOAD phase).

With partitioning, you REORG only the partition that needs it — typically the most recently loaded or most actively updated partition:

z/OS:

REORG TABLESPACE MERIDIANDB.TSHIST PART 84
    SHRLEVEL CHANGE
    MAPPINGTABLE MERIDIAN.TSHIST_MAP

This REORGs only partition 84 while all other partitions remain fully available for read and write access. The SHRLEVEL CHANGE option allows even the target partition to remain readable during most of the REORG process.

LUW:

REORG TABLE meridian.transaction_history
    ON DATA PARTITION p_2026_11;

30.8.2 Partition-Level Backup and Recovery

z/OS COPY (Image Copy):

COPY TABLESPACE MERIDIANDB.TSHIST PART 84
    FULL YES
    SHRLEVEL CHANGE

z/OS RECOVER:

RECOVER TABLESPACE MERIDIANDB.TSHIST PART 47
    TORBA X'...'

Only partition 47 is recovered; all other partitions remain available. This dramatically reduces recovery time from hours (full table) to minutes (single partition).

LUW BACKUP: LUW does not support partition-level backup directly in the same way as z/OS. However, the rolling window pattern (detach, backup the detached table, attach new) achieves a similar result. For online table spaces, DB2 LUW's incremental backup captures only changed pages, which effectively limits backup volume to recently modified partitions.

30.8.3 Partition-Level RUNSTATS

After a bulk load into a specific partition, you should refresh statistics for that partition:

z/OS:

RUNSTATS TABLESPACE MERIDIANDB.TSHIST PART 84
    TABLE(MERIDIAN.TRANSACTION_HISTORY)
    COLUMN(TRANS_DATE, ACCOUNT_ID, TRANS_TYPE)
    INDEX(ALL)
    SHRLEVEL CHANGE

LUW:

RUNSTATS ON TABLE meridian.transaction_history
    ON DATA PARTITION p_2026_11
    AND INDEXES ALL;

Partition-level RUNSTATS is particularly important after the rolling window rotation, as the newly attached partition has no statistics until RUNSTATS runs.

30.9 Partition-Level Indexing

The relationship between partitions and indexes is one of the most nuanced aspects of partitioning. Both z/OS and LUW offer multiple index types that interact differently with partitions.

30.9.1 z/OS: DPSI vs. NPSI

On z/OS, there are two primary index types for partitioned tables:

Data-Partitioned Secondary Index (DPSI): - The index is partitioned in the same way as the table — one index partition per data partition. - Each index partition contains entries only for rows in the corresponding data partition. - DPSI enables partition independence: you can REORG, RECOVER, or REBUILD one index partition without affecting others. - Limitation: A query that filters only on DPSI columns (not including the partition key) must probe every index partition — a "multi-probe" scan that is slower than a single index probe.

Non-Partitioned Secondary Index (NPSI): - A single, global index spanning all partitions. - A query on NPSI columns performs a single index probe and locates the row directly. - Limitation: Any partition-level operation (REORG, RECOVER) on the data requires corresponding maintenance on the NPSI — undermining partition independence. - An NPSI can become a concurrency bottleneck because all inserts (across all partitions) update the same global index.

Choosing between DPSI and NPSI:

For Meridian National Bank, the TRANSACTION_HISTORY table uses DPSI for all secondary indexes. The small performance penalty of multi-probe scans is offset by the enormous operational benefit of partition independence.

30.9.2 LUW: Partitioned and Non-Partitioned Indexes

On LUW, the terminology differs but the concepts are analogous:

Partitioned index: An index that is physically partitioned to match the table's data partitions. Created automatically when the index includes the partitioning key as a leading column.

Non-partitioned (global) index: An index that spans all partitions. Any index whose leading columns do not include the partition key is automatically non-partitioned.

-- This index is partitioned (trans_date is the partition key)
CREATE INDEX idx_trans_date_acct
    ON meridian.transaction_history (trans_date, account_id);

-- This index is non-partitioned (account_id is not the partition key)
CREATE INDEX idx_acct_id
    ON meridian.transaction_history (account_id);

Impact of DETACH on non-partitioned indexes: When you detach a partition, all non-partitioned indexes must be updated to remove entries for the detached data. This can be expensive. For tables that use the rolling window pattern extensively, minimizing non-partitioned indexes reduces the cost of each detach operation.

30.9.3 Index Design Strategy for Partitioned Tables

A practical index strategy for partitioned tables follows these guidelines:

1. Primary key / unique constraints: If the partitioning key is part of the unique constraint, the index can be partitioned. If not, the index must be global (non-partitioned). On z/OS, this means an NPSI; on LUW, it is automatic.

2. Most-queried secondary indexes: If queries typically include the partition key in the WHERE clause, the index can be partitioned (DPSI or LUW partitioned index) — partition elimination will be in effect, and only relevant index partitions are scanned.

3. Lookup indexes without the partition key: These must be global. Accept the trade-off: faster lookups but reduced partition independence.

4. Minimize global indexes on rolling-window tables: Each detach/attach cycle must maintain global indexes. If possible, restructure queries to include the partition key so that indexes can be partitioned.

30.10 Partitioning Anti-Patterns

30.10.1 Too Many Partitions

Creating daily partitions for a table with a 10-year retention policy yields 3,650 partitions. While DB2 z/OS supports up to 4,096 partitions and LUW has no hard limit, excessive partition counts create problems:

• Catalog bloat: Each partition has entries in the system catalog. Thousands of partitions increase the size of catalog tables and slow DDL operations.
• Optimizer overhead: The optimizer must evaluate each partition for elimination. With thousands of partitions, the compilation time for access paths increases.
• Utility management: Scheduling and monitoring thousands of individual partition-level utility operations is operationally complex.
• Open file handles: Each partition may correspond to a separate file descriptor. Operating system limits on open files can be reached.

Guideline: Keep the total partition count under 500 for most workloads. If you need finer granularity for recent data, consider a hybrid approach: daily partitions for the current quarter, monthly partitions for older data.

30.10.2 Wrong Partition Key

Choosing a partition key that does not appear in most queries eliminates the performance benefit of partitioning. If the TRANSACTION_HISTORY table is partitioned by BRANCH_ID but 90% of queries filter by TRANS_DATE, the optimizer cannot eliminate partitions for those queries.

Symptom: EXPLAIN output shows PARTITION_START = 1 and PARTITION_STOP = <last partition> for common queries — no elimination is occurring.

Resolution: Alter the partitioning scheme. Unfortunately, this typically requires recreating the table — there is no ALTER TABLE operation to change the partition key on either platform. Plan the partition key carefully before creating the table.

30.10.3 Unbalanced Partitions

If one partition contains significantly more data than others, it becomes a performance and maintenance bottleneck. Common causes:

• Seasonal variation: Retail banks process more transactions in December than in February. Monthly partitions will vary in size by 20-40%.
• Catch-all first partition: If the first partition has a very wide range (e.g., all data before 2019), it may contain millions of historical rows that should have been distributed across multiple partitions.
• Hash distribution skew: In DPF environments, a distribution key with low cardinality (e.g., TRANS_TYPE with only 5 values) produces extreme skew.

Mitigation: Accept moderate variation (2:1 ratios are normal) but redesign if variation exceeds 5:1. For seasonal skew, consider mixed-granularity partitioning (biweekly in December, monthly otherwise) if the operational complexity is justified.

30.10.4 Cross-Partition Queries Without Awareness

Queries that must scan all partitions — such as aggregations without a date filter — receive no benefit from partitioning and may actually perform worse due to the overhead of managing multiple partition scans.

-- This query scans ALL 84 partitions
SELECT ACCOUNT_ID, SUM(AMOUNT)
FROM MERIDIAN.TRANSACTION_HISTORY
GROUP BY ACCOUNT_ID;

Mitigation: Add a date filter whenever possible. For reporting queries that genuinely require all-partition scans, schedule them during off-peak hours and ensure adequate parallelism is available.

30.10.5 Partition Key Not in the Primary Key

On both platforms, if the partitioning key is not part of the primary key or unique constraint, the enforcing index must be global (non-partitioned). This is a subtle but consequential design mistake:

-- ANTI-PATTERN: Partition key (trans_date) not in primary key
CREATE TABLE meridian.transactions (
    trans_id   BIGINT NOT NULL PRIMARY KEY,  -- NPSI required!
    trans_date DATE   NOT NULL,
    -- ...
)
PARTITION BY RANGE (trans_date) ( ... );

The unique index on TRANS_ID alone cannot be partitioned because DB2 cannot guarantee uniqueness within a single partition — the same TRANS_ID could theoretically appear in different partitions. Therefore, DB2 creates a global index, breaking partition independence.

Fix: Include the partition key in the primary key:

-- CORRECT: Partition key included in primary key
CREATE TABLE meridian.transactions (
    trans_id   BIGINT NOT NULL,
    trans_date DATE   NOT NULL,
    CONSTRAINT pk_trans PRIMARY KEY (trans_date, trans_id)
)
PARTITION BY RANGE (trans_date) ( ... );

With (trans_date, trans_id) as the primary key, uniqueness can be enforced within each partition independently, and the unique index can be partitioned.

30.10.6 Ignoring Partition Boundaries in Application Logic

Application developers sometimes write queries that inadvertently span all partitions when they only need recent data. A common example is a "last N transactions" query:

-- ANTI-PATTERN: No date filter — scans all 84 partitions
SELECT * FROM meridian.transaction_history
WHERE account_id = 12345
ORDER BY created_timestamp DESC
FETCH FIRST 10 ROWS ONLY;

Even though the query returns only 10 rows, DB2 must probe the ACCOUNT_ID index across all partitions (DPSI multi-probe) or scan the global index (NPSI). Adding a date filter dramatically reduces the scope:

-- CORRECT: Date filter enables partition elimination
SELECT * FROM meridian.transaction_history
WHERE account_id = 12345
  AND trans_date >= CURRENT_DATE - 90 DAYS
ORDER BY created_timestamp DESC
FETCH FIRST 10 ROWS ONLY;

This query scans at most 3-4 partitions (the current month plus the previous 2-3 months), a 95% reduction in work compared to scanning all 84 partitions. Application architects should establish a standard practice of always including the partition key in queries against partitioned tables, even when the partition key is not strictly required by the business logic.

30.11 Meridian Bank Partitioning Strategy

With the principles established in the preceding sections, we now design a complete partitioning strategy for Meridian National Bank's TRANSACTION_HISTORY table.

30.11.1 Requirements

1. Retention: 7 years of transaction data for regulatory compliance.
2. Volume: 15 million transactions per month, growing at 8% annually.
3. Query patterns: 80% of queries filter on TRANS_DATE (date range). 15% filter on ACCOUNT_ID + TRANS_DATE. 5% are ad-hoc analytics across all data.
4. Maintenance window: 4 hours nightly, 8 hours on weekends.
5. Availability: 99.95% for the most recent 2 years of data.
6. Platforms: z/OS for core banking (primary), LUW for digital banking (secondary replica).

30.11.2 Design Decisions

30.11.3 z/OS Implementation

CREATE TABLESPACE TSHIST
    IN MERIDIANDB
    USING STOGROUP MERDSN
    DSSIZE 16G
    BUFFERPOOL BP32K
    SEGSIZE 64
    MAXPARTITIONS 128
    LOCKSIZE ROW
    CLOSE NO;

CREATE TABLE MERIDIAN.TRANSACTION_HISTORY (
    TRANS_ID          BIGINT        NOT NULL GENERATED ALWAYS AS IDENTITY
                                    (START WITH 1, INCREMENT BY 1, CACHE 1000),
    ACCOUNT_ID        BIGINT        NOT NULL,
    TRANS_DATE        DATE          NOT NULL,
    TRANS_TYPE        CHAR(3)       NOT NULL
                      CHECK (TRANS_TYPE IN ('DEP','WDR','TRF','PMT','FEE','INT')),
    AMOUNT            DECIMAL(15,2) NOT NULL,
    CURRENCY_CODE     CHAR(3)       NOT NULL DEFAULT 'USD',
    BRANCH_ID         INTEGER,
    TELLER_ID         INTEGER,
    CHANNEL           CHAR(3)       DEFAULT 'BRN'
                      CHECK (CHANNEL IN ('BRN','ATM','ONL','MOB','API')),
    DESCRIPTION       VARCHAR(200),
    CREATED_TIMESTAMP TIMESTAMP     NOT NULL WITH DEFAULT,
    CONSTRAINT PK_TRANS PRIMARY KEY (TRANS_DATE, TRANS_ID)
)
IN MERIDIANDB.TSHIST
PARTITION BY RANGE (TRANS_DATE)
(
    PARTITION 1  ENDING AT ('2019-12-31'),
    PARTITION 2  ENDING AT ('2020-01-31'),
    PARTITION 3  ENDING AT ('2020-02-29'),
    PARTITION 4  ENDING AT ('2020-03-31'),
    -- ... 80 more monthly partitions ...
    PARTITION 84 ENDING AT ('2026-11-30')
);

-- DPSI: Account lookup (requires partition key in most queries)
CREATE INDEX MERIDIAN.IX_TRANS_ACCT
    ON MERIDIAN.TRANSACTION_HISTORY (ACCOUNT_ID, TRANS_DATE)
    USING STOGROUP MERDSN
    CLUSTER
    PARTITIONED;

-- DPSI: Transaction type analysis
CREATE INDEX MERIDIAN.IX_TRANS_TYPE
    ON MERIDIAN.TRANSACTION_HISTORY (TRANS_TYPE, TRANS_DATE)
    USING STOGROUP MERDSN
    PARTITIONED;

-- DPSI: Branch reporting
CREATE INDEX MERIDIAN.IX_TRANS_BRANCH
    ON MERIDIAN.TRANSACTION_HISTORY (BRANCH_ID, TRANS_DATE)
    USING STOGROUP MERDSN
    PARTITIONED;

30.11.4 LUW Implementation

CREATE TABLE meridian.transaction_history (
    trans_id          BIGINT        NOT NULL GENERATED ALWAYS AS IDENTITY
                                    (START WITH 1, INCREMENT BY 1, CACHE 1000),
    account_id        BIGINT        NOT NULL,
    trans_date        DATE          NOT NULL,
    trans_type        CHAR(3)       NOT NULL
                      CONSTRAINT chk_trans_type
                      CHECK (trans_type IN ('DEP','WDR','TRF','PMT','FEE','INT')),
    amount            DECIMAL(15,2) NOT NULL,
    currency_code     CHAR(3)       NOT NULL DEFAULT 'USD',
    branch_id         INTEGER,
    teller_id         INTEGER,
    channel           CHAR(3)       DEFAULT 'BRN'
                      CONSTRAINT chk_channel
                      CHECK (channel IN ('BRN','ATM','ONL','MOB','API')),
    description       VARCHAR(200),
    created_timestamp TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT pk_trans PRIMARY KEY (trans_date, trans_id)
)
DISTRIBUTE BY HASH (account_id)
PARTITION BY RANGE (trans_date)
(
    PARTITION p_2019    STARTING ('2019-01-01') ENDING ('2019-12-31') INCLUSIVE,
    PARTITION p_2020_01 STARTING ('2020-01-01') ENDING ('2020-01-31') INCLUSIVE,
    PARTITION p_2020_02 STARTING ('2020-02-01') ENDING ('2020-02-29') INCLUSIVE,
    -- ... additional monthly partitions ...
    PARTITION p_2026_11 STARTING ('2026-11-01') ENDING ('2026-11-30') INCLUSIVE
);

30.11.5 Monthly Maintenance Plan

The following operations run on the first of each month:

1. Rotate/Detach oldest partition (Month 85 → archive): - z/OS: ROTATE PARTITION FIRST TO LAST ENDING AT ('<new-month-end>') - LUW: DETACH PARTITION <oldest> INTO archive_table

2. Archive detached data: - z/OS: UNLOAD the partition before rotation to archive data set. - LUW: EXPORT the detached table to IXF format; compress and ship to cold storage.

3. Attach/Prepare new partition: - z/OS: The ROTATE operation handles this automatically. - LUW: CREATE staging table; ATTACH as new partition; SET INTEGRITY.

4. RUNSTATS on the new partition: - Run after the first full day of data loading into the new partition.

5. REORG the previous month's partition: - After a full month of inserts, the previous month's partition benefits from REORG to optimize clustering.

6. Image copy / Backup: - z/OS: COPY TABLESPACE ... PART . - LUW: Incremental backup captures changed pages.

30.11.6 Capacity Planning and Growth Projections

With 15 million transactions per month growing at 8% annually, the partition sizes evolve as follows:

The 16 GB DSSIZE on z/OS provides sufficient headroom through 2032, at which point partition sizes would approach 12 GB. The DBA team should review DSSIZE annually and plan an ALTER TABLESPACE to increase DSSIZE before any partition reaches 80% of the limit.

On LUW, table partition sizes are not explicitly limited by DSSIZE, but individual partitions exceeding 50 GB should trigger a review of the partitioning granularity. At the projected growth rate, Meridian's monthly partitions will not exceed 50 GB within the foreseeable planning horizon.

30.11.7 Monitoring Partition Health

Create a monitoring query to track partition sizes and detect imbalance:

-- z/OS: Monitor partition sizes
SELECT P.PARTITION,
       P.NACTIVE AS ACTIVE_PAGES,
       P.SPACE AS ALLOCATED_KB,
       P.NACTIVE * 32 AS DATA_SIZE_KB  -- 32K page size
FROM SYSIBM.SYSTABLEPART P
WHERE P.TSNAME = 'TSHIST'
  AND P.DBNAME = 'MERIDIANDB'
ORDER BY P.PARTITION;

-- LUW: Monitor partition sizes
SELECT D.DATAPARTITIONNAME,
       D.DATAPARTITIONID,
       T.DATA_OBJECT_P_SIZE AS SIZE_KB,
       T.INDEX_OBJECT_P_SIZE AS INDEX_KB
FROM SYSCAT.DATAPARTITIONS D
JOIN TABLE(MON_GET_TABLE('MERIDIAN', 'TRANSACTION_HISTORY', -2)) T
  ON D.DATAPARTITIONID = T.DATA_PARTITION_ID
WHERE D.TABSCHEMA = 'MERIDIAN'
  AND D.TABNAME = 'TRANSACTION_HISTORY'
ORDER BY D.DATAPARTITIONID;

Spaced Review: Connecting to Earlier Chapters

From Chapter 14 — Indexing Strategies

In Chapter 14, we discussed clustering indexes and their impact on sequential read performance. Partitioning adds a new dimension: the clustering index should align with the partition key. For TRANSACTION_HISTORY, the clustering index leads with ACCOUNT_ID, TRANS_DATE. Within each monthly partition, transactions for the same account are physically adjacent, enabling efficient range scans for account activity reports.

Review question: If you change the clustering index on a partitioned table from (ACCOUNT_ID, TRANS_DATE) to (TRANS_TYPE, TRANS_DATE), which partition-level operation must you run to realize the new clustering order?

Answer: REORG on each partition. Changing the clustering index definition does not physically reorder the data — only REORG does.

From Chapter 17 — Query Optimization

Chapter 17 introduced the concept of predicate pushdown and access path selection. Partition elimination is a form of predicate pushdown at the storage level. The optimizer evaluates partition key predicates before selecting an index access path within the qualifying partitions.

Review question: A query contains WHERE trans_date >= '2026-01-01' AND account_id = 12345. In what order does the optimizer apply these predicates in a partitioned table with a DPSI on ACCOUNT_ID?

Answer: First, partition elimination reduces the scan to partitions starting from January 2026. Second, within each qualifying partition, the DPSI on ACCOUNT_ID is probed to locate rows for account 12345.

From Chapter 22 — Concurrency and Locking

Chapter 22 covered lock escalation and its impact on concurrent access. Partitioning reduces the scope of lock escalation: on z/OS, lock escalation on a partitioned table escalates to partition-level locks rather than table-level locks. An escalation in partition 84 does not block access to partitions 1-83.

Review question: On z/OS, if LOCKSIZE ROW is specified and lock escalation occurs during a batch update of the current month's data in partition 84, what is the escalation target?

Answer: The lock escalates from row locks to a partition-level lock (S, X, or IX depending on the operation) on partition 84 only. Other partitions are not affected.

Summary

Partitioning transforms large, unwieldy tables into collections of manageable pieces. The partition key — almost always a date column for transactional tables — determines how rows are distributed and how effectively the optimizer can eliminate irrelevant partitions.

On z/OS, partition-by-range universal table spaces provide deep integration with the utility infrastructure, enabling partition-level REORG, COPY, RECOVER, and RUNSTATS. On LUW, table partitioning combined with DPF enables both range-based and hash-based distribution across multiple servers.

The rolling window pattern replaces expensive DELETE operations with efficient DDL operations — detach the oldest partition, attach the newest — maintaining a fixed retention window with minimal overhead. Partition-level indexing (DPSI on z/OS, partitioned indexes on LUW) preserves partition independence at the cost of multi-probe scans for queries that lack the partition key.

For Meridian National Bank, monthly range partitioning on TRANS_DATE with a 7-year rolling window provides the balance of performance, manageability, and regulatory compliance that a production banking system demands.

In Chapter 31, we will explore how DB2 extends into the cloud, examining IBM's managed Db2 services, container deployments, and hybrid architectures that connect the on-premises z/OS systems we have been configuring throughout this book with the cloud-based services that are increasingly part of modern banking infrastructure.