Case Study 27.2: MedClaim Claims Database Migration

Background

MedClaim's claim processing system originally used VSAM files for all data storage. The CLAIM-MASTER VSAM KSDS held 12 million active claims. As the system grew, several limitations became painful:

• No ad hoc query capability for business analysts
• Complex multi-file updates required manual coordination
• No transaction management (a program ABEND could leave files in an inconsistent state)
• Provider and member lookups required sequential scanning when the alternate index was unavailable

Tomás Rivera led the migration to DB2. The project spanned 18 months and required converting 35 COBOL programs from VSAM file I/O to embedded SQL.

Migration Strategy

Tomás chose a phased approach:

Phase 1 (Months 1-3): Parallel run. Both VSAM and DB2 were updated simultaneously. VSAM remained the system of record. This validated data migration accuracy.

Phase 2 (Months 4-9): DB2 primary, VSAM backup. DB2 became the system of record. VSAM was updated asynchronously as a rollback option.

Phase 3 (Months 10-18): DB2 only. VSAM files were decommissioned. All programs used embedded SQL exclusively.

Technical Challenges

Challenge 1: Batch Commit Strategy

The biggest technical decision was the batch commit interval for the nightly claim processing run, which processed 50,000-80,000 claims.

Tomás tested three strategies:

Strategy A was unacceptable — the batch window was 2 hours. Strategy C was risky — an ABEND at record 79,000 would require reprocessing all 80,000 records.

Tomás chose Strategy B with a refinement: the program wrote a checkpoint record to a control table at each COMMIT, recording the last successfully processed claim ID. On restart, the program queried the control table and resumed from the checkpoint.

       COMMIT-CHECKPOINT.
           EXEC SQL
               UPDATE BATCH_CONTROL
                  SET LAST_CLAIM_ID = :HV-CURRENT-CLAIM,
                      RECORDS_PROCESSED = :WS-RECORD-COUNT,
                      CHECKPOINT_TIME = CURRENT TIMESTAMP
                WHERE JOB_NAME = 'CLM-PROCESS'
                  AND RUN_DATE = CURRENT DATE
           END-EXEC

           EXEC SQL COMMIT END-EXEC

           DISPLAY "Checkpoint at record "
                   WS-RECORD-COUNT
                   " claim " HV-CURRENT-CLAIM.

Challenge 2: DCLGEN Discipline

With 35 programs accessing 8 DB2 tables, keeping host variable declarations synchronized was critical. Tomás established a strict rule: every program uses DCLGEN-generated copybooks. No manual host variable declarations.

When a DBA changed a column type (MEMBER_ID expanded from CHAR(10) to CHAR(12)), Tomás regenerated the DCLGEN copybook once, and all 35 programs picked up the change at their next compilation. Without DCLGEN, each program would need individual updates — a recipe for mismatched data types.

Challenge 3: NULL Handling

VSAM had no concept of NULL. Every field had a value, even if it was spaces or zeros. DB2 introduced NULLs for optional fields like DENIAL_REASON and OVERRIDE_CODE.

James Okafor's team initially forgot null indicator variables on several queries, producing sporadic -305 ABENDs whenever a claim had a NULL denial reason. The fix was a coding standard: every nullable column MUST have an indicator variable, documented in the DCLGEN copybook with a comment.

      *> NULLABLE columns — always use indicators
       01  HV-DENIAL-REASON       PIC X(4).
       01  NI-DENIAL-REASON       PIC S9(4) COMP.
      *> DENIAL_REASON is nullable — NI required

Challenge 4: VSAM-to-SQL Mindset Shift

The hardest challenge was cultural. COBOL programmers accustomed to VSAM thought in terms of: - READ NEXT (sequential processing) - REWRITE (update in place) - START/READ (keyed access)

SQL thinking required a different approach: - SELECT with WHERE clause (set-based queries) - UPDATE with WHERE clause (potentially affecting multiple rows) - Cursors as the bridge between set-based SQL and record-at-a-time COBOL

James noticed that early converted programs were "VSAM with SQL syntax" — they fetched every record one at a time even when a single UPDATE with a WHERE clause would do. He held code review sessions to retrain the team on set-based thinking.

Example of the evolution:

      *> VSAM-style thinking (inefficient)
       OPEN CURSOR, FETCH each record
       IF STATUS = 'EX' AND DATE < CUTOFF
           UPDATE ... WHERE CURRENT OF CURSOR
       END-IF

      *> SQL-style thinking (efficient)
       EXEC SQL
           UPDATE CLAIM_MASTER
              SET CLAIM_STATUS = 'AR'
            WHERE CLAIM_STATUS = 'EX'
              AND SERVICE_DATE < :HV-CUTOFF-DATE
       END-EXEC
       DISPLAY "Archived " SQLERRD(3) " claims"

Outcome

After 18 months, the migration was complete: - 35 programs converted from VSAM to DB2 - Batch processing time reduced by 30% (DB2 I/O is faster than VSAM for complex queries) - Business analysts gained ad hoc query access through QMF/SPUFI - Zero data integrity issues since migration (VSAM had averaged 2 per year) - Three ABENDs during the first month (all NULL-handling bugs), zero in the following months

Discussion Questions

1. Tomás chose a commit interval of 500 records. How would you determine the optimal interval for your environment? What factors influence this number?
2. The "VSAM with SQL syntax" anti-pattern is common during migrations. What training or code review practices would you implement to help procedural programmers think in set-based terms?
3. The phased migration (parallel run → DB2 primary → DB2 only) took 18 months. A "big bang" cutover could have been done in 3 months. Compare the risks and benefits of each approach.
4. NULL handling caused the most post-migration bugs. How would you prevent this category of errors? Consider both technical solutions (COALESCE, coding standards) and process solutions (code reviews, automated checks).