𝗧𝗿𝗮𝗻𝘀𝗳𝗼𝗿𝗺𝗶𝗻𝗴 𝗮 𝗖𝗢𝗕𝗢𝗟 𝗔𝗿𝗿𝗮𝘆 𝗶𝗻𝘁𝗼 𝗮 𝗧𝗲𝗺𝗽𝗼𝗿𝗮𝗿𝘆 𝗧𝗮𝗯𝗹𝗲 𝗮𝗻𝗱 𝗝𝗼𝗶𝗻𝗶𝗻𝗴 𝘄𝗶𝘁𝗵 𝗮 𝗗𝗕𝟮 𝘁𝗮𝗯𝗹𝗲

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.