SPLICE of ICETOOL - Merge two or more flat files

Tuesday, November 6, 20120 comments


The Splice operator of ICETOOL is a very useful utility for matching and merging files based on a key. Now lets say we have two files with a common key as shown below:

File 1: 
Y12 89503 MKT
Y12 57301 MKT
Z35 02316 DEV
Y12 91073 MKT
Z35 18693 DEV

File 2:
89503 27M $9,185,354 SAN JOSE CA
72135 08M   $317,632 BOSTON   MA
18693 10M $8,732,105 BUFFALO  NY
57301 50M    $30,000 NEWARK   NJ

Now lets say we want the output file to have the information for a particular key value which consists of data from both the files. Lets say the output file should something like this.

Output:
Y12 89503 MKT 27M $9,185,354 CA
Y12 57301 MKT 50M    $30,000 NJ
Z35 02316 DEV
Y12 91073 MKT
Z35 18693 DEV 10M $8,732,105 NY

We can do this using simple Splice tricks. The point that needs to be noted here is just that both the files should have the key field in the same position. In this process both the files are merged to a single temp file and then the temp file is spliced to created the reformatted records which contains info from both the files.

//SPLCMRGE JOB  (WAR,,'ADMIN',GM,0085,),
//             'JCL MANAGEMENT 10.0',
//             MSGCLASS=V,
//             CLASS=E,
//             REGION=0M,
//             NOTIFY=&SYSUID
//SM01 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD *
Y12 89503 MKT
Y12 57301 MKT
Z35 02316 DEV
Y12 91073 MKT
Z35 18693 DEV
/*
//IN2 DD *
89503 27M $9,185,354 SAN JOSE CA
72135 08M   $317,632 BOSTON   MA
18693 10M $8,732,105 BUFFALO  NY
57301 50M    $30,000 NEWARK   NJ
/*
//TEMP1 DD DSN=&&TEMP1,DISP=(MOD,PASS),SPACE=(TRK,(5,5)),UNIT=SYSDA
//COMBINE DD SYSOUT=*
//TOOLIN DD *
   COPY FROM(IN1) TO(TEMP1) USING(CTL1)
   COPY FROM(IN2) TO(TEMP1) USING(CTL2)
   SPLICE FROM(TEMP1) TO(COMBINE) ON(5,5,ZD) WITH(15,17)
/*
//CTL1CNTL DD *
   OUTREC FIELDS=(1:1,14,31:X)
/*
//CTL2CNTL DD *
   OUTREC FIELDS=(5:1,5,15:7,15,30:33,2)
/*

You Can use this technique to merge more than two files as well:


//STEP0100 EXEC PGM=ICETOOL                            
//TOOLMSG  DD SYSOUT=*                                
//DFSMSG   DD SYSOUT=*                                
//IN       DD *                                        
1234 Y                                                
1268 Y                                                
1299 Y                                                
1290 Y                                                
1000 Y                                                
//         DD *                                        
1000   Y                                              
1250   Y                                              
1386   Y                                              
1290   Y                                              
//         DD *                                        
1000     Y                                            
1001     Y                                            
1003     Y                                            
//OUT      DD SYSOUT=*                                
//TOOLIN   DD *                                        
  SPLICE FROM(IN) TO(OUT) ON(1,4,CH) WITHEACH -        
  WITH(8,1) WITH(10,1) KEEPNODUPS         
//*

And the output looks like:

1000 Y Y Y 
1001     Y 
1003     Y 
1234 Y    
1250   Y  
1268 Y    
1290 Y Y  
1299 Y    
1386   Y  

Share this article :

Post a Comment

 
Support : Creating Website | Johny Template | Mas Template
Copyright © 2011. Atom's Arena - All Rights Reserved
Template Created by Creating Website Published by Mas Template
Proudly powered by Blogger