Case Study 34.1: Stored Procedure Architecture for Core Banking

Background

Meridian National Bank is migrating from a monolithic COBOL batch system to a real-time transaction processing model. The existing system processes account transfers, loan payments, and fee assessments in nightly batch windows. The bank needs these operations to execute in real time while maintaining the same data integrity guarantees.

The architecture team must decide: should the new real-time logic live in DB2 stored procedures, in a Java application tier, or in a combination of both?

The Challenge

The existing batch system processes approximately 2.3 million transactions per night across 4 hours. The new real-time system must handle peak loads of 500 transactions per second during business hours, with sub-200ms response times.

Key constraints: - Regulatory requirement: All fund transfers must be atomic — no partial transfers are acceptable under any circumstances. - Audit requirement: Every transaction must be logged with the originating user, timestamp, and full transaction details before the response is returned. - Multi-channel: The same transfer operation must be available from the teller application (Java), the mobile banking app (REST API), and the automated clearinghouse (COBOL batch). - Existing investment: 15 years of COBOL business rules for fee calculation and interest accrual that must remain consistent during the migration.

Analysis

Option A: All Logic in the Application Tier

Place all business logic in a Java service layer. Each channel (teller, mobile, ACH) calls the Java service, which executes SQL statements against DB2.

Pros: Modern tooling, easy debugging, horizontal scalability. Cons: Three channels mean three potential implementations of the same rules. The Java service becomes a single point of failure. Network round-trips between Java and DB2 multiply for complex operations.

Option B: All Logic in DB2 Stored Procedures

Move all business logic into SQL PL stored procedures. Each channel calls the stored procedure directly.

Pros: Single implementation of business rules. Atomic execution guaranteed. No network round-trips between logic and data. Three channels share the same procedure. Cons: SQL PL debugging is harder. DB2 becomes the scaling bottleneck. Complex logic (like external credit checks) is awkward in SQL PL.

Option C: Hybrid Architecture (Chosen)

Core data operations (transfers, loan payments, balance updates) live in DB2 stored procedures. Application-tier logic handles channel-specific concerns (authentication, request formatting, external service calls).

[Teller App] → [Java Service] → PROC_TRANSFER_FUNDS → DB2
[Mobile App] → [REST API]     → PROC_TRANSFER_FUNDS → DB2
[ACH Batch]  → [COBOL Job]    → PROC_TRANSFER_FUNDS → DB2

Implementation

Phase 1: Core Procedures

The team implemented PROC_TRANSFER_FUNDS following the pattern in Section 34.12, with these production enhancements:

1. Deadlock prevention: Updates always proceed in ascending account ID order.
2. Optimistic concurrency: The procedure checks the account's LAST_MODIFIED timestamp against the value the caller read, detecting concurrent modifications.
3. Daily limit enforcement: Aggregate checks against the daily transfer limit prevent abuse.
4. Audit logging: Every transfer, whether successful or rejected, is logged.

Phase 2: Error Handling Standardization

All procedures return a consistent interface: - p_status_code (INTEGER): 0 for success, negative for business errors, positive SQLCODE for system errors. - p_status_msg (VARCHAR(500)): Human-readable message suitable for end-user display.

The error handling pattern from Section 34.3.5 was adopted as a standard, with an EXIT handler in every production procedure.

Phase 3: Testing Framework

The team created a test suite of 47 stored procedure tests covering: - Normal operation paths - All validation failure scenarios - Concurrent access (tested with 50 simultaneous connections) - Boundary conditions (exact daily limit, zero balance, maximum transfer amount)

Results

Lessons Learned

1. Start with the interface. Define the procedure's parameters, return codes, and error messages before writing the logic. Every consuming application depends on this contract.
2. Deadlock prevention is not optional. The ascending-ID lock ordering pattern eliminated 100% of deadlock incidents in production.
3. Test procedures like application code. The testing framework caught 12 bugs before production deployment.
4. Version management discipline. Every procedure change goes through Git, with a deployment script reviewed by two team members.

Discussion Questions

1. Under what circumstances would you move the transfer logic out of the stored procedure and into the application tier?
2. How would you modify PROC_TRANSFER_FUNDS to support multi-currency transfers?
3. If the bank acquires another bank and needs to merge account systems, how does the stored procedure architecture simplify or complicate the merger?