Case Study 30.2: Rolling Window Implementation for Regulatory Retention

Background

Eastern Federal Credit Union operates under NCUA (National Credit Union Administration) regulations that require 7-year retention of all member transaction records. The credit union processes transactions across 45 branch locations and serves 350,000 members.

The Problem

The existing MEMBER_TRANSACTIONS table on DB2 LUW 11.5 has grown to 1.2 billion rows (380 GB) over 8 years. The table is not partitioned. The current archival process uses a monthly DELETE job that:

1. Deletes all transactions older than 7 years.
2. Runs REORG to reclaim space.
3. Rebuilds all indexes.
4. Takes a full database backup.

Pain points: - The DELETE operation takes 14 hours, generating 45 GB of transaction log. - REORG takes 8 hours and requires exclusive access to the table. - Total maintenance window: 22+ hours (spanning an entire weekend). - During the DELETE/REORG, online banking is unavailable — members cannot view transaction history. - The log volume frequently fills the active log space, causing applications to hang waiting for log space. - Recovery from any table corruption requires restoring the entire 380 GB table — a 6-hour operation.

Target Architecture

The DBA team proposes migrating to a monthly range-partitioned table with a rolling window to eliminate the DELETE-based archival.

Migration Plan

Phase 1: Create the new partitioned table

CREATE TABLE meridian.member_transactions_new (
    trans_id          BIGINT        NOT NULL GENERATED ALWAYS AS IDENTITY,
    member_id         BIGINT        NOT NULL,
    trans_date        DATE          NOT NULL,
    trans_type        CHAR(4)       NOT NULL,
    amount            DECIMAL(13,2) NOT NULL,
    running_balance   DECIMAL(13,2),
    branch_id         INTEGER,
    teller_id         INTEGER,
    description       VARCHAR(150),
    check_number      VARCHAR(20),
    created_ts        TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT pk_member_trans PRIMARY KEY (trans_date, trans_id)
)
PARTITION BY RANGE (trans_date)
(
    PARTITION p_2019_01 STARTING ('2019-01-01') ENDING ('2019-01-31') INCLUSIVE,
    PARTITION p_2019_02 STARTING ('2019-02-01') ENDING ('2019-02-28') INCLUSIVE,
    PARTITION p_2019_03 STARTING ('2019-03-01') ENDING ('2019-03-31') INCLUSIVE,
    -- ... (84 monthly partitions through December 2025)
    PARTITION p_2025_12 STARTING ('2025-12-01') ENDING ('2025-12-31') INCLUSIVE,
    -- Plus 12 future months
    PARTITION p_2026_01 STARTING ('2026-01-01') ENDING ('2026-01-31') INCLUSIVE,
    -- ... through December 2026
    PARTITION p_2026_12 STARTING ('2026-12-01') ENDING ('2026-12-31') INCLUSIVE
);

Phase 2: Data migration using LOAD per partition

Rather than INSERTing the full 1.2 billion rows through the SQL engine, the team exports data by month and loads it into individual staging tables that are then attached:

#!/bin/bash
# Export and load data month by month
for year in $(seq 2019 2025); do
    for month in $(seq -w 1 12); do
        start_date="${year}-${month}-01"
        # Calculate end date (last day of month)
        end_date=$(date -d "${start_date} + 1 month - 1 day" +%Y-%m-%d)

        echo "Processing ${year}-${month}..."

        # Export from the old table
        db2 "EXPORT TO /staging/trans_${year}_${month}.ixf OF IXF
             SELECT * FROM meridian.member_transactions
             WHERE trans_date BETWEEN '${start_date}' AND '${end_date}'"

        # Create staging table
        db2 "CREATE TABLE meridian.staging_${year}_${month}
             LIKE meridian.member_transactions_new"

        # Load into staging table
        db2 "LOAD FROM /staging/trans_${year}_${month}.ixf OF IXF
             INSERT INTO meridian.staging_${year}_${month}"

        echo "Loaded ${year}-${month} successfully."
    done
done

Phase 3: Attach partitions and validate

-- Attach each staging table as a partition
ALTER TABLE meridian.member_transactions_new
    ATTACH PARTITION p_2019_01
    STARTING ('2019-01-01') ENDING ('2019-01-31') INCLUSIVE
    FROM meridian.staging_2019_01;

-- Repeat for all 84 months...

-- Validate all attached partitions
SET INTEGRITY FOR meridian.member_transactions_new IMMEDIATE CHECKED;

-- Run RUNSTATS
RUNSTATS ON TABLE meridian.member_transactions_new AND INDEXES ALL;

Phase 4: Cutover

-- Rename old table
RENAME TABLE meridian.member_transactions TO meridian.member_transactions_old;

-- Rename new table
RENAME TABLE meridian.member_transactions_new TO meridian.member_transactions;

-- Update views and synonyms as needed
-- Test application connectivity
-- After validation period, drop old table

