Case Study 1: CNB's Partitioned End-of-Day Processing

Case Study 1: CNB's Partitioned End-of-Day Processing

Background

City National Bank's end-of-day (EOD) batch processing has been under pressure for two years. When Kwame joined the mainframe team, the batch window was 11 PM to 5 AM — six comfortable hours. Today, with the international correspondent banking platform requiring a midnight start for European markets and the mobile banking platform demanding a 4:30 AM online availability, the window has compressed to four and a half hours.

The EOD batch processes 6.2 million transactions daily across 2.1 million active accounts. The core pipeline — transaction validation, account posting, interest calculation, and statement generation — runs serially in 6 hours 15 minutes. The math does not work.

Kwame raised the alarm at the quarterly architecture review. Rob, the senior systems programmer, confirmed what the batch logs showed: the window overrun had been papered over with temporary measures — delaying the mobile banking startup, skipping non-critical reports, running the GL posting as a "morning catch-up" job. None of these were sustainable.

Lisa, the DB2 DBA, pulled the performance data. The bottleneck was not CPU — the z15 had plenty of capacity during batch. It was not I/O — the DS8900F storage array could deliver far more throughput than the serial batch consumed. The bottleneck was serialization: each job waited for the previous job to complete, and within each job, records were processed one at a time. The machine was capable of far more concurrent work than the batch design allowed.

The decision was made: redesign the EOD batch for parallel processing.

The Partition Design Challenge

Lisa started with the transaction posting job — the longest step at 180 minutes. The program reads the day's validated transactions from a VSAM KSDS (sorted by account number), reads the corresponding account master record from DB2, applies the transaction, and writes the updated balance.

The natural partition key was account number. But CNB's account numbering was a legacy disaster. Account numbers were assigned sequentially as branches opened, starting in 1971. The original downtown branch (branch 001) had accounts 0010000001 through 0010999999. The second branch (branch 002) had 0020000001 through 0020999999. By 2024, CNB had 47 branches and had been through three mergers that folded in account ranges from acquired banks.

Lisa ran a distribution analysis:

SELECT SUBSTR(ACCOUNT_NUM, 1, 3) AS BRANCH_PREFIX,
       COUNT(*) AS ACCT_COUNT
FROM ACCOUNT_MASTER
GROUP BY SUBSTR(ACCOUNT_NUM, 1, 3)
ORDER BY ACCT_COUNT DESC;

The results were stark. Branch 001 (downtown) had 312,000 accounts. Branch 002 had 287,000. The 12 branches opened before 1985 collectively held 58% of all accounts. The 35 branches opened after 1985 held 42%. A naive four-way split by account range — 001–012, 013–024, 025–036, 037–047 — would put 58% of accounts in partition 1 and distribute the remaining 42% unevenly across partitions 2–4.

Lisa computed balanced partition boundaries using cumulative counts:

SELECT ACCOUNT_NUM,
       SUM(1) OVER (ORDER BY ACCOUNT_NUM
                     ROWS UNBOUNDED PRECEDING) AS CUMULATIVE
FROM ACCOUNT_MASTER
ORDER BY ACCOUNT_NUM;

She identified the account numbers at the 25th, 50th, and 75th percentiles of the distribution. The boundaries were not round numbers: 0012847293, 0024102887, 0037850441. Ugly, but balanced: each partition contained approximately 525,000 accounts (within 3% of equal).

These boundaries were loaded into the partition control table — not hardcoded in JCL. Lisa wrote a setup job that recomputes boundaries monthly based on current account distribution.

Implementation: The Partition-Safe Posting Program

Rob worked with Kwame on the COBOL program changes. The existing serial posting program (EODPOST) was a well-tested 3,200-line program that had run reliably for eight years. They did not want to rewrite it. They wanted to add a partition layer around the existing logic.

The key changes:

1. Partition initialization. A new paragraph at program start reads the PARM to get the partition number, queries the partition control table for key boundaries, and updates the partition status to 'R' (running).

2. Key range filtering. The existing VSAM read loop was modified to start at the partition's low key (using START with the low-key value) and stop when the account number exceeded the high key. This was a three-line change to the main read loop.

3. Checkpoint integration. The existing commit logic (every 500 DB2 updates) was extended to also write the current account number to the partition control table as a checkpoint. On restart, the program reads this checkpoint and repositions the VSAM file.

4. Deadlock handling. Rob added a deadlock retry wrapper around the DB2 UPDATE statement. The original program had no deadlock handling because, running serially, it never encountered deadlocks. With four concurrent copies hitting the same DB2 tablespace, deadlocks became possible despite row-level locking — particularly on index pages.

5. Completion reporting. On normal exit, the program updates the partition control table with status 'C', actual record count, and end timestamp. On abnormal exit, it updates with status 'F' and the last-processed key.

The modified program was 3,480 lines — only 280 lines added. The core business logic was untouched.

The Pipeline

Kwame designed the full pipeline:

EODSETUP   (serial, 3 min)
  - Analyze transaction file, compute partition boundaries
  - Populate partition control table
  - Allocate partition work datasets
  |
  +-- EODPOST-P01  (parallel, ~42 min)
  +-- EODPOST-P02  (parallel, ~44 min)
  +-- EODPOST-P03  (parallel, ~43 min)
  +-- EODPOST-P04  (parallel, ~45 min)
  |
