Case Study 2: Pinnacle Health's Temporal Tables for Claims History

Background

Pinnacle Health Systems processes 50 million insurance claims annually across its mainframe platform. The claims processing system — a suite of 340 COBOL-DB2 programs built and maintained over 22 years — is the backbone of the organization's operations. Every claim passes through this system: intake, validation, adjudication, payment, and post-payment review.

In March 2025, Pinnacle Health received notice of a CMS (Centers for Medicare & Medicaid Services) audit covering claims processed between January 2023 and December 2024. The audit required Pinnacle to demonstrate, for any sampled claim, exactly what the system knew about a beneficiary's coverage, eligibility, and benefit level at the time the claim was adjudicated — not what the current records show, but what the records showed on the specific date the claim was processed.

Diane Kowalski, Lead DB2 Administrator (19 years at Pinnacle), and Ahmad Patel, Senior COBOL Developer (14 years), were assigned to respond.

The Audit Response Problem

The CMS auditors selected 2,400 claims for detailed review. For each claim, they wanted to see:

1. The beneficiary's eligibility status at the time of adjudication
2. The benefit plan and coverage level in effect at adjudication
3. The provider's network status at adjudication
4. The fee schedule used for pricing
5. Any prior authorization records that existed at adjudication

The problem: Pinnacle's current tables reflected current state only. When a beneficiary's eligibility changed, the old record was overwritten with UPDATE. When a fee schedule was revised, the new rates replaced the old ones. When a provider's network status changed, the STATUS_CODE column was updated in place.

Pinnacle had audit trail tables — INSERT-only log tables that recorded every change. But these tables stored change events, not point-in-time state. Reconstructing the state of a beneficiary's record on a specific date required:

1. Finding the beneficiary's current record
2. Querying the audit trail for all changes after the target date
3. Reversing each change in chronological order to reconstruct the prior state
4. Repeating for every related table (eligibility, benefits, provider, fee schedule, prior auth)

"For the first three claims, it took two analysts a full day each," Diane recalled. "They were manually reading audit trail records, figuring out which columns changed, and reversing the changes in a spreadsheet. We had 2,400 claims to review. At that rate, it would take over four years."

Ahmad wrote a program to automate the reconstruction, but the logic was fragile. Different tables had different audit trail formats. Some changes were recorded as before/after pairs, others as just the new value. Some tables had been restructured during the review period, adding columns that didn't exist in older audit records.

"The reconstruction program worked for about 80% of cases," Ahmad said. "The other 20% required manual intervention because the audit trail was incomplete or inconsistent. We spent six weeks on the audit response that should have taken six days."

The Decision to Implement Temporal Tables

After the audit, Pinnacle's CTO authorized a project to implement temporal tables on all claims-related tables. The business case was straightforward: another CMS audit was statistically likely within 3–5 years, and the next one might cover a longer period or sample more claims. The cost of manual reconstruction was unsustainable.

Diane and Ahmad designed a phased implementation covering 47 claims-related tables.

Phase 1: System-Period Temporal on Core Tables

The first phase covered the 12 most critical tables:

DDL Design Decisions

Diane faced several design decisions:

Timestamp precision: She chose TIMESTAMP(12) — the maximum precision DB2 supports. CMS audits can require sub-second precision, and claims adjudication can process multiple updates to the same record within a single second during batch processing.

History table placement: Each history table was placed in a separate tablespace from its base table, on different DASD volumes. This prevented history table growth from affecting base table I/O performance and allowed independent backup/recovery.

Partitioning strategy: History tables were range-partitioned by SYS_END timestamp, with monthly partitions. This allowed efficient purging (drop the oldest partition instead of DELETE) and targeted backup of recent history.

Indexing: Each history table had a composite index on (primary_key, SYS_END DESC). This optimized the most common temporal query pattern: "find the version of this record that was active at time T."

Here is the DDL for the BENEFICIARY table:

