Case Study 2: Pinnacle Health's Claims Query Optimization

A Join Methods Comparison That Saved $400K in Annual CPU Costs

The Setting

Pinnacle Health Insurance processes 50 million claims per month. Their DB2 environment holds 2.4 billion claim records in the CLAIMS tablespace (3.2 TB) and 800 million line items in CLAIM_DETAILS (1.8 TB). A suite of 14 batch programs and 6 CICS online transactions query these tables constantly.

Diane Okoye, the lead architect, has been tracking a growing problem. The Claims Provider Report — a nightly batch program called CLMPROV1 — has been consuming more CPU every month. When it was written five years ago, it completed in 35 minutes. Last month, it took 2 hours and 47 minutes. The growth is linear and predictable: approximately 3 minutes per month as data accumulates.

Ahmad Rashidi, the compliance officer, has added urgency. A new regulatory requirement demands that the Provider Report include additional claim categories and produce output within a 90-minute window. With the current trajectory, the program will exceed 90 minutes next quarter — even before adding the new categories.

Diane decides to investigate. Not just patch the immediate problem, but understand the join methods at play and choose the optimal approach for the next five years of data growth.

The Current State

The Query

CLMPROV1's core query (simplified but structurally accurate):

SELECT C.PROVIDER_ID,
       C.PROVIDER_STATE,
       C.CLAIM_TYPE,
       C.CLAIM_STATUS,
       SUM(D.LINE_AMOUNT) AS TOTAL_AMOUNT,
       COUNT(DISTINCT C.CLAIM_ID) AS CLAIM_COUNT,
       COUNT(*) AS LINE_COUNT
FROM   CLAIMS C
       INNER JOIN CLAIM_DETAILS D
         ON C.CLAIM_ID = D.CLAIM_ID
WHERE  C.CLAIM_DATE BETWEEN :WS-START-DATE AND :WS-END-DATE
  AND  C.CLAIM_STATUS IN ('PAID', 'DENIED', 'PEND')
  AND  C.PROVIDER_STATE = :WS-STATE-CODE
GROUP BY C.PROVIDER_ID, C.PROVIDER_STATE,
         C.CLAIM_TYPE, C.CLAIM_STATUS
ORDER BY C.PROVIDER_ID, C.CLAIM_TYPE;

The program loops through all 52 state/territory codes, executing this query once per state. Total: 52 executions per night.

The Current EXPLAIN

QUERYNO  PLANNO  METHOD  TABLE_NAME       ACCESSTYPE  MATCHCOLS  INDEX_NAME          INDEXONLY  PREFETCH  SORTN_JOIN  SORTC_JOIN  SORTN_ORDERBY  SORTN_GROUPBY
-------  ------  ------  ---------------  ----------  ---------  ------------------  --------  --------  ----------  ----------  -------------  -------------
10       1       0       CLAIMS           I           1          IX_CLM_STATE        N         L         Y           N           N              N
10       2       2       CLAIM_DETAILS    I           0          IX_CDET_CLMID       N         L         N           Y           N              N
10       3       0       SORT_COMPOSITE   --          --         --                  --        --        --          --          Y              Y

Reading this: - CLAIMS: Accessed via index IX_CLM_STATE with 1 matching column (PROVIDER_STATE). List prefetch — low CLUSTERRATIO. Sort for join. - CLAIM_DETAILS: Non-matching index scan (MATCHCOLS=0) on IX_CDET_CLMID. List prefetch. Sort for merge. - SORT_COMPOSITE: Sorts for ORDER BY and GROUP BY on the merged result.

This is a merge scan join with two sorts on the input sides and two sorts on the output. Four sort operations.

The Statistics

SELECT NAME, COLCARDF FROM SYSIBM.SYSCOLUMNS
WHERE TBNAME = 'CLAIMS' AND TBCREATOR = 'PROD'
  AND NAME IN ('PROVIDER_STATE', 'CLAIM_DATE', 'CLAIM_STATUS', 'CLAIM_TYPE');