EODMERGE   (serial, 8 min)
  - Merge four sorted partition output files
  - Reconcile record counts against input
  |
  +-- EODINTCALC-P01  (parallel, ~18 min)
  +-- EODINTCALC-P02  (parallel, ~19 min)
  +-- EODINTCALC-P03  (parallel, ~17 min)
  +-- EODINTCALC-P04  (parallel, ~18 min)
  |
  +-- EODFRAUD-P01  (parallel with INTCALC, ~20 min)
  +-- EODFRAUD-P02  (parallel with INTCALC, ~22 min)
  |
EODSTMT    (serial, 35 min — already fast enough)
EODRECONCILE (serial, 5 min)
EODGLPOST  (serial, 15 min)

Critical path: 3 + 45 + 8 + 19 + 35 + 5 + 15 = 130 minutes = 2 hours 10 minutes.

The serial pipeline took 6 hours 15 minutes. The parallel pipeline took 2 hours 10 minutes. CNB had gone from a 1-hour-45-minute overrun to a 2-hour-20-minute margin.

The DB2 Parallelism Layer

Lisa did not stop at application-level partitioning. She also enabled DB2 parallelism for the batch workload.

The ACCOUNT_MASTER table was already partitioned into 8 DB2 partitions (by account number range). Lisa rebound the EODPOST plan with DEGREE(ANY):

BIND PLAN(EODPOST) DEGREE(ANY) -
     ISOLATION(CS) -
     CURRENTDATA(NO) -
     RELEASE(COMMIT)

She sized buffer pool BP2 to 80,000 pages — enough for four concurrent batch threads, each scanning two DB2 partitions (aligned with the application partitions) with I/O parallelism.

The DB2 accounting traces showed that the SELECT statements within each application partition now used I/O parallelism degree 2 (reading both aligned DB2 partitions simultaneously). This shaved another 15% off each partition's elapsed time — the 45-minute posting step dropped to 38 minutes.

The Incident

Three weeks after go-live, partition 3 failed at 1:47 AM with SQLCODE -904 (resource unavailable — tablespace in STOP status). Investigation revealed that an ad hoc DBA operation had stopped and restarted a tablespace for a REORG, not realizing that the batch was now using that tablespace from multiple concurrent threads.

The partition control table showed: - P1: Completed at 1:31 AM, 524,800 records - P2: Completed at 1:33 AM, 526,100 records - P3: Failed at 1:47 AM, 387,422 of 525,300 records, checkpoint at 386,000 - P4: Completed at 1:35 AM, 523,800 records

Rob restarted the stopped tablespace. Kwame set partition 3's status back to 'P' and resubmitted only the EODPOST-P03 job. It restarted from checkpoint record 386,000, processing the remaining 139,300 records in 12 minutes. The merge job ran with all four complete outputs. The pipeline finished at 2:38 AM — well within the window.

Without partition-level restart, the entire posting step would have been rerun from scratch — 45 minutes for all four partitions. With partition restart, only 12 minutes were needed. The partition control table made the difference.

Lessons Learned

1. Distribution analysis is not optional. The naive four-way split by account range would have produced a 3.2:1 imbalance ratio. Lisa's percentile-based split produced 1.08:1. The difference is between a 45-minute parallel step and a 90-minute "parallel" step gated by the overloaded partition.

2. Monthly boundary recomputation prevents drift. Account distribution shifts as new accounts are opened and old accounts are closed. After six months without recomputation, the imbalance ratio at CNB crept from 1.08:1 to 1.4:1. Monthly recomputation keeps it below 1.15:1.

3. Minimal code changes, maximum impact. The COBOL program gained 280 lines — less than 9% growth — for a 70% elapsed time reduction. The partition framework was a layer around existing tested logic, not a rewrite.

4. DB2 parallelism compounds application parallelism. Application partitioning gave a 3.3x speedup (180 minutes → 55 minutes). Adding DB2 I/O parallelism gave an additional 1.15x (55 minutes → 48 minutes). Combined: 3.75x. These are multiplicative benefits.

5. Partition-level restart is the operational safety net. The partition control table and checkpoint design saved 33 minutes on the first production failure. Over a year of production operation, partition-level restart was used 11 times — always recovering within 15 minutes instead of requiring a full 45-minute rerun.

6. Communication with operations is critical. The REORG incident happened because the DBA did not know the batch schedule had changed. Kwame added the parallel batch schedule to the operations calendar and established a rule: no tablespace maintenance during the batch window without explicit approval from the batch controller.

Discussion Questions

CNB chose key-range partitioning over hash partitioning. Under what circumstances would hash partitioning be a better choice for the EOD posting job? What would the trade-offs be?
The fraud scoring step runs in parallel with interest calculation, using only 2 partitions. Why not 4 partitions like the posting step? What factors determine the optimal partition count for each job independently?
The statement generation step (EODSTMT) runs serially at 35 minutes. It was not parallelized because 35 minutes was "fast enough." At what point would you recommend parallelizing it? What would trigger that decision?
Lisa recomputes partition boundaries monthly. Rob argues for weekly. Kwame says quarterly is sufficient. What data would you examine to determine the right recomputation frequency?
The partition control table is in DB2. What happens if DB2 itself is unavailable when the batch needs to start? How would you design a fallback mechanism?