Case Study 2: MedClaim Provider File Redesign

Generated by Claude

Case Study 2: MedClaim Provider File Redesign

Background

MedClaim Health Services processes 500,000 insurance claims per month. Every claim must be validated against the PROVIDER-FILE — a VSAM KSDS containing records for 85,000 healthcare providers (doctors, hospitals, clinics, pharmacies, and laboratories).

The original PROVIDER-FILE had a single access path: the provider ID (PRV-PROVIDER-ID). But the claims adjudication team, led by James Okafor, faced a growing problem: claims from some states arrived with the provider's NPI (National Provider Identifier) number instead of MedClaim's internal provider ID. The workaround — a batch lookup program that ran nightly to cross-reference NPIs to provider IDs — introduced a 24-hour delay in claims processing.

Sarah Kim, the business analyst, estimated that 30% of incoming claims used NPI numbers, and regulatory changes would push that to 60% within two years. "We cannot process claims with a 24-hour lookup delay when competitors adjudicate in real time," she told the team.

The Problem

Current State

Claim arrives with NPI number
    → Wait for nightly batch cross-reference
    → Next morning: NPI mapped to Provider ID
    → Claim enters adjudication pipeline
    → Total time: 24-36 hours

Desired State

Claim arrives with NPI number
    → Real-time lookup by NPI (alternate key)
    → Immediate adjudication
    → Total time: seconds

Technical Analysis

James Okafor analyzed the options:

Maintain a separate NPI-to-Provider cross-reference file: More data to manage, synchronization issues
Add an alternate index on NPI: Clean, automatic, standard VSAM feature
Switch to DB2: Massive effort, long timeline, overkill for this requirement

The team chose option 2 — an alternate index on the NPI field.

Implementation

Step 1: Define the Alternate Index

Tomás Rivera, the DBA, defined the alternate index:

//DEFNPIAI EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//SYSIN    DD *
  DEFINE ALTERNATEINDEX -
    (NAME(MEDCLM.PROVIDER.NPI.AIX) -
     RELATE(MEDCLM.PROVIDER.MASTER) -
     KEYS(10 10) -
     UNIQUEKEY -
     UPGRADE -
     RECORDSIZE(30 30) -
     CYLINDERS(2 1))

  DEFINE PATH -
    (NAME(MEDCLM.PROVIDER.NPI.PATH) -
     PATHENTRY(MEDCLM.PROVIDER.NPI.AIX))

  BLDINDEX -
    INDATASET(MEDCLM.PROVIDER.MASTER) -
    OUTDATASET(MEDCLM.PROVIDER.NPI.AIX)
