Problem encountered with Merging 3 files together with ICETOOL and placing them in seperate columns in an OUTPUT file

Question

Recently encountered a problem with a merge/fusion using JCL code that i was trying to do today. To explain, I wanted to use ICETOOL to fusion/merge 3 different files and place the datas from these files in specific columns in an OUTPUT file.

In other words, i have 3 input files and i wanted to insert them to an output file but in a way that data from these files appear in separate columns in my output file. The description is as follows:

The data from the first INPUT file: to be placed from columns 1 to 7 INPUT2 file: to be placed from columns 10 to 20 and lastly INPUT3 file: to be placed from columns 25 to 40.

To be more detailed an example of my files looks like this : INPUT1 has the following records:

001 AAAAA

002 CCCCC

003 EEEEE

INPUT2 has the following records:

001 BBBBB

002 DDDDD

003 FFFFF

and INPUT3 has the following records:

001 BB232

002 DD985

003 FFF38

I want to join the data to get an OUTPUT file with data merged like this:

Picture of what the data final data should look like

how can I do this with ice tool ICETOOL and in my sysin using SELECT FROM parameter

My SYSIN looks like this:

Image of my SYSIN

Each time i submit the job i realise that only my INPUT2 data was copied and into the wrong columns (columns 1 to 7) in the OUTPUT file. NB: My INPUT2 file are names of individuals, INPUT2 file are numbers only, and INPUT3 file is a mixture of numbers and alphabets (addresses and numbers).

Answer 1

Looking at the data, it is quite simple to get the data from 3 files as a single record.

Based on what you showed ALL the input files (1 thru 3 ) have an LRECL=80 and RECFM=FB.

So the trick is to concantenate the 3 files to SORTIN dd and then to identify the beginning of each file you concanatenate with $$ content. Make sure to pick a constnat that NONE of your files have that character in the first 2 bytes.

You tag the File and number the records from each file with WHEN=GROUP. Based on the file tag number 1,2,3 we arrange the data in the respective positions.

To get the data on to a single, we sorted based on the record number which we added using WHEN=GROUP, so that the data records from each files are tagged together. ie the first record from file1 followed by first record from file2 followed by first record from file3 and so on.

Once the data is sorted, once again we use the WHEN=GROUP to move the file 1 and file 2 records on to file3 record. Once that is done all we need is get one record for each group of record numbers which is done using OUTFIL

Here is a DFSORT JCL which will give you the desired.

If your files have different LRECL then create another file with the same lrecl and add 1 record into it which contains '$$'

//STEP0100 EXEC PGM=SORT                               
//SYSOUT   DD SYSOUT=*                                 
//SORTIN   DD *                                        
$$                                                     
//         DD DISP=SHR,DSN=Your.input.file1            
//         DD *                                        
$$                                                     
//         DD DISP=SHR,DSN=Your.input.file2            
//         DD *                                        
$$                                                     
//         DD DISP=SHR,DSN=Your.input.file3
//*            
//SORTOUT  DD SYSOUT=*                                 
//SYSIN    DD *                                     
  INREC IFTHEN=(WHEN=INIT,                          
               BUILD=(41:01,15)),                   
                                                    
        IFTHEN=(WHEN=GROUP,                         
               BEGIN=(41,2,CH,EQ,C'$$'),            
                PUSH=(60:ID=2,                      
                      63:SEQ=8)),                   
                                                    
        IFTHEN=(WHEN=(60,2,ZD,EQ,1,AND,             
                      63,8,ZD,GT,1),                
             OVERLAY=(01:41,09)),                   
                                                    
        IFTHEN=(WHEN=(60,2,ZD,EQ,2,AND,             
                      63,8,ZD,GT,1),                
             OVERLAY=(10:41,10)),                   
                                                    
        IFTHEN=(WHEN=(60,2,ZD,EQ,3,AND,             
                      63,8,ZD,GT,1),                
             OVERLAY=(25:41,15))                    
                                                    
  SORT FIELDS=(63,8,CH,A),EQUALS                    
                                                    
  OUTREC IFTHEN=(WHEN=GROUP,                        
                BEGIN=(60,2,ZD,EQ,1),               
                  END=(60,2,ZD,EQ,3),               
                 PUSH=(01:01,09),RECORDS=3),        
                                                    
         IFTHEN=(WHEN=GROUP,                        
                BEGIN=(60,2,ZD,EQ,2),               
                  END=(60,2,ZD,EQ,3),               
                 PUSH=(10:10,10),RECORDS=2)         
                                                    
  OUTFIL OMIT=(63,8,ZD,EQ,1),                       
  REMOVECC,NODETAIL,BUILD=(40X),                    
  SECTIONS=(63,8,                                   
  TRAILER3=(01,40))                                 
/*                                                  

The output from this job based on the data shown is

----+----1----+----2----+----3----+----4
AAAAA    BBBBB          BB232           
CCCCC    DDDDD          DD985           
EEEEE    FFFFF          FFF38