It is not uncommon for COBOL programs to maintain a list of
values in a WORKING STORAGE table and use those values to search for matching
rows in a DB2 table. Consider the following working-storage structure:
01
WS-DIAG-CODE-TABLE.
05 WS-DIAG-CODE-COUNT PIC S9(04) COMP.
05 WS-DIAG-CODE-LIST.
10 WS-DIAG-CODE OCCURS 1 TO 100 TIMES
DEPENDING ON WS-DIAG-CODE-COUNT
PIC X(05).
Traditional Approach
The typical implementation is to perform a loop through each
occurrence of WS-DIAG-CODE and execute a DB2 query to fetch matching rows.
PERFORM VARYING IDX
FROM 1 BY 1
UNTIL IDX > WS-DIAG-CODE-COUNT
EXEC SQL
SELECT ...
FROM TEST_TABLE
WHERE DIAG_CODE = :WS-DIAG-CODE(IDX)
END-EXEC
END-PERFORM
While this approach is straightforward and easy to
understand, it can become a significant performance bottleneck. Each iteration
results in a separate database access, increasing CPU consumption and elapsed
time.
A More Efficient Alternative
A better approach is to convert the COBOL table into a
temporary result set within SQL and allow DB2 to process all diagnosis codes in
a single query.
This can be achieved using a recursive Common Table
Expression (CTE). The recursive CTE transforms the contents of the COBOL
array into a relational structure that can be joined directly with the target
DB2 table.
WITH TEMP (IDX,
DIAG_CODE) AS
(SELECT 1, LEFT(:WS-DIAG-CODE-LIST,
5)
FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT IDX + 1, SUBSTR(:WS-DIAG-CODE-LIST,
(IDX * 5) + 1, 5)
FROM TEMP
WHERE IDX <
:WS-DIAG-CODE-COUNT),
SELECT ...
FROM TEST_TABLE A, TEMP
T
WHERE A.DIAG_CODE =
T.DIAG_CODE;
Why This Approach Performs Better
By leveraging a recursive CTE:
- Only one
SQL statement is executed.
- DB2
can optimize the join operation internally.
- Database
round trips are eliminated.
- CPU
and elapsed time are significantly reduced, especially when the list
contains many values.
- The
solution scales better as the number of diagnosis codes grows.
Key Takeaway: Whenever you find yourself executing
the same SQL repeatedly for values stored in a COBOL OCCURS table, consider
transforming the data into a temporary table using a recursive CTE and let DB2
perform the matching in one pass. This is a classic example of replacing
row-by-row processing with high-performance set-based SQL.