/*

Key decisions: - KEYS(10 10): NPI is 10 bytes starting at offset 10 in the record - UNIQUEKEY: Each NPI is unique (unlike names or specialty codes) - UPGRADE: The alternate index is automatically maintained on insert/update/delete

Step 2: Add a Second Alternate Index

While they were at it, Sarah Kim requested an alternate index on specialty code for reporting:

  DEFINE ALTERNATEINDEX -
    (NAME(MEDCLM.PROVIDER.SPEC.AIX) -
     RELATE(MEDCLM.PROVIDER.MASTER) -
     KEYS(3 60) -
     NONUNIQUEKEY -
     UPGRADE -
     RECORDSIZE(20 500) -
     CYLINDERS(1 1))

  DEFINE PATH -
    (NAME(MEDCLM.PROVIDER.SPEC.PATH) -
     PATHENTRY(MEDCLM.PROVIDER.SPEC.AIX))

Note NONUNIQUEKEY — many providers share the same specialty code. Also note the variable RECORDSIZE: non-unique alternate index records can grow large because they must store the primary keys of all records sharing that alternate key value.

Step 3: Modify the COBOL Programs

The claims adjudication program's SELECT statement changed from:

      *--- BEFORE: Primary key only
           SELECT PROVIDER-FILE
               ASSIGN TO PROVFILE
               ORGANIZATION IS INDEXED
               ACCESS MODE IS RANDOM
               RECORD KEY IS PRV-PROVIDER-ID
               FILE STATUS IS WS-PRV-STATUS.

To:

      *--- AFTER: Primary key + two alternate keys
           SELECT PROVIDER-FILE
               ASSIGN TO PROVFILE
               ORGANIZATION IS INDEXED
               ACCESS MODE IS DYNAMIC
               RECORD KEY IS PRV-PROVIDER-ID
               ALTERNATE RECORD KEY IS PRV-NPI-NUMBER
               ALTERNATE RECORD KEY IS PRV-SPECIALTY-CODE
                   WITH DUPLICATES
               FILE STATUS IS WS-PRV-STATUS.

Step 4: New Lookup Logic

James Okafor wrote a dual-lookup routine that could handle claims arriving with either identifier:

       LOOKUP-PROVIDER.
           EVALUATE TRUE
               WHEN CLM-HAS-PROVIDER-ID
                   MOVE CLM-PROVIDER-ID TO PRV-PROVIDER-ID
                   READ PROVIDER-FILE
                       KEY IS PRV-PROVIDER-ID
                       INVALID KEY
                           PERFORM PROVIDER-NOT-FOUND
                       NOT INVALID KEY
                           PERFORM PROVIDER-FOUND
                   END-READ

               WHEN CLM-HAS-NPI-NUMBER
                   MOVE CLM-NPI-NUMBER TO PRV-NPI-NUMBER
                   READ PROVIDER-FILE
                       KEY IS PRV-NPI-NUMBER
                       INVALID KEY
                           PERFORM PROVIDER-NOT-FOUND
                       NOT INVALID KEY
                           PERFORM PROVIDER-FOUND
                   END-READ

               WHEN OTHER
                   PERFORM NO-PROVIDER-IDENTIFIER
           END-EVALUATE.

Step 5: Specialty Code Reporting

Sarah Kim's reporting program used the specialty alternate key to generate provider network adequacy reports:

       REPORT-BY-SPECIALTY.
           MOVE WS-TARGET-SPECIALTY TO PRV-SPECIALTY-CODE

           START PROVIDER-FILE
               KEY IS EQUAL TO PRV-SPECIALTY-CODE
               INVALID KEY
                   DISPLAY 'No providers for specialty: '
                           WS-TARGET-SPECIALTY
                   GO TO REPORT-BY-SPECIALTY-EXIT
           END-START

           SET WS-NOT-EOF TO TRUE
           PERFORM UNTIL WS-EOF
               OR PRV-SPECIALTY-CODE NOT =
                  WS-TARGET-SPECIALTY
               READ PROVIDER-FILE NEXT
                   AT END
                       SET WS-EOF TO TRUE
               END-READ
               IF NOT WS-EOF
                   IF PRV-SPECIALTY-CODE =
                      WS-TARGET-SPECIALTY
                       ADD 1 TO WS-SPEC-COUNT
                       IF PRV-IN-NETWORK
                           ADD 1 TO WS-IN-NETWORK-COUNT
                       END-IF
                       PERFORM WRITE-PROVIDER-DETAIL
                   END-IF
               END-IF
           END-PERFORM.

       REPORT-BY-SPECIALTY-EXIT.
           EXIT.

Testing Challenges

Challenge 1: BLDINDEX Performance

Building the alternate indexes on the 85,000-record file took 12 minutes in the test environment. Tomás Rivera discovered that the BLDINDEX was sorting in memory and spilling to work files. Increasing the sort work space cut the time to 3 minutes.

Challenge 2: Write Performance Impact

With two alternate indexes (both with UPGRADE), WRITE operations took 40% longer than before. For the batch provider-load program that ran monthly, this extended the job from 8 minutes to 11 minutes — acceptable. For online CICS updates (rare — only a few per day), the impact was imperceptible.

Challenge 3: Status Code '02' Confusion

During testing, James noticed that writes to the provider file now returned status '02' instead of '00' when the specialty code matched an existing record. His initial error-handling code treated any non-'00' status as an error:

      *--- Original (broken with alternate keys)
           IF WS-PRV-STATUS NOT = '00'
               PERFORM ERROR-ROUTINE
           END-IF

      *--- Fixed
           IF NOT (WS-PRV-SUCCESS OR WS-PRV-DUP-ALT)
               PERFORM ERROR-ROUTINE
           END-IF

This was a critical reminder: status '02' means "successful completion with duplicate alternate key" and must be treated as success.

Results

Performance

Metric	Before	After	Change
Claims with NPI — lookup time	24-36 hours	< 100 ms	-99.99%
Provider write (batch)	5.2 ms/rec	7.3 ms/rec	+40%
Specialty report generation	45 min (full scan)	8 min (alt key browse)	-82%
Claims cleared same day	70%	97%	+27 points

Business Impact

Claims processing SLA improved from 3 days to 1 day
Customer satisfaction scores increased 15%
Regulatory compliance for real-time NPI lookup achieved 6 months ahead of deadline
Monthly specialty network adequacy reports now run in 8 minutes instead of 45

Discussion Questions

Why did the team choose UNIQUEKEY for the NPI alternate index but NONUNIQUEKEY for specialty? What would happen if NPI were defined as NONUNIQUEKEY?
The 40% write performance penalty was deemed acceptable. Under what circumstances would it become unacceptable? What alternatives exist?
James could have used a separate NPI cross-reference file (option 1) instead of an alternate index. Compare the data consistency guarantees of each approach.
Sarah Kim's specialty report previously did a full sequential scan of 85,000 records. With the alternate index, it reads only matching records. Calculate the approximate I/O savings for a specialty with 2,000 providers.
If MedClaim later needs to add a third alternate key (on provider ZIP code for geographic analysis), what is the cumulative impact on write performance? At what point should they consider a DB2 migration instead?