Case Study 2: CICS Transaction Response Time Improvement
Background
Union Atlantic Bank (UAB) operates a CICS-based online banking platform that handles account inquiries, balance transfers, bill payments, and teller transactions for 1,600 branch terminals and 320,000 concurrent online banking sessions. The platform processes approximately 45,000 transactions per hour during peak periods (10:00 AM to 2:00 PM Eastern).
Over the past six months, branch tellers have reported increasingly slow response times for the account inquiry transaction (AINQ) and the balance transfer transaction (AXFR). Internal SLA monitoring confirms the problem:
| Transaction | SLA Target | Current P95 | Status |
|---|---|---|---|
| AINQ (Account Inquiry) | 1.0 sec | 3.2 sec | Breaching |
| AXFR (Balance Transfer) | 2.0 sec | 5.8 sec | Breaching |
| APAY (Bill Payment) | 2.0 sec | 2.1 sec | At Risk |
| ATXN (Transaction History) | 1.5 sec | 1.4 sec | Compliant |
The CTO has mandated that all transactions meet their SLA targets within 30 days. The performance team must diagnose the root causes and implement targeted optimizations without a full application rewrite.
Phase 1: Transaction-Level Diagnosis
The team began by analyzing CICS monitoring data from the CMF (CICS Monitoring Facility) and DB2 accounting trace records. The response time breakdown for the AINQ transaction revealed:
| Component | Time (ms) | % of Total |
|---|---|---|
| CICS dispatch wait | 180 | 5.6% |
| BMS SEND MAP | 420 | 13.1% |
| DB2 SQL execution | 1,840 | 57.5% |
| COBOL CPU processing | 310 | 9.7% |
| DB2 thread create/terminate | 280 | 8.8% |
| CICS journal write | 170 | 5.3% |
| Total | 3,200 | 100% |
The dominant bottleneck was DB2 SQL execution at 57.5% of response time. Secondary issues included BMS map processing and DB2 thread management.
For the AXFR transaction:
| Component | Time (ms) | % of Total |
|---|---|---|
| CICS dispatch wait | 220 | 3.8% |
| BMS SEND/RECEIVE MAP | 680 | 11.7% |
| DB2 SQL execution (total) | 3,180 | 54.8% |
| COBOL CPU processing | 540 | 9.3% |
| DB2 thread create/terminate | 580 | 10.0% |
| CICS syncpoint | 400 | 6.9% |
| Conversational wait | 200 | 3.4% |
| Total | 5,800 | 100% |
The AXFR transaction had the same DB2 bottleneck plus an additional problem: it was implemented as a conversational transaction, holding CICS resources while waiting for user confirmation.
Phase 2: DB2 SQL Analysis
The Account Inquiry Query
The AINQ transaction executed the following embedded SQL to retrieve account information:
*================================================================*
* ORIGINAL ACCOUNT INQUIRY SQL - PERFORMANCE PROBLEM
*================================================================*
EXEC SQL
SELECT A.ACCOUNT_NUMBER,
A.ACCOUNT_TYPE,
A.CURRENT_BALANCE,
A.AVAILABLE_BALANCE,
A.OPEN_DATE,
A.STATUS,
C.CUSTOMER_NAME,
C.CUSTOMER_ADDRESS,
C.CUSTOMER_CITY,
C.CUSTOMER_STATE,
C.CUSTOMER_ZIP,
C.CUSTOMER_PHONE,
B.BRANCH_NAME,
B.BRANCH_ADDRESS,
(SELECT COUNT(*)
FROM UAB.TRANSACTION_HISTORY T
WHERE T.ACCOUNT_NUMBER =
A.ACCOUNT_NUMBER
AND T.TRANS_DATE >=
CURRENT DATE - 30 DAYS),
(SELECT SUM(T.AMOUNT)
FROM UAB.TRANSACTION_HISTORY T
WHERE T.ACCOUNT_NUMBER =
A.ACCOUNT_NUMBER
AND T.TRANS_DATE >=
CURRENT DATE - 30 DAYS
AND T.TRANS_TYPE IN ('DP','TI','WI'))
INTO :WS-ACCT-NUMBER,
:WS-ACCT-TYPE,
:WS-CURR-BALANCE,
:WS-AVAIL-BALANCE,
:WS-OPEN-DATE,
:WS-ACCT-STATUS,
:WS-CUST-NAME,
:WS-CUST-ADDRESS,
:WS-CUST-CITY,
:WS-CUST-STATE,
:WS-CUST-ZIP,
:WS-CUST-PHONE,
:WS-BRANCH-NAME,
:WS-BRANCH-ADDRESS,
:WS-TRANS-COUNT,
:WS-DEPOSIT-TOTAL
FROM UAB.ACCOUNT_MASTER A
JOIN UAB.CUSTOMER_MASTER C
ON A.CUSTOMER_ID = C.CUSTOMER_ID
JOIN UAB.BRANCH_MASTER B
ON A.BRANCH_CODE = B.BRANCH_CODE
WHERE A.ACCOUNT_NUMBER = :WS-INPUT-ACCOUNT
END-EXEC.
EXPLAIN Analysis
The team ran EXPLAIN on this query and found the access path:
TABLE: UAB.ACCOUNT_MASTER
ACCESS: INDEX SCAN (XACCT01 - PRIMARY KEY)
MATCHING COLUMNS: 1
ROWS EXAMINED: 1
TABLE: UAB.CUSTOMER_MASTER
ACCESS: INDEX SCAN (XCUST01 - PRIMARY KEY)
MATCHING COLUMNS: 1
ROWS EXAMINED: 1
TABLE: UAB.BRANCH_MASTER
ACCESS: TABLE SPACE SCAN
REASON: NO INDEX ON BRANCH_CODE
ROWS EXAMINED: 850
SUBQUERY 1: (COUNT)
TABLE: UAB.TRANSACTION_HISTORY
ACCESS: TABLE SPACE SCAN
REASON: NO QUALIFYING INDEX
ROWS EXAMINED: 270,000,000
SUBQUERY 2: (SUM)
TABLE: UAB.TRANSACTION_HISTORY
ACCESS: TABLE SPACE SCAN
REASON: NO QUALIFYING INDEX
ROWS EXAMINED: 270,000,000
Three devastating problems emerged:
Problem 1: The BRANCH_MASTER join was performing a tablespace scan of all 850 rows because no index existed on BRANCH_CODE. While 850 rows is small, the scan still consumed measurable time on every execution.
Problem 2: Both correlated subqueries against TRANSACTION_HISTORY performed full tablespace scans of 270 million rows. The table had no index on (ACCOUNT_NUMBER, TRANS_DATE), so DB2 could not restrict the scan to the relevant account and date range.
Problem 3: The two subqueries were independent but scanned the same table twice. Each scan of the 270-million-row table took approximately 900 milliseconds, accounting for the 1,840 ms total DB2 time.
SQL and Index Optimization
The team implemented three changes:
Fix 1: Create missing indexes.
-- INDEX FOR BRANCH MASTER LOOKUP
CREATE UNIQUE INDEX UAB.XBRNCH01
ON UAB.BRANCH_MASTER (BRANCH_CODE)
USING STOGROUP UABSG01
BUFFERPOOL BP1
CLOSE NO;
-- COMPOSITE INDEX FOR TRANSACTION HISTORY
-- COVERS BOTH SUBQUERY PREDICATES
CREATE INDEX UAB.XTRHST02
ON UAB.TRANSACTION_HISTORY
(ACCOUNT_NUMBER, TRANS_DATE, TRANS_TYPE, AMOUNT)
USING STOGROUP UABSG01
BUFFERPOOL BP2
CLOSE NO;
The composite index on TRANSACTION_HISTORY is deliberately designed to be a "covering index" -- it includes all four columns referenced in the subqueries (ACCOUNT_NUMBER, TRANS_DATE, TRANS_TYPE, AMOUNT). DB2 can satisfy both subqueries entirely from the index without accessing the base table, a technique known as "index-only access."
Fix 2: Rewrite the query to eliminate redundant table scans.
*================================================================*
* OPTIMIZED ACCOUNT INQUIRY - SEPARATE QUERIES
*================================================================*
* QUERY 1: ACCOUNT, CUSTOMER, AND BRANCH DATA
EXEC SQL
SELECT A.ACCOUNT_NUMBER,
A.ACCOUNT_TYPE,
A.CURRENT_BALANCE,
A.AVAILABLE_BALANCE,
A.OPEN_DATE,
A.STATUS,
C.CUSTOMER_NAME,
C.CUSTOMER_ADDRESS,
C.CUSTOMER_CITY,
C.CUSTOMER_STATE,
C.CUSTOMER_ZIP,
C.CUSTOMER_PHONE,
B.BRANCH_NAME,
B.BRANCH_ADDRESS
INTO :WS-ACCT-NUMBER,
:WS-ACCT-TYPE,
:WS-CURR-BALANCE,
:WS-AVAIL-BALANCE,
:WS-OPEN-DATE,
:WS-ACCT-STATUS,
:WS-CUST-NAME,
:WS-CUST-ADDRESS,
:WS-CUST-CITY,
:WS-CUST-STATE,
:WS-CUST-ZIP,
:WS-CUST-PHONE,
:WS-BRANCH-NAME,
:WS-BRANCH-ADDRESS
FROM UAB.ACCOUNT_MASTER A
JOIN UAB.CUSTOMER_MASTER C
ON A.CUSTOMER_ID = C.CUSTOMER_ID
JOIN UAB.BRANCH_MASTER B
ON A.BRANCH_CODE = B.BRANCH_CODE
WHERE A.ACCOUNT_NUMBER = :WS-INPUT-ACCOUNT
END-EXEC
* QUERY 2: TRANSACTION SUMMARY (USES COVERING INDEX)
EXEC SQL
SELECT COUNT(*),
COALESCE(SUM(CASE
WHEN TRANS_TYPE IN ('DP','TI','WI')
THEN AMOUNT ELSE 0 END), 0)
INTO :WS-TRANS-COUNT,
:WS-DEPOSIT-TOTAL
FROM UAB.TRANSACTION_HISTORY
WHERE ACCOUNT_NUMBER = :WS-INPUT-ACCOUNT
AND TRANS_DATE >= CURRENT DATE - 30 DAYS
END-EXEC.
The single monolithic query was split into two simpler queries. The second query combines the COUNT and SUM into a single scan using a CASE expression, eliminating the redundant second scan. With the covering index, this query uses index-only access and examines only the rows matching the specific account and date range.
Fix 3: REBIND the plan with REOPT(ONCE).
//REBIND EXEC PGM=IKJEFT01
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DB2P)
REBIND PLAN(HBTINQ01) -
ACQUIRE(USE) -
RELEASE(COMMIT) -
REOPT(ONCE) -
CURRENTDATA(NO)
END
/*
The REOPT(ONCE) option tells DB2 to re-optimize the access path at first execution using the actual host variable values rather than the default filter factor estimates. The CURRENTDATA(NO) option avoids unnecessary lock escalation for read-only queries. ACQUIRE(USE) and RELEASE(COMMIT) minimize lock duration.
Post-Optimization EXPLAIN
TABLE: UAB.ACCOUNT_MASTER
ACCESS: INDEX SCAN (XACCT01)
MATCHING COLUMNS: 1
ROWS EXAMINED: 1
TABLE: UAB.CUSTOMER_MASTER
ACCESS: INDEX SCAN (XCUST01)
MATCHING COLUMNS: 1
ROWS EXAMINED: 1
TABLE: UAB.BRANCH_MASTER
ACCESS: INDEX SCAN (XBRNCH01)
MATCHING COLUMNS: 1
ROWS EXAMINED: 1
QUERY 2:
TABLE: UAB.TRANSACTION_HISTORY
ACCESS: INDEX-ONLY SCAN (XTRHST02)
MATCHING COLUMNS: 2
ROWS EXAMINED: ~85 (AVERAGE PER ACCOUNT/30-DAY)
The TRANSACTION_HISTORY access changed from a 270-million-row tablespace scan to an index-only scan examining approximately 85 rows per account. The BRANCH_MASTER access changed from an 850-row tablespace scan to a single-row index lookup.
Phase 3: BMS Map Optimization
The AINQ transaction used a BMS map with 87 fields, including 42 fields for a 30-day transaction history display. The BMS SEND MAP command transmitted the entire map to the terminal on every response, even when only the account summary fields at the top of the screen had changed.
Original BMS Map Design
*================================================================*
* ORIGINAL: SEND ENTIRE MAP EVERY TIME
*================================================================*
EXEC CICS SEND
MAP('AINQMAP1')
MAPSET('AINQSET1')
FROM(AINQMAP1O)
ERASE
CURSOR
END-EXEC.
The ERASE option cleared the entire screen and retransmitted all 87 fields, generating approximately 3,200 bytes of 3270 data stream per response. At 45,000 transactions per hour, this produced 144 MB per hour of network traffic.
Optimized BMS Map Design
*================================================================*
* OPTIMIZED: SEND ONLY CHANGED FIELDS (DATAONLY ON REFRESH)
*================================================================*
3000-SEND-INITIAL-MAP.
* FIRST DISPLAY: SEND COMPLETE MAP WITH ALL FIELDS
EXEC CICS SEND
MAP('AINQMAP1')
MAPSET('AINQSET1')
FROM(AINQMAP1O)
ERASE
CURSOR
END-EXEC.
3100-SEND-REFRESH-MAP.
* SUBSEQUENT DISPLAYS: SEND ONLY CHANGED DATA FIELDS
* DO NOT RETRANSMIT LABELS, BORDERS, OR STATIC TEXT
MOVE LOW-VALUES TO AINQMAP1O
* SET ONLY THE FIELDS THAT CHANGED
MOVE WS-ACCT-NUMBER TO AINQ-ACCTNO
MOVE WS-CURR-BALANCE TO AINQ-BALO
MOVE WS-AVAIL-BALANCE TO AINQ-AVALO
MOVE WS-ACCT-STATUS TO AINQ-STATO
MOVE WS-CUST-NAME TO AINQ-NAMEO
MOVE WS-TRANS-COUNT TO AINQ-TCNTO
MOVE WS-DEPOSIT-TOTAL TO AINQ-DEPTO
EXEC CICS SEND
MAP('AINQMAP1')
MAPSET('AINQSET1')
FROM(AINQMAP1O)
DATAONLY
CURSOR
END-EXEC.
The DATAONLY technique: By moving LOW-VALUES to the entire output map before setting specific fields, the DATAONLY option tells BMS to transmit only those fields that differ from LOW-VALUES. Fields that remain LOW-VALUES are not transmitted, reducing the data stream from 3,200 bytes to approximately 400 bytes -- an 88% reduction.
This optimization reduces both network bandwidth and the CPU cost of building the 3270 data stream. For 3270 terminals on slow WAN links (common in branch banking), the reduction in data stream size directly translates to faster screen rendering at the terminal.
Phase 4: Pseudo-Conversational Redesign
The AXFR (balance transfer) transaction was implemented as a conversational transaction:
*================================================================*
* ORIGINAL: CONVERSATIONAL DESIGN (HOLDS RESOURCES)
*================================================================*
0000-MAIN.
PERFORM 1000-DISPLAY-SOURCE-ACCOUNT
PERFORM 2000-RECEIVE-TRANSFER-DETAILS
PERFORM 3000-DISPLAY-CONFIRMATION
PERFORM 4000-RECEIVE-CONFIRMATION
IF WS-USER-CONFIRMED
PERFORM 5000-EXECUTE-TRANSFER
END-IF
EXEC CICS RETURN END-EXEC.
2000-RECEIVE-TRANSFER-DETAILS.
* PROGRAM WAITS HERE FOR USER INPUT
* CICS TASK REMAINS ACTIVE, HOLDING:
* - DB2 THREAD (CONNECTION)
* - WORKING STORAGE (64KB)
* - CICS TASK CONTROL BLOCK
* AVERAGE WAIT: 12 SECONDS (USER THINK TIME)
EXEC CICS RECEIVE
MAP('AXFRMAP1')
MAPSET('AXFRSET1')
INTO(AXFRMAP1I)
END-EXEC.
4000-RECEIVE-CONFIRMATION.
* PROGRAM WAITS AGAIN FOR CONFIRMATION
* AVERAGE WAIT: 8 SECONDS
EXEC CICS RECEIVE
MAP('AXFRMAP2')
MAPSET('AXFRSET2')
INTO(AXFRMAP2I)
END-EXEC.
During the two user-wait periods (averaging 20 seconds combined), the conversational transaction held a DB2 thread, 64 KB of working storage, and a CICS task control block. With 800 concurrent transfer transactions, this consumed 800 DB2 threads and 50 MB of CICS working storage -- all idle, waiting for users to press Enter.
Pseudo-Conversational Redesign
IDENTIFICATION DIVISION.
PROGRAM-ID. UABAXFR2.
*================================================================*
* PROGRAM: UABAXFR2 - BALANCE TRANSFER (PSEUDO-CONVERSATIONAL)
* REDESIGNED TO RELEASE ALL RESOURCES BETWEEN USER INTERACTIONS
*================================================================*
DATA DIVISION.
WORKING-STORAGE SECTION.
01 WS-COMMAREA.
05 WS-CA-STATE PIC X(2).
88 STATE-INITIAL VALUE 'IN'.
88 STATE-DETAILS-ENTERED VALUE 'DE'.
88 STATE-CONFIRMED VALUE 'CF'.
05 WS-CA-SOURCE-ACCOUNT PIC X(10).
05 WS-CA-DEST-ACCOUNT PIC X(10).
05 WS-CA-AMOUNT PIC S9(11)V99 COMP-3.
05 WS-CA-SOURCE-BALANCE PIC S9(13)V99 COMP-3.
05 WS-CA-DEST-BALANCE PIC S9(13)V99 COMP-3.
05 WS-CA-SOURCE-NAME PIC X(30).
05 WS-CA-DEST-NAME PIC X(30).
05 WS-CA-TIMESTAMP PIC X(26).
LINKAGE SECTION.
01 DFHCOMMAREA PIC X(150).
PROCEDURE DIVISION.
0000-MAIN.
IF EIBCALEN = ZERO
PERFORM 1000-INITIAL-ENTRY
ELSE
MOVE DFHCOMMAREA TO WS-COMMAREA
EVALUATE TRUE
WHEN STATE-INITIAL
PERFORM 2000-PROCESS-DETAILS
WHEN STATE-DETAILS-ENTERED
PERFORM 3000-PROCESS-CONFIRMATION
WHEN OTHER
PERFORM 1000-INITIAL-ENTRY
END-EVALUATE
END-IF.
1000-INITIAL-ENTRY.
* DISPLAY THE INITIAL TRANSFER SCREEN
* AND RETURN TO CICS (RELEASING ALL RESOURCES)
INITIALIZE WS-COMMAREA
SET STATE-INITIAL TO TRUE
EXEC CICS SEND
MAP('AXFRMAP1')
MAPSET('AXFRSET2')
FROM(AXFRMAP1O)
ERASE
CURSOR
END-EXEC
EXEC CICS RETURN
TRANSID('AXFR')
COMMAREA(WS-COMMAREA)
LENGTH(LENGTH OF WS-COMMAREA)
END-EXEC.
2000-PROCESS-DETAILS.
* USER HAS ENTERED TRANSFER DETAILS
* VALIDATE, PREPARE CONFIRMATION, AND RETURN
EXEC CICS RECEIVE
MAP('AXFRMAP1')
MAPSET('AXFRSET2')
INTO(AXFRMAP1I)
END-EXEC
PERFORM 2100-VALIDATE-INPUT
IF INPUT-IS-VALID
PERFORM 2200-LOOKUP-ACCOUNTS
PERFORM 2300-DISPLAY-CONFIRMATION
SET STATE-DETAILS-ENTERED TO TRUE
ELSE
PERFORM 2400-DISPLAY-ERROR
SET STATE-INITIAL TO TRUE
END-IF
EXEC CICS RETURN
TRANSID('AXFR')
COMMAREA(WS-COMMAREA)
LENGTH(LENGTH OF WS-COMMAREA)
END-EXEC.
2200-LOOKUP-ACCOUNTS.
* VERIFY BOTH ACCOUNTS AND GET CURRENT BALANCES
EXEC SQL
SELECT CURRENT_BALANCE, CUSTOMER_NAME
INTO :WS-CA-SOURCE-BALANCE,
:WS-CA-SOURCE-NAME
FROM UAB.ACCOUNT_MASTER
WHERE ACCOUNT_NUMBER = :WS-CA-SOURCE-ACCOUNT
END-EXEC
IF SQLCODE NOT = 0
SET INPUT-IS-INVALID TO TRUE
MOVE 'SOURCE ACCOUNT NOT FOUND'
TO WS-ERROR-MESSAGE
ELSE
EXEC SQL
SELECT CURRENT_BALANCE, CUSTOMER_NAME
INTO :WS-CA-DEST-BALANCE,
:WS-CA-DEST-NAME
FROM UAB.ACCOUNT_MASTER
WHERE ACCOUNT_NUMBER =
:WS-CA-DEST-ACCOUNT
END-EXEC
IF SQLCODE NOT = 0
SET INPUT-IS-INVALID TO TRUE
MOVE 'DESTINATION ACCOUNT NOT FOUND'
TO WS-ERROR-MESSAGE
END-IF
END-IF.
2300-DISPLAY-CONFIRMATION.
MOVE WS-CA-SOURCE-ACCOUNT TO AXFR-SRCACCTO
MOVE WS-CA-SOURCE-NAME TO AXFR-SRCNAMEO
MOVE WS-CA-SOURCE-BALANCE TO AXFR-SRCBALO
MOVE WS-CA-DEST-ACCOUNT TO AXFR-DSTACCTO
MOVE WS-CA-DEST-NAME TO AXFR-DSTNAMEO
MOVE WS-CA-AMOUNT TO AXFR-AMTO
EXEC CICS SEND
MAP('AXFRMAP2')
MAPSET('AXFRSET2')
FROM(AXFRMAP2O)
ERASE
CURSOR
END-EXEC.
3000-PROCESS-CONFIRMATION.
* USER HAS CONFIRMED (OR CANCELLED) THE TRANSFER
EXEC CICS RECEIVE
MAP('AXFRMAP2')
MAPSET('AXFRSET2')
INTO(AXFRMAP2I)
END-EXEC
IF AXFR-CONFIRMI = 'Y'
PERFORM 3100-EXECUTE-TRANSFER
ELSE
PERFORM 3200-CANCEL-TRANSFER
END-IF
* RETURN TO MENU (NO COMMAREA = END OF CONVERSATION)
EXEC CICS RETURN END-EXEC.
3100-EXECUTE-TRANSFER.
* RE-READ ACCOUNTS WITH UPDATE LOCK
* (BALANCES MAY HAVE CHANGED SINCE LOOKUP)
EXEC SQL
SELECT CURRENT_BALANCE
INTO :WS-CURRENT-SRC-BAL
FROM UAB.ACCOUNT_MASTER
WHERE ACCOUNT_NUMBER = :WS-CA-SOURCE-ACCOUNT
FOR UPDATE OF CURRENT_BALANCE
END-EXEC
IF WS-CURRENT-SRC-BAL < WS-CA-AMOUNT
MOVE 'INSUFFICIENT FUNDS - BALANCE CHANGED'
TO WS-ERROR-MESSAGE
PERFORM 3300-DISPLAY-ERROR
ELSE
EXEC SQL
UPDATE UAB.ACCOUNT_MASTER
SET CURRENT_BALANCE =
CURRENT_BALANCE - :WS-CA-AMOUNT
WHERE ACCOUNT_NUMBER =
:WS-CA-SOURCE-ACCOUNT
END-EXEC
EXEC SQL
UPDATE UAB.ACCOUNT_MASTER
SET CURRENT_BALANCE =
CURRENT_BALANCE + :WS-CA-AMOUNT
WHERE ACCOUNT_NUMBER =
:WS-CA-DEST-ACCOUNT
END-EXEC
EXEC CICS SYNCPOINT END-EXEC
PERFORM 3400-DISPLAY-SUCCESS
END-IF.
COMMAREA Sizing Optimization
The COMMAREA is deliberately sized to contain only the minimum data needed to restore state between interactions:
WS-CA-STATE: 2 bytes
WS-CA-SOURCE-ACCOUNT: 10 bytes
WS-CA-DEST-ACCOUNT: 10 bytes
WS-CA-AMOUNT: 8 bytes (COMP-3)
WS-CA-SOURCE-BALANCE: 8 bytes (COMP-3)
WS-CA-DEST-BALANCE: 8 bytes (COMP-3)
WS-CA-SOURCE-NAME: 30 bytes
WS-CA-DEST-NAME: 30 bytes
WS-CA-TIMESTAMP: 26 bytes
--------
Total: 142 bytes
The original conversational transaction held 64 KB of working storage for the entire duration of user think time. The pseudo-conversational COMMAREA is 142 bytes -- a 99.8% reduction in per-transaction storage held during user interactions.
Re-Validation After User Confirmation
A critical design element in paragraph 3100-EXECUTE-TRANSFER is the re-read of the source account balance with FOR UPDATE before executing the transfer. Between the time the user saw the confirmation screen and the time they pressed Enter, other transactions may have changed the account balance. The re-read with FOR UPDATE obtains a lock on the row and verifies the current balance is still sufficient for the transfer. Without this re-validation, a race condition could allow overdrafts.
Combined Results
| Transaction | Before P95 | After P95 | Improvement |
|---|---|---|---|
| AINQ | 3,200 ms | 380 ms | 88% |
| AXFR | 5,800 ms | 620 ms | 89% |
| APAY | 2,100 ms | 480 ms | 77% |
| ATXN | 1,400 ms | 420 ms | 70% |
The resource utilization improvements were equally dramatic:
| Resource | Before | After | Improvement |
|---|---|---|---|
| DB2 CPU per AINQ | 1,840 ms | 45 ms | 98% |
| DB2 threads (peak) | 1,200 | 380 | 68% |
| CICS storage (peak) | 312 MB | 89 MB | 71% |
| Network bytes per AINQ | 3,200 | 400 | 88% |
Lessons Learned
1. EXPLAIN Is the Most Important Performance Tool for DB2
The entire DB2 optimization -- new indexes, query restructuring, REBIND options -- was driven by the EXPLAIN output. Without EXPLAIN, the team would have been guessing about access paths. The EXPLAIN showed that two subqueries were each performing 270-million-row tablespace scans, immediately identifying the root cause. Every CICS program with embedded SQL should have its SQL EXPLAINed as part of the code review process.
2. Covering Indexes Eliminate Table Access Entirely
The composite index on TRANSACTION_HISTORY (ACCOUNT_NUMBER, TRANS_DATE, TRANS_TYPE, AMOUNT) included all columns referenced in the query. This allowed DB2 to satisfy the query entirely from the index without accessing the base table -- a technique called "index-only access." The key design principle is to include frequently queried columns in the index, even if they are not part of the key predicate.
3. Conversational Transactions Are a Performance Anti-Pattern
The conversational AXFR transaction held resources for an average of 20 seconds per user interaction -- time during which the DB2 thread, CICS storage, and task control block were completely idle. Pseudo-conversational design eliminates this waste by returning to CICS after each screen send, freeing all resources for use by other transactions. The COMMAREA preserves just enough state to resume processing when the user responds.
4. BMS DATAONLY Reduces Network Traffic by an Order of Magnitude
For inquiry screens that display the same labels and borders on every invocation, DATAONLY sends only the variable data fields. The reduction from 3,200 bytes to 400 bytes per response had a noticeable impact at branch terminals connected through WAN links, where network latency amplifies the effect of large data streams.
5. Re-Validation Is Required in Pseudo-Conversational Design
The pseudo-conversational design introduces a window during which the data may change -- between the time the confirmation screen is displayed and the time the user responds. The AXFR program addresses this by re-reading the account balance with FOR UPDATE before executing the transfer. This "optimistic concurrency" approach is more efficient than holding locks during user think time (which is what the conversational design implicitly did), but it requires the programmer to explicitly handle the case where the data has changed.
Discussion Questions
-
The covering index on TRANSACTION_HISTORY includes four columns. Adding columns to an index increases its size and the cost of INSERT/UPDATE operations on the base table. How would you evaluate the tradeoff between query performance improvement and index maintenance overhead for a table with 270 million rows and 3 million inserts per day?
-
The pseudo-conversational AXFR program stores the transfer amount in the COMMAREA, which is passed through the CICS terminal control element. A technically sophisticated user could modify the COMMAREA contents between interactions. What validation steps should the program perform when it regains control to detect and prevent such tampering?
-
The REBIND used REOPT(ONCE), which re-optimizes the access path at first execution. An alternative is REOPT(ALWAYS), which re-optimizes on every execution. Under what circumstances would REOPT(ALWAYS) be preferable, and what is its performance cost?
-
The BMS DATAONLY optimization assumes that the screen labels and borders are already displayed from a previous SEND. What happens if the terminal loses connectivity and reconnects between the initial SEND and the DATAONLY SEND? How should the program detect and handle this situation?
-
The original conversational AXFR transaction held a DB2 thread during user think time. In the pseudo-conversational redesign, the DB2 thread is released and re-acquired on each interaction. What is the cost of thread acquisition, and under what high-volume conditions might a connection pool be necessary?
Connection to Chapter Concepts
This case study integrates several key concepts from Chapter 32:
-
DB2 performance tuning (Section: DB2 SQL Performance): EXPLAIN analysis, index creation, query restructuring, and REBIND options demonstrate the systematic approach to SQL performance optimization.
-
CICS transaction performance (Section: CICS Performance Tuning): BMS map optimization with DATAONLY, pseudo-conversational redesign, and COMMAREA sizing illustrate the key CICS performance techniques.
-
Pseudo-conversational design (Section: Pseudo-Conversational Programming): The complete transformation from conversational to pseudo-conversational design, including state management through COMMAREA and re-validation of data after user interaction.
-
Index design for performance (Section: DB2 Index Strategies): The covering index design and the analysis of index-only access versus table access demonstrate how index architecture directly affects transaction response time.
-
Resource management (Section: Managing CICS Resources): The analysis of DB2 threads, CICS storage, and task control blocks during conversational wait time illustrates why resource management is a critical performance concern.