Wednesday, March 15, 2023

How to use COBOL occurs depending on working storage section table in a SQL query

Let us say we have below COBOL working storage section table and we want to match all the employee numbers from this array to a DB2 table.

01 W-EMP-COUNT   PIC S9(04) COMP.
01 W-EMP-LIST.
   05 W-EMP-NUM OCCURS 1 TO 50 TIMES DEPENDING ON W-EMP-COUNT
                 PIC X(05).

Below SQL does exactly that 

WITH TEMP(IDX, T_EMP_NUM) AS 
(SELECT 1, LEFT(:W-EMP-LIST,5)
     FROM SYSIBM.SYSDUMMY1 
   UNION ALL
SELECT IDX+1, SUBSTR(:W-EMP-LIST,((IDX)*5)+1,5)
     FROM TEMP
 WHERE IDX < :W-EMP-COUNT
),
SELECT * FROM EMP_TABLE, TEMP 
    WHERE EMP_NO  = EMP_NUM;

Find and replace using using TSO batch utility

Below job does find and replace using TSO batch utility. 

//STEP01 EXEC PGM=IKJEFT01
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSTSIN  DD *
  EDIT 'MY.DATA.SET' OLD NONUM CNTL
  VERIFY ON
  TOP
  C * 9999999 'LOG NO' 'LOG YES' ALL
  TOP
  C * 9999999 'INTO TABLE' 'RESUME YES INTO TABLE' ALL
  TOP
  C * 0000001 'RESUME YES INTO TABLE' 'REPLACE INTO TABLE'
  SAVE
//*