Rolling Window Stored Procedure

CREATE OR REPLACE PROCEDURE meridian.execute_rolling_window()
LANGUAGE SQL
BEGIN
    DECLARE v_detach_name VARCHAR(30);
    DECLARE v_attach_name VARCHAR(30);
    DECLARE v_new_start DATE;
    DECLARE v_new_end DATE;
    DECLARE v_sql VARCHAR(1000);

    -- Identify oldest partition
    SELECT DATAPARTITIONNAME INTO v_detach_name
    FROM SYSCAT.DATAPARTITIONS
    WHERE TABSCHEMA = 'MERIDIAN'
      AND TABNAME = 'MEMBER_TRANSACTIONS'
    ORDER BY DATAPARTITIONID
    FETCH FIRST 1 ROW ONLY;

    -- Calculate new partition dates (one month after the current latest)
    SELECT DATAPARTITIONRANGEEND + 1 DAY INTO v_new_start
    FROM SYSCAT.DATAPARTITIONS
    WHERE TABSCHEMA = 'MERIDIAN'
      AND TABNAME = 'MEMBER_TRANSACTIONS'
    ORDER BY DATAPARTITIONID DESC
    FETCH FIRST 1 ROW ONLY;

    SET v_new_end = LAST_DAY(v_new_start);
    SET v_attach_name = 'P_' || TO_CHAR(v_new_start, 'YYYY_MM');

    -- Step 1: Detach oldest partition
    SET v_sql = 'ALTER TABLE meridian.member_transactions '
             || 'DETACH PARTITION ' || v_detach_name
             || ' INTO meridian.archive_' || v_detach_name;
    EXECUTE IMMEDIATE v_sql;
    COMMIT;

    -- Step 2: Create staging table for new partition
    SET v_sql = 'CREATE TABLE meridian.staging_new '
             || 'LIKE meridian.member_transactions';
    EXECUTE IMMEDIATE v_sql;

    -- Step 3: Attach new partition
    SET v_sql = 'ALTER TABLE meridian.member_transactions '
             || 'ATTACH PARTITION ' || v_attach_name
             || ' STARTING (''' || CHAR(v_new_start) || ''')'
             || ' ENDING (''' || CHAR(v_new_end) || ''') INCLUSIVE'
             || ' FROM meridian.staging_new';
    EXECUTE IMMEDIATE v_sql;

    -- Step 4: Validate
    SET v_sql = 'SET INTEGRITY FOR meridian.member_transactions '
             || 'IMMEDIATE CHECKED';
    EXECUTE IMMEDIATE v_sql;
    COMMIT;

    -- Step 5: Archive the detached data
    -- (External process handles export and cleanup)
    CALL DBMS_OUTPUT.PUT_LINE('Rolling window complete. '
        || 'Detached: ' || v_detach_name
        || ' Attached: ' || v_attach_name);
END;

Archival Pipeline

After detaching, the archive pipeline:

1. EXPORT the detached table to IXF format.
2. Compress the IXF file using gzip (typically 5:1 compression).
3. Upload to Cloud Object Storage for long-term retention (10+ years for legal hold).
4. DROP the detached archive table.
5. Log the archival event in the ARCHIVE_CATALOG table for audit purposes.

Results

The most dramatic improvement was the elimination of the 8-hour downtime window. Online banking transaction history is now available 24/7, including during the monthly archival process.

Challenges Encountered

Challenge 1: Non-Partitioned Index on MEMBER_ID

The MEMBER_ID index was non-partitioned because it did not include the partition key. Each DETACH operation required updating this global index, adding 3-4 minutes to the detach process.

Resolution: Redesigned the index as (member_id, trans_date) — now partitioned. Queries on MEMBER_ID alone require multi-partition index probes but the trade-off was acceptable given the 24/7 availability requirement.

Challenge 2: SET INTEGRITY Duration

For the initial migration, SET INTEGRITY ... IMMEDIATE CHECKED on the 84 attached partitions took 4 hours to validate constraints and build indexes.

Resolution: For the ongoing monthly rolling window, SET INTEGRITY on a single empty partition completes in seconds. The 4-hour duration was a one-time migration cost.

Challenge 3: February Partition Boundaries

The team initially used ENDING ('2019-02-28') for February 2019 but ENDING ('2020-02-29') for February 2020 (leap year). This required careful date calculation in the automation script.

Resolution: Used database date functions (LAST_DAY()) to calculate partition boundaries dynamically rather than hard-coding dates.

Discussion Questions

1. What would happen if the rolling window procedure fails midway — after DETACH but before ATTACH? How would you design the procedure to be idempotent?

2. If the credit union merges with another institution that uses Oracle instead of DB2, how could the rolling window archive files (IXF format) be migrated to the target platform?

3. The compliance department requests that archived data remain queryable for legal discovery purposes. How would you provide SQL access to archived data without reloading it into the active table?