-- Base table with system-period temporal columns
CREATE TABLE BENEFICIARY
(
    BENE_ID          CHAR(10)       NOT NULL,
    BENE_LAST_NAME   VARCHAR(40)    NOT NULL,
    BENE_FIRST_NAME  VARCHAR(30)    NOT NULL,
    DATE_OF_BIRTH    DATE           NOT NULL,
    SSN_HASH         CHAR(64)       NOT NULL,
    STATUS_CODE      CHAR(2)        NOT NULL,
    PLAN_CODE        CHAR(6)        NOT NULL,
    ENROLLMENT_DATE  DATE           NOT NULL,
    TERM_DATE        DATE,
    ADDRESS_LINE1    VARCHAR(60),
    ADDRESS_LINE2    VARCHAR(60),
    CITY             VARCHAR(40),
    STATE_CODE       CHAR(2),
    ZIP_CODE         CHAR(10),
    PHONE            CHAR(10),
    EMAIL            VARCHAR(80),
    SYS_START        TIMESTAMP(12)  NOT NULL
                     GENERATED ALWAYS AS ROW BEGIN,
    SYS_END          TIMESTAMP(12)  NOT NULL
                     GENERATED ALWAYS AS ROW END,
    TXN_START_ID     TIMESTAMP(12)
                     GENERATED ALWAYS AS TRANSACTION
                     START ID,
    PERIOD SYSTEM_TIME (SYS_START, SYS_END),
    CONSTRAINT PK_BENEFICIARY PRIMARY KEY (BENE_ID)
)
IN DBCLAIMS.TSBENE
PARTITION BY (BENE_ID ASC)
(
    PARTITION 1 ENDING AT ('2999999999'),
    PARTITION 2 ENDING AT ('5999999999'),
    PARTITION 3 ENDING AT ('8999999999'),
    PARTITION 4 ENDING AT ('ZZZZZZZZZZ')
);

-- History table
CREATE TABLE BENEFICIARY_HIST
(
    BENE_ID          CHAR(10)       NOT NULL,
    BENE_LAST_NAME   VARCHAR(40)    NOT NULL,
    BENE_FIRST_NAME  VARCHAR(30)    NOT NULL,
    DATE_OF_BIRTH    DATE           NOT NULL,
    SSN_HASH         CHAR(64)       NOT NULL,
    STATUS_CODE      CHAR(2)        NOT NULL,
    PLAN_CODE        CHAR(6)        NOT NULL,
    ENROLLMENT_DATE  DATE           NOT NULL,
    TERM_DATE        DATE,
    ADDRESS_LINE1    VARCHAR(60),
    ADDRESS_LINE2    VARCHAR(60),
    CITY             VARCHAR(40),
    STATE_CODE       CHAR(2),
    ZIP_CODE         CHAR(10),
    PHONE            CHAR(10),
    EMAIL            VARCHAR(80),
    SYS_START        TIMESTAMP(12)  NOT NULL,
    SYS_END          TIMESTAMP(12)  NOT NULL,
    TXN_START_ID     TIMESTAMP(12)
)
IN DBCLAIMS.TSBENEHIST
PARTITION BY (SYS_END ASC)
(
    PARTITION 1 ENDING AT ('2025-03-31-24.00.00.000000000000'),
    PARTITION 2 ENDING AT ('2025-06-30-24.00.00.000000000000'),
    PARTITION 3 ENDING AT ('2025-09-30-24.00.00.000000000000'),
    PARTITION 4 ENDING AT ('2025-12-31-24.00.00.000000000000'),
    PARTITION 5 ENDING AT ('9999-12-30-24.00.00.000000000000')
);

CREATE INDEX IX_BENE_HIST_PK
    ON BENEFICIARY_HIST (BENE_ID, SYS_END DESC)
    USING STOGROUP SGCLAIMS;

-- Enable versioning
ALTER TABLE BENEFICIARY
    ADD VERSIONING USE HISTORY TABLE BENEFICIARY_HIST;

Existing Program Impact

