Case Study 1: CNB's Account Validation Stored Procedure

From Seven Implementations to One

Background

City National Bank had a problem that Kwame Asante described, with characteristic understatement, as "a rich diversity of interpretations of what constitutes a valid account." Seven different programs across three platforms validated accounts before processing transactions. Each had been written at a different time, by a different developer, with a different understanding of the business rules.

The CICS online teller transaction (written in 2008) checked account status, balance, and hold flags. The batch posting program (written in 2012) checked account status and balance but not hold flags — because in 2012, the hold flag feature didn't exist yet, and nobody went back to add it. The web service gateway (written in 2019) checked everything but also performed a dormancy check that no other program did. The ATM interface (2015) had its own version. The IVR system (2017) was a copy-paste from the ATM version with modifications. The mobile API (2020) was based on the web service version. And the internal audit batch (2022) had the most comprehensive validation — because the auditors kept finding gaps in the other six.

Lisa Park discovered the divergence during a regulatory exam prep. A regulator's test account was flagged as valid by the online teller but invalid by the batch posting program. The difference: the teller program accepted accounts in "PENDING_CLOSE" status for balance inquiries, while the batch program rejected them. Both behaviors were defensible. Neither was documented as the canonical rule.

"We spent three weeks reconciling the seven implementations," Lisa said. "We found 23 behavioral differences. Eleven of them had produced incorrect transaction processing in production. We estimated $340,000 in mis-posted transactions over two years."

The Decision

Kwame applied the scoring matrix:

Total: 20. Clear stored procedure candidate.

Design Phase

Rob Nguyen led the technical design. The team made several deliberate architectural decisions:

Decision 1: Two procedures, not one. Rob split the validation into two procedures:

• CNB.VALIDATE_ACCOUNT_QUICK — A lightweight check for balance inquiries and status lookups. Reads ACCOUNT_MASTER only. Returns status and balance. For the IVR and ATM paths where speed matters most.

• CNB.VALIDATE_ACCOUNT_FULL — The comprehensive validation for transactional operations. Reads all four tables. Checks dormancy, holds, restrictions, and customer status. Returns a detailed validation result structure.

"The temptation was to build one procedure that does everything," Rob explained. "But the ATM path doesn't need hold checks for a balance inquiry. Making the ATM call the full procedure adds 3ms of latency for zero business value."

Decision 2: Status code contract. The team defined a status code contract that all callers must follow:

Status Code 0: Account valid, proceed with transaction
Status Code 2: Account valid with warnings (dormancy, low balance)
Status Code 4: Account not found
Status Code 8: Account found but invalid for this operation
Status Code 12: System error (DB2 failure)
Status Code 16: Parameter error (null or malformed input)

Every caller checks the status code. The status message provides human-readable detail. This contract is documented in a copybook (ACTVLRSP) that all calling programs include.

Decision 3: No commit in the procedure. Both procedures are read-only validation. They execute SELECT statements only. COMMIT ON RETURN is NO. The calling program owns the unit of work.

Decision 4: Separate WLM environment for high-volume procedures. Rob created a dedicated WLM application environment (WLMACTVL) for the validation procedures, separate from the general-purpose stored procedure environment. This prevents a misbehaving procedure in the general environment from affecting account validation latency.

Implementation

The full validation procedure's CREATE PROCEDURE DDL:

CREATE PROCEDURE CNB.VALIDATE_ACCOUNT_FULL
    (IN  P_ACCOUNT_NUM      CHAR(12),
     IN  P_OPERATION_TYPE   CHAR(4),
     OUT P_ACCOUNT_STATUS   CHAR(2),
     OUT P_CURRENT_BALANCE  DECIMAL(15,2),
     OUT P_AVAILABLE_BALANCE DECIMAL(15,2),
     OUT P_CUSTOMER_ID      CHAR(10),
     OUT P_RESTRICTION_CODE CHAR(4),
     OUT P_HOLD_AMOUNT      DECIMAL(15,2),
     OUT P_DORMANCY_DAYS    INTEGER,
     OUT P_STATUS_CODE      INTEGER,
     OUT P_STATUS_MSG       VARCHAR(200))
    LANGUAGE COBOL
    EXTERNAL NAME ACTVLFSP
    PARAMETER STYLE GENERAL
    NOT DETERMINISTIC
    READS SQL DATA
    WLM ENVIRONMENT WLMACTVL
    PROGRAM TYPE MAIN
    COLLID CNBCOLL
    COMMIT ON RETURN NO
    ASUTIME LIMIT 2000;

Key design elements in the COBOL implementation:

Available balance calculation. The procedure calculates available balance as current balance minus hold amounts minus pending debits. This calculation previously existed in four different programs with three different interpretations of "pending debits."

       3000-CALC-AVAILABLE-BALANCE.
           MOVE LS-CURRENT-BALANCE TO WS-AVAILABLE

           EXEC SQL
               SELECT COALESCE(SUM(HOLD_AMOUNT), 0)
               INTO :WS-TOTAL-HOLDS
               FROM ACCOUNT_HOLDS
               WHERE ACCOUNT_NUMBER = :LS-ACCOUNT-NUM
                 AND HOLD_STATUS = 'ACTIVE'
                 AND HOLD_EXPIRY_DATE > CURRENT DATE
           END-EXEC

           IF SQLCODE = 0 OR SQLCODE = +100
               SUBTRACT WS-TOTAL-HOLDS FROM WS-AVAILABLE
           END-IF

           EXEC SQL
               SELECT COALESCE(SUM(TRANSACTION_AMOUNT), 0)
               INTO :WS-PENDING-DEBITS
               FROM PENDING_TRANSACTIONS
               WHERE ACCOUNT_NUMBER = :LS-ACCOUNT-NUM
                 AND TRANSACTION_TYPE = 'DEBIT'
                 AND PROCESSING_STATUS = 'PENDING'
           END-EXEC

           IF SQLCODE = 0 OR SQLCODE = +100
               SUBTRACT WS-PENDING-DEBITS FROM WS-AVAILABLE
           END-IF

           MOVE WS-AVAILABLE TO LS-AVAILABLE-BALANCE
           .

Operation-type-aware validation. The P_OPERATION_TYPE parameter controls which checks are performed:

       4000-VALIDATE-FOR-OPERATION.
           EVALUATE LS-OPERATION-TYPE
               WHEN 'BALC'
                   PERFORM 4100-VALIDATE-BALANCE-INQUIRY
               WHEN 'WDRL'
                   PERFORM 4200-VALIDATE-WITHDRAWAL
               WHEN 'DPST'
                   PERFORM 4300-VALIDATE-DEPOSIT
               WHEN 'XFER'
                   PERFORM 4400-VALIDATE-TRANSFER
               WHEN OTHER
                   MOVE 16 TO LS-STATUS-CODE
                   STRING 'UNKNOWN OPERATION TYPE: '
                          LS-OPERATION-TYPE
                          DELIMITED BY SIZE
                          INTO LS-STATUS-MSG
           END-EVALUATE
           .

A withdrawal requires a positive available balance. A deposit only requires that the account is open. A transfer requires both positive available balance and no outbound restriction flags. By embedding these rules in the procedure, the seven calling programs no longer need to implement operation-specific logic.

Restriction checking. The procedure reads from the ACCOUNT_RESTRICTIONS table and applies rules based on the restriction code and operation type. This replaced the most divergent logic — the batch program ignored restrictions entirely, the web service treated "REVIEW" restrictions as hard blocks, and the CICS transaction prompted the teller for override authorization. The stored procedure now returns the restriction information and lets the caller decide how to handle the user interaction (prompt for override, auto-reject, etc.), while the business rule of what constitutes a restriction is centralized.

Migration

The migration took four months, not because the stored procedure was hard to build, but because seven teams had to modify seven programs:

Month 1: Stored procedure developed and deployed to QA. Test harness created with 156 test cases covering every known behavioral scenario.

Month 2: CICS online teller and web service gateway migrated. These were the highest-risk callers. Lisa personally reviewed every test result.

Month 3: Batch posting, ATM interface, and IVR migrated. The batch posting team discovered three additional business rules they had implemented locally that weren't in the stored procedure — all three were added after review.

Month 4: Mobile API and audit batch migrated. Internal audit actually expanded their test suite to validate that the stored procedure matched their (correct) implementation.

During the migration, both the old application-side logic and the new stored procedure ran in parallel for the CICS transaction. A comparison routine logged every case where the results differed. In the first week, there were 847 differences out of 1.2 million transactions. Lisa's team reviewed each one. Every difference was a case where the application-side logic was wrong and the stored procedure was correct.

Performance Results

Rob's benchmarks after the migration:

The total daily CPU actually decreased even though per-invocation CPU increased. Why? Because the stored procedure eliminated redundant SQL calls. The application-side logic in the batch program was making 4 separate SQL calls per account; the stored procedure consolidated them into optimized queries with a single parse and bind.

Lessons Learned

1. The copybook contract matters more than the procedure. The ACTVLRSP copybook that defines the response structure became the most important artifact. Every team includes it. When the response structure changes, the copybook change forces a recompile of every caller — which is exactly what you want.

2. Separate WLM environments for different SLAs. The quick-validation procedure (ATM, IVR) runs in a WLM environment with a 2ms velocity goal. The full-validation procedure runs in an environment with a 10ms velocity goal. Mixing them in one environment would force a single goal that compromises one path or the other.

3. Parallel running is non-negotiable. Without the parallel-run comparison during migration, the team would not have caught the 847 differences. Several were edge cases that no test suite would have covered — accounts in unusual states created by production incidents years earlier.

4. Don't centralize the UI decision. The stored procedure returns restriction codes and dormancy information. It does not return "show message X to the user." That's a presentation decision that varies by channel. The procedure provides data; the caller decides what to do with it.

5. Monitor from day one. Rob set up SMF 101 record monitoring for the stored procedure on the first day of production deployment. Within a week, he identified that the ATM interface was calling VALIDATE_ACCOUNT_FULL when it should have been calling VALIDATE_ACCOUNT_QUICK, adding 3ms of unnecessary latency to every ATM transaction. The ATM team fixed it in the next sprint.

Current State

Two years after deployment, the validation stored procedures are the most-called procedures in CNB's DB2 subsystem: 2.3 million calls per day across all seven callers. The procedure has been updated four times for regulatory changes. Each update was deployed once and immediately effective for all callers. The estimated savings from eliminating logic divergence: $500,000 per year in reduced mis-postings and audit findings.

Kwame Asante's assessment: "This is what stored procedures are for. Not because they're faster — although they are. Because we finally have one answer to the question 'is this account valid?'"

Discussion Questions

1. Rob split the validation into two procedures (quick and full) rather than using a flag parameter in a single procedure. What are the trade-offs of each approach? Under what circumstances would a single procedure with a mode parameter be preferable?

2. During parallel running, 847 out of 1.2 million transactions showed differences. All were cases where the old logic was wrong. What does this tell you about the quality of the existing test suites for those programs?

3. CNB chose PARAMETER STYLE GENERAL (no null handling) for the validation procedure. Under what circumstances would PARAMETER STYLE GENERAL WITH NULLS be necessary? What would change in the COBOL code?

4. The case study mentions that the batch posting team discovered three additional business rules during migration that weren't in the stored procedure. How should a team systematically identify all business rules embedded in existing programs before building the stored procedure?

5. CNB's validation procedures are read-only (READS SQL DATA). How would the design change if the procedure also needed to write an audit record for every validation? Consider the implications for COMMIT ON RETURN and the caller's unit of work.