NAME              COLCARDF
----------------  ----------
PROVIDER_STATE    52
CLAIM_DATE        1826      -- 5 years of dates
CLAIM_STATUS      5
CLAIM_TYPE        24

SELECT IXNAME, CLUSTERRATIO, FIRSTKEYCARDF, FULLKEYCARDF, NLEAF
FROM   SYSIBM.SYSINDEXES
WHERE  TBNAME = 'CLAIMS' AND TBCREATOR = 'PROD';

IXNAME            CLUSTERRATIO  FIRSTKEYCARDF  FULLKEYCARDF   NLEAF
-----------------  ------------  -------------  ------------   --------
IX_CLM_PK         98            2400000000     2400000000     4,200,000
IX_CLM_STATE      3             52             2400000000     4,800,000
IX_CLM_DATE       95            1826           2400000000     4,100,000
IX_CLM_STATUS     2             5              2400000000     3,900,000

CLUSTERRATIO = 3 for IX_CLM_STATE. The data is clustered by CLAIM_DATE (primary insert order), not by state. Accessing by state means essentially random I/O across the entire 3.2 TB tablespace.

Diane's Analysis

Why the Current Plan Is Bad

The merge scan join was chosen because:

1. IX_CLM_STATE has MATCHCOLS=1 but CLUSTERRATIO=3. The optimizer knows that index access will produce nearly random I/O. List prefetch mitigates this somewhat by sorting RIDs, but for millions of rows it's still expensive.

2. IX_CDET_CLMID has MATCHCOLS=0. A non-matching index scan — DB2 is reading the entire index leaf chain. This happens because the optimizer decided that for the merge join, it needs CLAIM_DETAILS sorted by CLAIM_ID, and scanning the index (which is already in CLAIM_ID order) is cheaper than sorting the table.

3. Four sorts consume CPU and write to DSNDB07 work files.

Filter Factor Analysis

For a single state (e.g., PROVIDER_STATE = 'CA'):

FF(PROVIDER_STATE = 'CA') = 1/52 = 0.0192
FF(CLAIM_DATE range, 1 month) = 30/1826 = 0.0164
FF(CLAIM_STATUS IN list) = 3/5 = 0.60
Combined FF = 0.0192 * 0.0164 * 0.60 = 0.000189
Estimated rows from CLAIMS = 2,400,000,000 * 0.000189 = 453,600

453,600 rows per state from CLAIMS. Each of those joins to an average of 3.3 line items in CLAIM_DETAILS, producing ~1.5M output rows per state.

The problem: 453,600 qualifying rows from CLAIMS, but accessed via an index with CLUSTERRATIO = 3. Each qualifying row potentially fetches a different 4K page from the 3.2 TB tablespace. Even with list prefetch, this is devastating.

Option 1: Better Indexing for the Current Approach

Create a composite index that serves this query's predicates better:

CREATE INDEX IX_CLM_STATEDTSTAT
  ON CLAIMS (PROVIDER_STATE, CLAIM_DATE, CLAIM_STATUS)
  INCLUDE (PROVIDER_ID, CLAIM_TYPE, CLAIM_ID);

This would give MATCHCOLS = 3 (state equality, date range, status in-list can be matched after date range ends matching — actually MATCHCOLS = 2 because the IN-list on the third column after a range on the second column is only screening). With the INCLUDE columns, this might even enable index-only access on CLAIMS.

But the CLUSTERRATIO problem remains. Even with a better index, the base table access for CLAIM_DETAILS will still be driven by CLAIM_ID order, and CLAIM_DETAILS is clustered by CLAIM_ID (good for nested loop) but the volume is massive.

Option 2: Hash Join

What if DB2 used a hash join instead of merge scan?

Build phase: Read qualifying CLAIMS rows (453,600 per state) into hash table
Probe phase: Scan CLAIM_DETAILS, probe hash table for each CLAIM_ID

Estimated hash table size: 453,600 rows * ~200 bytes = ~87 MB. This fits in memory.

The problem: probing CLAIM_DETAILS still requires reading the relevant rows. For a single state, the qualifying CLAIM_IDs are scattered across the 800M-row table. Without an index, DB2 would tablespace scan CLAIM_DETAILS (1.8 TB) for every state. Unacceptable.