A critical advantage of system-period temporal tables: existing COBOL programs required zero changes. Every UPDATE and DELETE statement already written continued to work. DB2 handled the history archival transparently. The 340 COBOL programs in the claims suite didn't need a single line of modification for the base temporal functionality.

"That was the selling point for management," Diane said. "No application changes, no regression testing of 340 programs, no deployment risk. The only changes were DDL — ALTER TABLE to add the temporal columns and versioning."

The one caveat: programs that used SELECT * broke when the temporal columns were added (three new columns in the result set). Pinnacle had a coding standard prohibiting SELECT *, but seven legacy programs violated it. Those seven required modification.

Phase 2: Bi-Temporal for Coverage Tables

The ELIGIBILITY and BENEFIT_PLAN tables required bi-temporal support. These tables have business-meaningful validity periods (a beneficiary's coverage is effective from date X to date Y), and the audit requires knowing both what the coverage was (business time) and when the system recorded it (system time).

Example scenario that only bi-temporal handles correctly:

1. January 15: Beneficiary B001 enrolled with Plan P100, effective January 1 through December 31.
2. March 3: Claim processed for B001, dated February 15. System shows Plan P100 active.
3. April 10: Retroactive correction — B001's plan was actually P200 starting February 1 (employer submitted late enrollment change).
4. CMS audit asks: "What plan did your system show for B001 when you adjudicated the February 15 claim on March 3?"

With system-period only, querying FOR SYSTEM_TIME AS OF '2025-03-03' returns the record as it existed on March 3 — which shows Plan P100 effective January 1. Correct.

But the CMS auditor also asks: "What is the correct coverage for February 15 according to your current records?" That's a business-time query: FOR BUSINESS_TIME AS OF '2025-02-15' returns Plan P200 (the retroactive correction). The auditor can then see both what the system believed and what was actually correct, and evaluate whether the claim should be reprocessed.

CREATE TABLE ELIGIBILITY
(
    BENE_ID          CHAR(10)       NOT NULL,
    PLAN_CODE        CHAR(6)        NOT NULL,
    COVERAGE_TYPE    CHAR(3)        NOT NULL,
    COPAY_AMT        DECIMAL(7,2)   NOT NULL,
    DEDUCTIBLE_AMT   DECIMAL(9,2)   NOT NULL,
    OOP_MAX          DECIMAL(9,2)   NOT NULL,
    COV_START        DATE           NOT NULL,
    COV_END          DATE           NOT NULL,
    SYS_START        TIMESTAMP(12)  NOT NULL
                     GENERATED ALWAYS AS ROW BEGIN,
    SYS_END          TIMESTAMP(12)  NOT NULL
                     GENERATED ALWAYS AS ROW END,
    TXN_START_ID     TIMESTAMP(12)
                     GENERATED ALWAYS AS TRANSACTION
                     START ID,
    PERIOD BUSINESS_TIME (COV_START, COV_END),
    PERIOD SYSTEM_TIME (SYS_START, SYS_END)
)
IN DBCLAIMS.TSELIG;

The bi-temporal query that answers the auditor's complete question:

-- What the system showed on March 3 for February 15 coverage
SELECT BENE_ID, PLAN_CODE, COVERAGE_TYPE,
       COPAY_AMT, DEDUCTIBLE_AMT, OOP_MAX,
       COV_START, COV_END,
       SYS_START, SYS_END
FROM ELIGIBILITY
FOR SYSTEM_TIME AS OF '2025-03-03-12.00.00.000000'
FOR BUSINESS_TIME AS OF '2025-02-15'
WHERE BENE_ID = 'B000000001';

-- What we currently know about February 15 coverage
SELECT BENE_ID, PLAN_CODE, COVERAGE_TYPE,
       COPAY_AMT, DEDUCTIBLE_AMT, OOP_MAX,
       COV_START, COV_END,
       SYS_START, SYS_END
FROM ELIGIBILITY
FOR BUSINESS_TIME AS OF '2025-02-15'
WHERE BENE_ID = 'B000000001';

Phase 3: Audit Response Automation

Ahmad built a COBOL-DB2 program (AUDITRESP) that could answer any CMS audit query in seconds. Given a claim ID and adjudication date, the program:

1. Retrieves the claim from CLAIM_HEADER using FOR SYSTEM_TIME AS OF the adjudication timestamp
2. Retrieves all claim lines from CLAIM_LINE for that claim using the same temporal clause
3. Retrieves beneficiary eligibility using bi-temporal query (system time = adjudication date, business time = service date from the claim)
4. Retrieves provider network status using FOR SYSTEM_TIME AS OF the adjudication timestamp
5. Retrieves the fee schedule in effect using FOR SYSTEM_TIME AS OF the adjudication timestamp
6. Retrieves any prior authorization records using FOR SYSTEM_TIME AS OF the adjudication timestamp
7. Produces a formatted report showing the complete state of all relevant records at adjudication time

       PROCEDURE DIVISION.
       MAIN-LOGIC.
           PERFORM GET-CLAIM-DETAILS.
           PERFORM GET-BENE-ELIGIBILITY.
           PERFORM GET-PROVIDER-STATUS.
           PERFORM GET-FEE-SCHEDULE.
           PERFORM GET-PRIOR-AUTH.
           PERFORM GENERATE-AUDIT-REPORT.
           STOP RUN.

       GET-BENE-ELIGIBILITY.
           EXEC SQL
               SELECT PLAN_CODE, COVERAGE_TYPE,
                      COPAY_AMT, DEDUCTIBLE_AMT,
                      OOP_MAX, COV_START, COV_END,
                      SYS_START, SYS_END
               INTO :WS-PLAN-CODE, :WS-COV-TYPE,
                    :WS-COPAY, :WS-DEDUCTIBLE,
                    :WS-OOP-MAX, :WS-COV-START,
                    :WS-COV-END, :WS-SYS-START,
                    :WS-SYS-END
               FROM ELIGIBILITY
               FOR SYSTEM_TIME AS OF :WS-ADJUD-TSTAMP
               FOR BUSINESS_TIME AS OF :WS-SERVICE-DATE
               WHERE BENE_ID = :WS-BENE-ID
           END-EXEC.

           EVALUATE SQLCODE
               WHEN 0
                   MOVE 'Y' TO WS-ELIG-FOUND
               WHEN 100
                   MOVE 'N' TO WS-ELIG-FOUND
                   STRING 'NO ELIGIBILITY RECORD FOUND '
                       'FOR BENE ' WS-BENE-ID
                       ' AS OF ' WS-ADJUD-TSTAMP
                       DELIMITED BY SIZE
                       INTO WS-ELIG-MSG
               WHEN OTHER
                   PERFORM HANDLE-SQL-ERROR
           END-EVALUATE.

The program processes one claim per execution and runs in under 2 seconds. For the 2,400-claim audit sample, Ahmad ran it in batch with a driver program that read the claim list and called AUDITRESP for each — total elapsed time: 47 minutes, producing a complete, formatted audit response package.

"What took six weeks manually now takes under an hour," Ahmad said. "And the temporal queries are deterministic — they return the same answer every time, based on what the database actually contained. No reconstruction logic, no assumptions about audit trail completeness."

Performance Impact and Mitigation

The temporal table implementation did have performance consequences:

DML Overhead

The nightly claims batch cycle — which processes 200,000 claims with an average of 8 updates per claim across the 12 temporal tables — saw elapsed time increase from 2.8 hours to 4.3 hours (54% increase). Each UPDATE now triggered a history INSERT on the corresponding history table.

Diane and Ahmad mitigated this by:

1. Converting to multi-row operations (Chapter 7 techniques): The claims batch used single-row FETCH and single-row UPDATE. Converting to multi-row FETCH (rowset 500) and batching the history-generating UPDATEs reduced thread-switching overhead by 98%, bringing elapsed time down to 1.9 hours — faster than the original non-temporal batch.

2. Separating history tablespaces to flash storage: History tables were placed on DS8950 flash arrays with sub-millisecond latency, eliminating the I/O bottleneck for the additional writes.

Storage Growth

After one year of temporal operation, the 12 history tables consumed:

The retention policy was set to 10 years (CMS audit lookback period). Diane implemented a monthly partition management job that: - Adds a new empty partition at the end of each history table - Drops partitions older than 10 years (once the system has been running long enough) - Runs RUNSTATS on the most recent partitions - Takes image copies of the rotated partitions for archive

Query Performance for Auditors

Point-in-time queries on the history tables performed well thanks to the (primary_key, SYS_END DESC) index. A typical query — "show me beneficiary B001's record as of timestamp T" — resolved via index lookup in under 10 milliseconds, even with 16.8 million rows in the history table.

Range queries were slower: "show me all changes to beneficiary B001 between January 2024 and December 2024" required scanning all history rows for that beneficiary. For beneficiaries with many changes, this could take 1–2 seconds. Acceptable for audit response, not acceptable for online inquiry. Diane added a secondary index on (BENE_ID, SYS_START) for range query optimization.

Unexpected Benefits

Beyond audit compliance, temporal tables provided three unexpected benefits:

1. Bug investigation. When a claims adjudication error was reported, Ahmad could query the temporal tables to see exactly what data the system had when it made the adjudication decision. This replaced the previous process of reading application logs, examining audit trails, and guessing what the program saw. "Temporal tables turned a two-day investigation into a ten-minute query," Ahmad said.

2. Retroactive correction impact analysis. When a provider's fee schedule was retroactively corrected, Pinnacle needed to identify all claims that were priced using the old schedule. A temporal query — FOR SYSTEM_TIME FROM old_effective_date TO correction_date WHERE provider_id = X — instantly identified every affected claim.

3. Data quality monitoring. Diane wrote a daily report that compared yesterday's temporal state against today's. Any record that changed more than expected (e.g., a beneficiary record updated 50 times in one day) was flagged for review. This caught a data entry error within hours instead of the weeks it would have taken to surface through normal business processes.

Lessons Learned

1. System-period temporal is nearly zero application risk. The 340 existing programs required no changes (except seven that used SELECT *). The temporal columns and versioning are entirely database-side.

2. Bi-temporal is essential for tables with business-meaningful date ranges. System-period alone can't answer "what coverage did this person have on date X according to our records on date Y?" Both dimensions matter for audit and compliance.

3. History table storage planning must be done before implementation. Nearly 500 GB in the first year, projected to reach 5 TB at steady state (10-year retention). This must be budgeted in the DASD capacity plan.

4. Multi-row operations and temporal tables are complementary. The DML overhead of temporal tables is offset by the efficiency gains of multi-row operations. Implementing both together yielded better performance than the original non-temporal, single-row system.

5. Partition management is not optional. Without monthly partition rotation, the history tables would become unmanageable. The partition management job must be scheduled, monitored, and tested as rigorously as any production batch job.

Discussion Questions

1. Pinnacle chose a 10-year retention period based on CMS audit requirements. How would you determine the retention period for your organization's temporal tables? What factors beyond regulatory requirements would you consider?

2. The seven programs that used SELECT * broke when temporal columns were added. What other SQL patterns might break or behave unexpectedly when temporal columns are added to existing tables?

3. Ahmad's AUDITRESP program processes one claim at a time. How would you redesign it to process the entire 2,400-claim audit sample as a batch job using multi-row FETCH and temporal queries?

4. Diane placed history tables on flash storage to mitigate DML overhead. What alternatives would you consider if flash storage were not available in your environment?

5. The bi-temporal example showed a retroactive enrollment correction. How should the claims adjudication system handle the discovery that a claim was adjudicated against incorrect eligibility data? Should it automatically readjudicate, flag for manual review, or something else?