Case Study: Building a Real-Time Account Inquiry System with DB2
Background
Continental Trust Bank, a commercial bank headquartered in Chicago with $18 billion in assets, operated a CICS-based online banking system that had been in production since 1996. The system allowed tellers and customer service representatives to view account information, process transactions, and manage customer relationships. By 2023, the system was handling an average of 24,000 online inquiries per hour during peak periods, with response time requirements of under two seconds per transaction.
The bank's existing account inquiry program, ACCTINQ1, had been written when the customer base was a fraction of its current size. As data volumes grew, the program's response times had degraded. During month-end processing, when batch programs competed for DB2 resources, online inquiry response times would spike to eight or even twelve seconds, generating complaints from branch staff and customer service representatives.
The bank's VP of Technology, Linda Vasquez, authorized a project to redesign the account inquiry system from the ground up, with a target response time of under one second for 95% of inquiries, even during peak batch processing. The project was assigned to a team of three developers led by James Hartwell, a fifteen-year veteran of mainframe development.
System Architecture
The new account inquiry system, designated ACCTINQ2, operated within the CICS Transaction Server environment on the bank's IBM z15 mainframe. The architecture involved several interconnected components.
The front end was a BMS (Basic Mapping Support) map set that defined the screen layouts for the inquiry terminal. The main program, ACCTINQ2, was a COBOL program that received input from the terminal, queried DB2, and formatted the response. A set of DB2 stored procedures handled complex queries that required multiple table joins, keeping the network traffic between CICS and DB2 to a minimum.
The DB2 database supporting the inquiry system consisted of the following primary tables: CUSTOMER (2.1 million rows), ACCOUNT (5.8 million rows), ACCOUNT_BALANCE (5.8 million rows, updated in real-time), TRANSACTION_HISTORY (340 million rows, rolling 24 months), and ACCOUNT_HOLD (180,000 rows for current holds and restrictions).
James's first design decision was to separate the ACCOUNT_BALANCE table from the ACCOUNT table. In the original system, balance information was stored as columns within the ACCOUNT table. This meant that every balance update, which happened thousands of times per hour, required locking a row that was also frequently read by inquiry transactions. By isolating balances into a separate table, the team could reduce lock contention between update transactions and read-only inquiries.
The Core Inquiry Program
The ACCTINQ2 program followed a structured design pattern optimized for CICS conversational transactions. The program used the pseudo-conversational model, where the program terminated after each screen display and was reinvoked when the user pressed a key, using a communication area (COMMAREA) to maintain state between interactions.
IDENTIFICATION DIVISION.
PROGRAM-ID. ACCTINQ2.
*================================================================*
* CONTINENTAL TRUST BANK - ACCOUNT INQUIRY SYSTEM V2 *
* CICS Online Transaction Program *
* Transaction ID: AINQ *
*================================================================*
DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL INCLUDE SQLCA END-EXEC.
COPY DFHAID.
COPY ACCTINQS.
COPY CPYERRWS.
01 WS-COMMAREA.
05 WS-CA-STATE PIC X(02).
88 CA-INITIAL-ENTRY VALUE SPACES.
88 CA-SEARCH-ENTERED VALUE 'SE'.
88 CA-DETAIL-DISPLAYED VALUE 'DD'.
88 CA-HISTORY-DISPLAYED VALUE 'HD'.
05 WS-CA-ACCOUNT-NUMBER PIC X(12).
05 WS-CA-CUSTOMER-ID PIC X(10).
05 WS-CA-HISTORY-OFFSET PIC S9(08) COMP.
05 WS-CA-LAST-SQLCODE PIC S9(09) COMP.
01 WS-RESPONSE-TIMER.
05 WS-START-TIME PIC S9(15) COMP-3.
05 WS-END-TIME PIC S9(15) COMP-3.
05 WS-ELAPSED-MS PIC S9(09) COMP.
* Host variables for DB2 queries
01 WS-CUSTOMER-DATA.
05 HV-CUST-ID PIC X(10).
05 HV-CUST-NAME-LAST PIC X(30).
05 HV-CUST-NAME-FIRST PIC X(25).
05 HV-CUST-SSN-LAST4 PIC X(04).
05 HV-CUST-STATUS PIC X(01).
01 WS-ACCOUNT-DATA.
05 HV-ACCT-NUMBER PIC X(12).
05 HV-ACCT-TYPE PIC X(03).
05 HV-ACCT-STATUS PIC X(01).
05 HV-ACCT-OPEN-DATE PIC X(10).
05 HV-ACCT-CURR-BAL PIC S9(13)V99 COMP-3.
05 HV-ACCT-AVAIL-BAL PIC S9(13)V99 COMP-3.
05 HV-ACCT-HOLD-AMT PIC S9(13)V99 COMP-3.
PROCEDURE DIVISION.
*================================================================*
0000-MAIN-CONTROL.
*================================================================*
EXEC CICS ASKTIME
ABSTIME(WS-START-TIME)
END-EXEC
EVALUATE TRUE
WHEN CA-INITIAL-ENTRY
PERFORM 1000-SEND-INITIAL-MAP
WHEN CA-SEARCH-ENTERED
PERFORM 2000-PROCESS-SEARCH
WHEN CA-DETAIL-DISPLAYED
PERFORM 3000-PROCESS-DETAIL-ACTION
WHEN CA-HISTORY-DISPLAYED
PERFORM 4000-PROCESS-HISTORY-ACTION
WHEN OTHER
PERFORM 1000-SEND-INITIAL-MAP
END-EVALUATE
EXEC CICS RETURN
TRANSID('AINQ')
COMMAREA(WS-COMMAREA)
LENGTH(LENGTH OF WS-COMMAREA)
END-EXEC
.
Optimizing for Response Time
The most technically demanding aspect of the project was meeting the sub-second response time target. James identified three primary strategies: query optimization, lock avoidance, and intelligent data access.
Query Optimization
The original ACCTINQ1 program executed five separate SQL statements to gather the information displayed on a single account detail screen: one for customer data, one for account details, one for the current balance, one for recent transactions, and one for active holds. Each SQL statement required a round trip between the CICS region and the DB2 subsystem.
James consolidated these into two optimized queries. The first retrieved customer and account information using a join:
*================================================================*
2100-RETRIEVE-ACCOUNT-DETAIL.
*================================================================*
* Single query retrieves customer + account + balance data
* using joins instead of separate queries.
*----------------------------------------------------------------*
EXEC SQL
SELECT C.CUSTOMER_ID,
C.NAME_LAST,
C.NAME_FIRST,
SUBSTR(C.SSN, 6, 4),
C.STATUS_CODE,
A.ACCOUNT_NUMBER,
A.ACCOUNT_TYPE,
A.STATUS_CODE,
CHAR(A.OPEN_DATE, ISO),
B.CURRENT_BALANCE,
B.AVAILABLE_BALANCE,
COALESCE(H.TOTAL_HOLD_AMOUNT, 0)
INTO :HV-CUST-ID,
:HV-CUST-NAME-LAST,
:HV-CUST-NAME-FIRST,
:HV-CUST-SSN-LAST4,
:HV-CUST-STATUS,
:HV-ACCT-NUMBER,
:HV-ACCT-TYPE,
:HV-ACCT-STATUS,
:HV-ACCT-OPEN-DATE,
:HV-ACCT-CURR-BAL,
:HV-ACCT-AVAIL-BAL,
:HV-ACCT-HOLD-AMT
FROM ACCOUNT A
INNER JOIN CUSTOMER C
ON A.CUSTOMER_ID = C.CUSTOMER_ID
INNER JOIN ACCOUNT_BALANCE B
ON A.ACCOUNT_NUMBER = B.ACCOUNT_NUMBER
LEFT OUTER JOIN
(SELECT ACCOUNT_NUMBER,
SUM(HOLD_AMOUNT) AS TOTAL_HOLD_AMOUNT
FROM ACCOUNT_HOLD
WHERE RELEASE_DATE IS NULL
OR RELEASE_DATE > CURRENT DATE
GROUP BY ACCOUNT_NUMBER) H
ON A.ACCOUNT_NUMBER = H.ACCOUNT_NUMBER
WHERE A.ACCOUNT_NUMBER = :WS-CA-ACCOUNT-NUMBER
END-EXEC
PERFORM 8100-CHECK-SQLCODE
.
The second query retrieved recent transaction history using a cursor with a FETCH FIRST clause to limit the result set:
*================================================================*
* Cursor for recent transaction history with pagination
*================================================================*
EXEC SQL DECLARE TXN-HISTORY-CURSOR CURSOR FOR
SELECT CHAR(TXN_DATE, ISO),
TXN_TIME,
TXN_TYPE_CODE,
TXN_DESCRIPTION,
TXN_AMOUNT,
RUNNING_BALANCE
FROM TRANSACTION_HISTORY
WHERE ACCOUNT_NUMBER = :WS-CA-ACCOUNT-NUMBER
ORDER BY TXN_DATE DESC,
TXN_TIME DESC,
TXN_SEQUENCE DESC
FETCH FIRST 15 ROWS ONLY
END-EXEC
*================================================================*
2200-RETRIEVE-RECENT-TRANSACTIONS.
*================================================================*
EXEC SQL OPEN TXN-HISTORY-CURSOR END-EXEC
PERFORM 8100-CHECK-SQLCODE
MOVE 0 TO CT-TXN-ROWS-FETCHED
PERFORM 2210-FETCH-TXN-ROW
UNTIL END-OF-CURSOR
OR CT-TXN-ROWS-FETCHED >= 15
EXEC SQL CLOSE TXN-HISTORY-CURSOR END-EXEC
.
2210-FETCH-TXN-ROW.
EXEC SQL
FETCH TXN-HISTORY-CURSOR
INTO :HV-TXN-DATE,
:HV-TXN-TIME,
:HV-TXN-TYPE,
:HV-TXN-DESC,
:HV-TXN-AMOUNT,
:HV-TXN-BALANCE
END-EXEC
EVALUATE SQLCODE
WHEN 0
ADD 1 TO CT-TXN-ROWS-FETCHED
PERFORM 2220-FORMAT-TXN-LINE
WHEN +100
SET END-OF-CURSOR TO TRUE
WHEN OTHER
PERFORM 8100-CHECK-SQLCODE
END-EVALUATE
.
Lock Avoidance with Uncommitted Read
For inquiry-only transactions, James implemented the ISOLATION(UR) option, which allowed the program to read data without acquiring locks. This was critical for eliminating contention between the inquiry system and batch update processes:
* Bind option for inquiry-only plan
* BIND PLAN(ACCTINQ2) ISOLATION(UR) -
* CURRENTDATA(NO) -
* ACQUIRE(USE) RELEASE(COMMIT)
The use of uncommitted read meant that the inquiry program might occasionally display a balance that was in the process of being updated. For an inquiry screen that was purely informational, this was an acceptable trade-off. The team documented this design decision carefully and ensured that any screen displaying balance information included a "Balance as of" timestamp to communicate that the data was a point-in-time snapshot rather than a guaranteed current value.
For the small number of inquiry paths that required accurate current data, such as when a teller needed to verify a balance before approving a large withdrawal, the program used a different access path with ISOLATION(CS) (cursor stability) that acquired appropriate locks:
*================================================================*
2500-RETRIEVE-VERIFIED-BALANCE.
*================================================================*
* Used for balance verification before high-value transactions.
* Uses Cursor Stability isolation for accuracy.
*----------------------------------------------------------------*
EXEC SQL
SELECT B.CURRENT_BALANCE,
B.AVAILABLE_BALANCE,
CURRENT TIMESTAMP
INTO :HV-ACCT-CURR-BAL,
:HV-ACCT-AVAIL-BAL,
:HV-VERIFIED-TIMESTAMP
FROM ACCOUNT_BALANCE B
WHERE B.ACCOUNT_NUMBER = :WS-CA-ACCOUNT-NUMBER
WITH CS
END-EXEC
PERFORM 8100-CHECK-SQLCODE
.
Intelligent Data Access with Indexing
The DBA, working with James, designed the index strategy to support the specific access patterns of the inquiry system. The critical indexes included:
- A unique index on ACCOUNT.ACCOUNT_NUMBER (primary access path)
- A non-unique index on ACCOUNT.CUSTOMER_ID (for customer-to-account navigation)
- A composite index on TRANSACTION_HISTORY (ACCOUNT_NUMBER, TXN_DATE DESC, TXN_SEQUENCE DESC) to support the recent transactions query
- A filtered index on ACCOUNT_HOLD for active holds only
The composite index on TRANSACTION_HISTORY was particularly important. Without it, the recent transactions query required a sort operation on hundreds of rows to identify the most recent fifteen. With the index, DB2 could satisfy the query through an index-only access path, reading exactly fifteen index entries without touching the base table at all.
Error Handling: Comprehensive SQLCODE Management
A critical aspect of the inquiry system was robust error handling for every possible DB2 condition. In a CICS environment, an unhandled SQL error could abend the transaction and display a cryptic error message to the user. James implemented a centralized error handling paragraph that translated SQLCODE values into user-friendly messages:
*================================================================*
8100-CHECK-SQLCODE.
*================================================================*
EVALUATE SQLCODE
WHEN 0
CONTINUE
WHEN +100
SET RECORD-NOT-FOUND TO TRUE
WHEN -811
MOVE 'MULTIPLE RECORDS FOUND - DATA ERROR'
TO WS-USER-MESSAGE
PERFORM 8500-LOG-DB2-ERROR
WHEN -904
MOVE 'SYSTEM IS TEMPORARILY BUSY - '
& 'PLEASE RETRY IN A MOMENT'
TO WS-USER-MESSAGE
PERFORM 8500-LOG-DB2-ERROR
WHEN -911
MOVE 'ACCOUNT IS BEING UPDATED - '
& 'PLEASE RETRY IN A MOMENT'
TO WS-USER-MESSAGE
PERFORM 8500-LOG-DB2-ERROR
WHEN -913
MOVE 'SYSTEM IS EXPERIENCING HIGH DEMAND - '
& 'PLEASE RETRY'
TO WS-USER-MESSAGE
PERFORM 8500-LOG-DB2-ERROR
WHEN OTHER
STRING 'A SYSTEM ERROR OCCURRED (REF: '
WS-FORMATTED-SQLCODE '). '
'PLEASE CONTACT SUPPORT.'
DELIMITED BY SIZE
INTO WS-USER-MESSAGE
END-STRING
PERFORM 8500-LOG-DB2-ERROR
END-EVALUATE
.
*================================================================*
8500-LOG-DB2-ERROR.
*================================================================*
* Write error details to the CICS transient data queue
* for later review by support staff.
*----------------------------------------------------------------*
MOVE SQLCODE TO WS-LOG-SQLCODE
MOVE SQLERRMC TO WS-LOG-SQLERRMC
MOVE SQLERRD(3) TO WS-LOG-ROWS-AFFECTED
MOVE SQLERRD(5) TO WS-LOG-DB2-REASON
EXEC CICS ASKTIME
ABSTIME(WS-LOG-TIMESTAMP)
END-EXEC
EXEC CICS FORMATTIME
ABSTIME(WS-LOG-TIMESTAMP)
YYYYMMDD(WS-LOG-DATE)
TIME(WS-LOG-TIME)
END-EXEC
STRING 'ACCTINQ2 DB2 ERROR: '
'DATE=' WS-LOG-DATE ' '
'TIME=' WS-LOG-TIME ' '
'SQLCODE=' WS-LOG-SQLCODE ' '
'ACCT=' WS-CA-ACCOUNT-NUMBER ' '
'SQLERRMC=' WS-LOG-SQLERRMC
DELIMITED BY SIZE
INTO WS-LOG-RECORD
END-STRING
EXEC CICS WRITEQ TD
QUEUE('AINQ')
FROM(WS-LOG-RECORD)
LENGTH(LENGTH OF WS-LOG-RECORD)
END-EXEC
.
The distinction between SQLCODE -904 (resource unavailable), -911 (timeout/deadlock), and -913 (deadlock or timeout on tablespace/partition) was important in the user experience. Each condition received a specific, non-technical message that told the user what to do rather than exposing the internal error code.
Handling Concurrent Access
The inquiry system had to coexist with several batch processes that updated the same tables. The nightly interest posting batch, the real-time transaction posting system, and the monthly statement generation all competed for access to the ACCOUNT_BALANCE and TRANSACTION_HISTORY tables.
James worked with the DBA to implement several concurrency strategies.
For the ACCOUNT_BALANCE table, the team used a row-level locking strategy where update transactions locked only the specific account row being modified. The inquiry system, using uncommitted read, was never blocked by these locks.
For the TRANSACTION_HISTORY table, which grew by approximately 500,000 rows per day, the team implemented a partitioning strategy that separated active data (current month) from historical data (prior months). Batch processes that scanned historical data operated on different partitions than the online system's recent-transactions query, eliminating physical I/O contention.
The team also implemented a retry mechanism for the rare cases where the verified-balance query encountered a lock timeout:
*================================================================*
2550-RETRIEVE-BALANCE-WITH-RETRY.
*================================================================*
MOVE 0 TO CT-RETRY-ATTEMPTS
SET BALANCE-NOT-RETRIEVED TO TRUE
PERFORM UNTIL BALANCE-RETRIEVED
OR CT-RETRY-ATTEMPTS >= 3
PERFORM 2500-RETRIEVE-VERIFIED-BALANCE
EVALUATE TRUE
WHEN SQLCODE = 0
SET BALANCE-RETRIEVED TO TRUE
WHEN SQLCODE = -911 OR SQLCODE = -913
ADD 1 TO CT-RETRY-ATTEMPTS
IF CT-RETRY-ATTEMPTS < 3
EXEC SQL ROLLBACK END-EXEC
EXEC CICS DELAY
MILLISECS(200)
END-EXEC
END-IF
WHEN OTHER
SET BALANCE-ERROR TO TRUE
SET BALANCE-RETRIEVED TO TRUE
END-EVALUATE
END-PERFORM
IF CT-RETRY-ATTEMPTS >= 3
MOVE 'UNABLE TO VERIFY BALANCE - '
& 'ACCOUNT MAY BE IN USE. PLEASE RETRY.'
TO WS-USER-MESSAGE
END-IF
.
The retry mechanism used a 200-millisecond delay between attempts, which was short enough to remain within the two-second response time window while allowing a competing transaction to complete its update.
Performance Results
After deploying ACCTINQ2 to production, the team measured performance over a three-month period covering both normal operations and month-end peaks.
Average response time for standard account inquiries dropped from 1.4 seconds (ACCTINQ1 during normal operations) to 0.3 seconds (ACCTINQ2). During month-end batch processing, the original system's response times had spiked to 8-12 seconds; the new system maintained an average of 0.6 seconds, well within the one-second target.
The 95th percentile response time, the critical SLA metric, was 0.7 seconds under normal load and 0.9 seconds during month-end peak, both within the one-second target.
DB2 resource consumption was significantly lower than the original system. The consolidation from five SQL statements to two, combined with the optimized index strategy, reduced the average number of getpages (DB2 buffer pool reads) per inquiry from 847 to 124. CPU consumption per transaction dropped by approximately 40%.
Lock contention metrics showed the most dramatic improvement. Under the old system, the DB2 performance monitor recorded an average of 340 lock suspension events per hour during peak periods. With the new system's uncommitted read strategy and row-level locking, lock suspension events dropped to fewer than 12 per hour.
Lessons Learned
The ACCTINQ2 project yielded several important insights for the Continental Trust development team.
First, understanding DB2's locking and isolation levels was essential for building a responsive online system. The decision to use uncommitted read for informational queries and cursor stability only for balance verification queries was the single most impactful design choice in the project. Without this distinction, the new system would have performed only marginally better than the old one.
Second, consolidating multiple SQL statements into fewer, more complex queries was not merely an optimization but a fundamental design principle for CICS/DB2 programs. Each SQL statement incurred overhead for the CICS-DB2 attachment facility, and reducing the number of statements reduced both elapsed time and CPU consumption.
Third, the composite index on TRANSACTION_HISTORY was a reminder that index design must be driven by actual query patterns, not just by primary key requirements. The DBA's analysis of the EXPLAIN output for each query in the program identified exactly which indexes were needed and, equally important, which candidate indexes would not be used and could be omitted.
Fourth, the comprehensive SQLCODE handling was not just a reliability feature but a user experience feature. Branch tellers who received a message saying "Account is being updated - please retry in a moment" instead of "DFHAC2206 TRANSACTION AINQ ABEND AEY9" were far more likely to handle the situation correctly and maintain confidence in the system.
Fifth, the retry mechanism for lock timeouts demonstrated that graceful degradation was achievable in a CICS/DB2 environment. Rather than treating every lock timeout as an error, the program could wait briefly and retry, transparently resolving transient contention without user intervention. The 200-millisecond delay was determined empirically through testing with various batch workloads and represented the optimal balance between retry responsiveness and avoiding adding load during contention events.
Conclusion
The Continental Trust Bank account inquiry system demonstrates that COBOL programs running in a CICS/DB2 environment can achieve modern response time expectations when designed with careful attention to query optimization, isolation level management, and concurrent access patterns. The combination of embedded SQL for data access, CICS for transaction management, and thoughtful DB2 index design produced a system that met sub-second response time targets while coexisting with heavy batch workloads. For organizations building or redesigning online inquiry systems on mainframe platforms, this case study illustrates that performance is primarily a design problem, not a technology limitation.