Option 3: Restructure the Query

Diane's breakthrough idea: eliminate the per-state loop.

Instead of executing the query 52 times (once per state), execute it once for all states:

SELECT C.PROVIDER_ID,
       C.PROVIDER_STATE,
       C.CLAIM_TYPE,
       C.CLAIM_STATUS,
       SUM(D.LINE_AMOUNT) AS TOTAL_AMOUNT,
       COUNT(DISTINCT C.CLAIM_ID) AS CLAIM_COUNT,
       COUNT(*) AS LINE_COUNT
FROM   CLAIMS C
       INNER JOIN CLAIM_DETAILS D
         ON C.CLAIM_ID = D.CLAIM_ID
WHERE  C.CLAIM_DATE BETWEEN :WS-START-DATE AND :WS-END-DATE
  AND  C.CLAIM_STATUS IN ('PAID', 'DENIED', 'PEND')
GROUP BY C.PROVIDER_ID, C.PROVIDER_STATE,
         C.CLAIM_TYPE, C.CLAIM_STATUS
ORDER BY C.PROVIDER_STATE, C.PROVIDER_ID, C.CLAIM_TYPE;

Remove the PROVIDER_STATE predicate entirely. Fetch all states at once. This changes the optimizer's calculus completely.

New filter factors:

FF(CLAIM_DATE range, 1 month) = 30/1826 = 0.0164
FF(CLAIM_STATUS IN list) = 3/5 = 0.60
Combined FF = 0.0164 * 0.60 = 0.00984
Estimated rows from CLAIMS = 2,400,000,000 * 0.00984 = 23,616,000

23.6 million rows. Now the optimizer prefers: - CLAIMS: Use IX_CLM_DATE (CLUSTERRATIO = 95!) with MATCHCOLS = 1. Sequential prefetch through the date range. Efficient. - CLAIM_DETAILS: Nested loop join using IX_CDET_CLMID with MATCHCOLS = 1.

Wait — 23.6 million nested loop probes? That seems like a lot. Let's think about this.

Each probe is a B-tree traversal (3 levels in NLEVELS) plus 1–4 leaf page reads plus base table page access. With CLAIM_DETAILS clustered by CLAIM_ID and the claims being accessed in CLAIM_DATE order (which has some correlation with CLAIM_ID for recently filed claims), there may be buffer pool reuse.

But Diane considers a fourth option.

Option 4: Hash Join on the Single-Execution Query

CLAIMS: Index scan on IX_CLM_DATE (MATCHCOLS=1, CLUSTERRATIO=95)
        → 23.6 million qualifying rows
CLAIM_DETAILS: Hash join
        → Build hash table from CLAIMS result (23.6M rows * 50 bytes key = 1.1 GB)
        → Scan CLAIM_DETAILS by CLAIM_ID index for matching CLAIM_IDs

The 1.1 GB hash table would spill to work files. Not ideal but manageable.

After analysis, Diane settles on a hybrid approach.

The Solution

Approach: Single-Execution Query with Optimized Indexing

Step 1: Create a new index on CLAIMS optimized for date-range access with covering columns:

CREATE INDEX IX_CLM_DATE_COVR
  ON CLAIMS (CLAIM_DATE, CLAIM_STATUS)
  INCLUDE (CLAIM_ID, PROVIDER_ID, PROVIDER_STATE, CLAIM_TYPE)
  CLUSTER;

The CLUSTER keyword requests that this index's key order be maintained by REORG. With CLAIM_DATE as the leading column, new claims (inserted with recent dates) naturally cluster well.

This index gives: - MATCHCOLS = 1 on CLAIM_DATE (range) - CLAIM_STATUS as screening column - INCLUDE columns enable index-only access for all CLAIMS columns in the query - No base table access needed for CLAIMS at all

Step 2: Ensure IX_CDET_CLMID exists and is well-maintained on CLAIM_DETAILS:

-- Already exists, but add INCLUDE for index-only potential
CREATE INDEX IX_CDET_CLMID_COVR
  ON CLAIM_DETAILS (CLAIM_ID)
  INCLUDE (LINE_AMOUNT);

With LINE_AMOUNT included, the CLAIM_DETAILS access is also index-only. DB2 never touches the base tables for either side of the join.

Step 3: Restructure the COBOL program:

The COBOL program changes from:

* OLD: Loop through 52 states
PERFORM VARYING WS-STATE-IDX FROM 1 BY 1
  UNTIL WS-STATE-IDX > 52
  MOVE STATE-TABLE(WS-STATE-IDX) TO WS-STATE-CODE
  EXEC SQL
    SELECT ... WHERE PROVIDER_STATE = :WS-STATE-CODE
    ...
  END-EXEC
  PERFORM PROCESS-STATE-RESULTS
END-PERFORM

To:

* NEW: Single execution, all states
EXEC SQL
  DECLARE CSR_PROVIDER_RPT CURSOR FOR
  SELECT PROVIDER_ID, PROVIDER_STATE, CLAIM_TYPE,
         CLAIM_STATUS, SUM(LINE_AMOUNT),
         COUNT(DISTINCT CLAIM_ID), COUNT(*)
  FROM   CLAIMS C
         INNER JOIN CLAIM_DETAILS D
           ON C.CLAIM_ID = D.CLAIM_ID
  WHERE  C.CLAIM_DATE BETWEEN :WS-START-DATE
                           AND :WS-END-DATE
    AND  C.CLAIM_STATUS IN ('PAID', 'DENIED', 'PEND')
  GROUP BY PROVIDER_ID, PROVIDER_STATE,
           CLAIM_TYPE, CLAIM_STATUS
  ORDER BY PROVIDER_STATE, PROVIDER_ID, CLAIM_TYPE
END-EXEC

EXEC SQL OPEN CSR_PROVIDER_RPT END-EXEC

PERFORM UNTIL SQLCODE = +100
  EXEC SQL
    FETCH CSR_PROVIDER_RPT
    INTO :WS-PROVIDER-ID, :WS-STATE, :WS-CLM-TYPE,
         :WS-CLM-STATUS, :WS-TOTAL-AMT,
         :WS-CLM-COUNT, :WS-LINE-COUNT
  END-EXEC
  IF SQLCODE = 0
    PERFORM PROCESS-ROW
  END-IF
END-PERFORM

EXEC SQL CLOSE CSR_PROVIDER_RPT END-EXEC

Step 4: RUNSTATS and REBIND:

//STEP01   EXEC DSNUPROC,SYSTEM=DB2P,UID='RSTCLM',UTPROC=''
//SYSIN    DD *
  RUNSTATS TABLESPACE DBPROD01.TSCLAIMS
    TABLE(PROD.CLAIMS)
    INDEX(PROD.IX_CLM_DATE_COVR)
    COLUMN(CLAIM_DATE) FREQVAL COUNT 30 MOST
    COLUMN(CLAIM_STATUS) FREQVAL COUNT 10 MOST
    COLGROUP(CLAIM_DATE, CLAIM_STATUS)
    SHRLEVEL CHANGE
    UPDATE ALL
  RUNSTATS TABLESPACE DBPROD01.TSCDET
    TABLE(PROD.CLAIM_DETAILS)
    INDEX(PROD.IX_CDET_CLMID_COVR)
    SHRLEVEL CHANGE
    UPDATE ALL
