Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Output records of the first file whose key not in 2nd file

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
karthikraman_m

New User


Joined: 10 Jun 2005
Posts: 12
Location: India

PostPosted: Wed Oct 25, 2006 2:14 pm    Post subject: Output records of the first file whose key not in 2nd file
Reply with quote

Hi,

I have two flat files of same record length and same file layout. I need to create an output file with records of the first file whose key(position 81-100 byte) is not found in the second file. In other words, I should create an output file by removing the records from the first file whose key is available in the second file. The second file may contain duplicate keys. The key is in the same byte location (81-100) in both the files. Is there any simple way using JCL SORT to do this?

Any help is appreciated.

Thanks,
Karthik
Back to top
View user's profile Send private message

priyesh.agrawal

Senior Member


Joined: 28 Mar 2005
Posts: 1452
Location: Chicago, IL

PostPosted: Wed Oct 25, 2006 6:37 pm    Post subject: Re: Comparing files
Reply with quote

Check out this link... for DFSORT Tricks....

Look for "Create files with matching and non-matching records" in the List of tricks...
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Wed Oct 25, 2006 8:25 pm    Post subject:
Reply with quote

Karthik,

If you need more specific help than that DFSORT Trick provides, show an example of the records in each input file (relevant fields only) and what you expect for output. Also, give the RECFM and LRECL of each input file.
Back to top
View user's profile Send private message
karthikraman_m

New User


Joined: 10 Jun 2005
Posts: 12
Location: India

PostPosted: Thu Oct 26, 2006 10:26 am    Post subject:
Reply with quote

Hi Frank,

I have created the sample Input files and the output file that is needed for me. It will be great if you could assist me in getting this done through JCL SORT. Please find the details below. Thanks for your time and assistance.

Thanks,
Karthik Raman

All files are of LRECL=300 and RECFM=FB. The key(20 byte) is available in the byte position (81 ? 100) in all the files. The first input file does not contain duplicate key but the second input file may or may not contain duplicate keys. (Only keys may be duplicate in the second file but the records wont be duplicate). The output file should contain records as it is from the first file by removing the record keys found in second file.
The second column in the following example case is the 20 byte key.

Input File 1
20060101 01CE2006VIPSNM567893 DATA...
20050425 01FH2005VIPSNM876561 DATA...
20050317 01GT2005VIPSNM089334 DATA...
20050205 01FH2005VIPSNM847244 DATA... Key Match 1 .Do not copy
20060521 01PS2006VIPSNM524357 DATA...
20050511 01VB2005VIPSNM083424 DATA... Key Match 2 .Do not copy

Input File 2
20050714 01AU2005VIPSNM846353 DATA...
20050519 01VB2005VIPSNM083424 DATA... Duplicate key. Key Match 1.
20050923 01VB2005VIPSNM083424 DATA... Duplicate Key. Key Match 1.

20051130 01FH2005VIPSNM847244 DATA... Key Match 2.
20051210 01UR2005VIPSNM836942 DATA...

Output File
Output File contain records from Input File 1 without any matched keys in Input File 2.(i.e.,) All matched key records are removed from File 1 and written to output file. In this example case, the fourth and sixth records of input file 1 has matching key in input file 2. So, those two records are removed from the input file 1 and written to output file.

20060101 01CE2006VIPSNM567893 DATA...
20050425 01FH2005VIPSNM876561 DATA...
20050317 01GT2005VIPSNM089334 DATA...
20060521 01PS2006VIPSNM524357 DATA...


Thanks to Priyesh for pointing to the Sort tricks.
Back to top
View user's profile Send private message
karthikraman_m

New User


Joined: 10 Jun 2005
Posts: 12
Location: India

PostPosted: Thu Oct 26, 2006 2:11 pm    Post subject:
Reply with quote

Hi Frank,

I was able to solve my trouble by looking at one of your reply to another query. Here is the sample code that I tried with a sample file attribute and it ran fine.

Just let me know if I need to remember any important point in this code.

Thank you very much.

Thanks,
Karthik Raman

Code:

//PS020   EXEC PGM=ICETOOL                                       
//IN1       DD *                                                 
20060101 01CE2006VIPSNM567893 DATA...                           
20050425 01FH2005VIPSNM876561 DATA...                           
20050317 01GT2005VIPSNM089334 DATA...                           
20050205 01FH2005VIPSNM847244 DATA... KEY MATCH 1 .DO NOT COPY   
20060521 01PS2006VIPSNM524357 DATA...                           
20050511 01VB2005VIPSNM083424 DATA... KEY MATCH 2 .DO NOT COPY   
//IN2       DD *                                                 
20050714 01AU2005VIPSNM846353 DATA...                           
20050519 01VB2005VIPSNM083424 DATA... DUPLICATE KEY. KEY MATCH 1.
20050923 01VB2005VIPSNM083424 DATA... DUPLICATE KEY. KEY MATCH 1.
20051130 01FH2005VIPSNM847244 DATA... KEY MATCH 2.               
20051210 01UR2005VIPSNM836942 DATA...                           
//OUT       DD DSN=HQL.ICETOOL.OUTPUT,                           
//             DISP=(MOD,CATLG,CATLG)                           
//T1        DD DSN=HQL.COPBP.ICETOOL.TEMP,                       
//             UNIT=SYSDA,                                       
//             SPACE=(CYL,(5,5)),DISP=(MOD,PASS)                 
//TOOLIN    DD *                   
 COPY FROM(IN1) TO(T1) USING(CTL1)
 COPY FROM(IN2) TO(T1) USING(CTL2)
 SPLICE FROM(T1) TO(OUT) -         
            ON(10,20,CH) -         
             WITH(82,1) -         
             USING(CTL3) -         
             KEEPNODUPS           
/*                                 
//CTL1CNTL  DD *                   
  OUTREC FIELDS=(1,80,81:C'11')     
/*                                 
//CTL2CNTL  DD *                   
  OUTREC FIELDS=(1,80,81:C'22')     
/*                                 
//CTL3CNTL DD *                   
  OUTFIL FNAMES=OUT,               
        INCLUDE=(81,2,CH,EQ,C'11'),
        OUTREC=(1,80)                 
/*                                   
//TOOLMSG   DD SYSOUT=*               
//DFSMSG    DD SYSOUT=*               
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Incorrect output when trying to add n... monica1 PL/I & Assembler 7 Fri Jan 13, 2017 5:02 pm
No new posts Copy RECFM =VB TO FB file with RECL =... sahil41352 DFSORT/ICETOOL 3 Wed Dec 28, 2016 11:29 pm
No new posts Removing Duplicates based on certain ... chandracdac DFSORT/ICETOOL 8 Fri Dec 09, 2016 4:40 am
No new posts Add PD field from 2nd file to PD in 1st Sushant Garje DFSORT/ICETOOL 6 Thu Dec 01, 2016 4:32 pm
No new posts File Aid to File Manager conversion murali3955 IBM Tools 4 Thu Nov 24, 2016 3:41 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us