/*
//STEP02   EXEC PGM=IKJEFT01
//SYSTSIN  DD *
  DSN SYSTEM(DB2P)
  BIND PACKAGE(CLMPROV) MEMBER(CLMPROV1) -
       EXPLAIN(YES) PLANMGMT(EXTENDED)
  END
/*

The New EXPLAIN

QUERYNO  PLANNO  METHOD  TABLE_NAME       ACCESSTYPE  MATCHCOLS  INDEX_NAME            INDEXONLY  PREFETCH  SORTN_JOIN  SORTC_JOIN  SORTN_ORDERBY  SORTN_GROUPBY
-------  ------  ------  ---------------  ----------  ---------  --------------------  --------  --------  ----------  ----------  -------------  -------------
10       1       0       CLAIMS           I           1          IX_CLM_DATE_COVR      Y         S         N           N           N              N
10       2       1       CLAIM_DETAILS    I           1          IX_CDET_CLMID_COVR    Y                   N           N           N              N
10       3       0       SORT_COMPOSITE   --          --         --                    --        --        --          --          Y              Y

Reading the new plan: - CLAIMS: Index-only access (INDEXONLY = Y) via IX_CLM_DATE_COVR with 1 matching column. Sequential prefetch on the index leaf pages. No base table I/O. No join sort needed. - CLAIM_DETAILS: Nested loop join (METHOD = 1). Index-only access (INDEXONLY = Y) via IX_CDET_CLMID_COVR. No base table I/O. - One sort for ORDER BY/GROUP BY on the final result.

From four sorts and two base table scans to one sort and zero base table access.

The Results

Performance Comparison

Cost Impact

At Pinnacle Health's negotiated MSU rate, the CPU reduction of 36 minutes per night translates to: - ~$1,100/day in reduced CPU costs - ~$400,000/year - Plus elimination of DSNDB07 contention that was delaying other batch jobs by 5–15 minutes nightly

Scalability

Diane's projection for data growth:

The new approach stays well within the 90-minute regulatory window for the foreseeable future. The old approach would have exceeded it within 6 months.

Key Lessons

On Join Method Selection

1. Merge scan join is not inherently bad — it's optimal when both tables are large and no useful index exists. But when you can restructure the query to make nested loop viable (through better indexing or query consolidation), nested loop with a good index often wins.

2. Index-only access is the ultimate optimizer gift. When both sides of a join are index-only, you've eliminated all base table I/O. The join operates entirely in the index B-tree structures, which are far smaller than the tablespaces.

3. Sort elimination is often the biggest win. Sorts consume CPU, require work file space, and create contention. Reducing four sorts to one was a larger factor in the CPU improvement than the access path change itself.

On Query Design

4. 52 queries vs. 1 query is not a neutral choice. The per-state loop forced the optimizer to treat each execution independently. The single-query approach let DB2 use the date-range index (CLUSTERRATIO=95) instead of the state index (CLUSTERRATIO=3). Sometimes restructuring the program is more effective than adding indexes.

5. The INCLUDE clause on CREATE INDEX changed the optimization landscape. Before INCLUDE columns, creating a covering index for this query would have required all six columns in the index key, bloating the B-tree non-leaf pages. INCLUDE columns keep the key narrow (2 columns) while covering the query (6 columns in the leaf).

On the Optimization Process

6. Measure before you change. Diane's analysis calculated filter factors, estimated I/O patterns, and compared options on paper before touching production. This discipline prevented trial-and-error changes that might have made things worse.

7. Design for five years, not five months. The regulatory 90-minute window is a hard constraint. Diane's solution doesn't just meet it today — it projects 38 minutes even at Year +5 data volumes. That margin of safety is an architectural decision.

Discussion Questions

1. Diane chose to eliminate the per-state loop and execute one query. What if the output format requires separate files per state? How would you handle the COBOL program restructuring?

2. The new covering indexes (IX_CLM_DATE_COVR and IX_CDET_CLMID_COVR) add storage overhead for the INCLUDE columns. On a 3.2 TB CLAIMS table, estimate the additional storage for the covering index versus the non-covering version. Is the trade-off worthwhile?

3. Ahmad Rashidi's new regulatory requirement adds three claim categories to the report. How does this change affect the optimization? Would you modify the index design?

4. The hash join option was considered but rejected. Under what data distribution would hash join have been the better choice? (Hint: consider what happens when CLAIM_DATE clustering deteriorates.)

5. Diane's projections assume linear data growth. If Pinnacle Health acquires another insurer and data doubles overnight, what breaks in the new design, and how would you prepare?

6. Compare the optimization approaches in Case Study 1 (fix statistics, preserve plan) vs. Case Study 2 (restructure query and indexes). When is each approach appropriate? Can you articulate a decision framework?