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
 

 

Check file 1 entries with file 2, if not found write to out

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

New User


Joined: 21 Oct 2006
Posts: 9

PostPosted: Sat Oct 21, 2006 3:49 am    Post subject: Check file 1 entries with file 2, if not found write to out
Reply with quote

Hi,

I am having 2 files and needs to output to 3rd file from file 2, filtering the records which are in file 1.

ex:

file 1:
Code:
1 aaa chicago
2 bbb newjersey

file 2:
Code:
1 aaa chicago
1 ccc boston
3 ccc loasangeles
2 bbb newjersey
1 aaa chicago
2 bbb newjersey


output file:
Code:
1 ccc boston
3 ccc loasangeles


Check file 1 entries with file 2, if not found write to outfile.

I appreciate if someone can help me....

Title Edited from "How to sort 2 files using icetool" to "Check file 1 entries with file 2, if not found write to out": Priyesh.

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

priyesh.agrawal

Senior Member


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

PostPosted: Sat Oct 21, 2006 11:12 am    Post subject: Re: How to sort 2 files using icetool
Reply with quote

Code:
//STEP010     EXEC PGM=ICETOOL                   
//TOOLMSG   DD SYSOUT=*                       
//DFSMSG    DD SYSOUT=*                       
//IN     DD DSN=RECORDS.INPUT1,DISP=SHR
//         DD DSN=RECORDS.INPUT2,DISP=SHR
//OUT    DD DSN=RECORDS.OUT,     
//          DISP=(,CATLG,DELETE),             
//TOOLIN DD *                                 
  SELECT FROM(IN) TO(OUT) ON(1,L,CH) NODUPS 
//


L= Length of the record, based on which you want to filter duplicates.
Back to top
View user's profile Send private message
beeeru

New User


Joined: 21 Oct 2006
Posts: 9

PostPosted: Sat Oct 21, 2006 9:30 pm    Post subject: Re: Check file 1 entries with file 2, if not found write to
Reply with quote

Hi Priyesh,

Thanks for your help.
You solution will delete all duplicates in both the files, if I am not wrong.
But my requirement is if the records which are in file 2, matches with file 1, then the record should NOT write to output file. But for ex if there are duplicate records in file 2, but no corresponding records in file 1, then I need to write all the records even though they are duplicates in file 2, to output file.

Pl let me know, if I am confusing.

ex:
file 1:
Code:
1 aaa randy
2 bbb john
2 bbb john
4 ddd annie


file 2:
Code:
1 aaa randy
1 aaa randy
3 ccc mary
3 ccc mary
2 bbb john

output file:
Code:
3 ccc mary
3 ccc mary


Thanks,
Beeru
Back to top
View user's profile Send private message
priyesh.agrawal

Senior Member


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

PostPosted: Sat Oct 21, 2006 9:38 pm    Post subject: Re: Check file 1 entries with file 2, if not found write to
Reply with quote

OK, see here...
Back to top
View user's profile Send private message
beeeru

New User


Joined: 21 Oct 2006
Posts: 9

PostPosted: Sat Oct 21, 2006 11:23 pm    Post subject:
Reply with quote

Hi Priyesh,
will the steps work for multiple records, if there in file 2, in the example you shown below. I am getting error if file 2 has many entries... 'DUPLICATE SYMBOL DEFINITION'

I apologize, if I am too much buggy...

Thanks,
Beeru
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: Sun Oct 22, 2006 1:07 am    Post subject:
Reply with quote

Beeru,

Here's a DFSORT/ICETOOL job that will do what you asked for. I assumed your input files have RECFM=FB and LRECL=80, but the job can be changed appropriately for other attributes.

Code:

//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD *
1 aaa randy
2 bbb john
2 bbb john
4 ddd annie
/*
//IN2 DD *
1 aaa randy
1 aaa randy
3 ccc mary
3 ccc mary
/*
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD SYSOUT=*
//TOOLIN DD *
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT) ON(1,80,CH) -
  WITH(1,81) KEEPNODUPS KEEPBASE USING(CTL3)
/*
//CTL1CNTL DD *
   INREC OVERLAY=(81:C'BB')
/*
//CTL2CNTL DD *
   INREC OVERLAY=(81:C'VV')
/*
//CTL3CNTL DD *
   OUTFIL FNAMES=OUT,INCLUDE=(81,2,CH,EQ,C'VV'),
     BUILD=(1,80)
/*
Back to top
View user's profile Send private message
beeeru

New User


Joined: 21 Oct 2006
Posts: 9

PostPosted: Sun Oct 22, 2006 11:09 pm    Post subject: Re: Check file 1 entries with file 2, if not found write to
Reply with quote

Hi Frank,

Thanks for your response.
Your magic worked. However I am having small issue with the code. Not sure where I am doing wrong.

Duplicates in file 2 are filtered if they are more than 2 entries....upto 2 records, even if they are duplicates they are wrinting to out file.

ex:
file1
Code:
1 aaa randy
2 bbb john
2 bbb john
3 ccc mary
4 ddd annie


file 2
Code:
1 aaa randy
1 aaa randy
1 aaa randy
3 ccc mary
3 ccc mary
3 ccc mary
3 ccc mary
2 bbb john
5 eee ben

outfile is filtered if more than 2 records from file 2
Code:
1 aaa randy
1 aaa randy
2 bbb john
3 ccc mary
3 ccc mary
5 eee ben

but i am expecting only one record in outfile with
Code:
5 eee ben

can you pl tell me why it is filtering more than 2 duplicate records??

Once again I appreciate all your help, Frank.

Thanks
Back to top
View user's profile Send private message
priyesh.agrawal

Senior Member


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

PostPosted: Mon Oct 23, 2006 7:39 am    Post subject: Re: Check file 1 entries with file 2, if not found write to
Reply with quote

Beeeru... Pls try below code, if it works... I really can't test it before posting due to changed weather now a days...
Anyway, Frank would be coming with a panacea... regardless of the result of this code...

Frank... I m sorry, I know its not advisable to interfere when you are already involved in the SORT posts but couldnt resist curiosity.
Code:
//**********************************************************
//* REMOVES DUPLICATES FROM FIRST FILE
//**********************************************************
//S010     EXEC PGM=ICETOOL                   
//TOOLMSG   DD SYSOUT=*                       
//DFSMSG    DD SYSOUT=*                       
//IN     DD DSN=RECORDS.INPUT.FILE1,DISP=SHR
//OUT    DD DSN=RECORDS.OUT.FILE1,     
//          DISP=(,CATLG,DELETE)               
//TOOLIN DD *                                 
  SELECT FROM(IN) TO(OUT) ON(1,80,CH) NODUPS 
/*
//**********************************************************
//* REMOVES DUPLICATES FROM SECOND FILE
//**********************************************************
//S020     EXEC PGM=ICETOOL                   
//TOOLMSG   DD SYSOUT=*                       
//DFSMSG    DD SYSOUT=*                       
//IN     DD DSN=RECORDS.INPUT.FILE2,DISP=SHR
//OUT    DD DSN=RECORDS.OUT.FILE2,     
//          DISP=(,CATLG,DELETE)             
//TOOLIN DD *                                 
  SELECT FROM(IN) TO(OUT) ON(1,80,CH) NODUPS 
/*
//**********************************************************
//* FILTERS DUPLICATES IN BOTH THE FILES
//**********************************************************
//S030     EXEC PGM=ICETOOL                   
//TOOLMSG   DD SYSOUT=*                       
//DFSMSG    DD SYSOUT=*                       
//IN     DD DSN=RECORDS.OUT.FILE1,DISP=SHR
//         DD DSN=RECORDS.OUT.FILE2,DISP=SHR
//OUT    DD DSN=RECORDS.OUT.FILTERED,     
//          DISP=(,CATLG,DELETE)           
//TOOLIN DD *                                 
  SELECT FROM(IN) TO(OUT) ON(1,80,CH) NODUPS 
/*
//**********************************************************
//* TAKES RECORDS FROM FILE2, PRESENT IN FILTERED FILE ONLY.
//**********************************************************
//S040     EXEC PGM=ICETOOL                   
//TOOLMSG   DD SYSOUT=*                       
//DFSMSG    DD SYSOUT=*                       
//IN1     DD DSN=RECORDS.INPUT.FILE2,DISP=SHR
//IN2     DD DSN=RECORDS.OUT.FILTERED,DISP=SHR   
//T1 DD DSN=&&TMP,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT    DD DSN=RECORDS.OUT.FINAL,     
//          DISP=(,CATLG,DELETE)       
//TOOLIN DD *                                 
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT) ON(1,80,CH) -
  WITH(1,81) KEEPALLDUPS USING(CTL3)
/*
//CTL1CNTL DD *
   INREC OVERLAY=(81:C'BB')
/*
//CTL2CNTL DD *
   INREC OVERLAY=(81:C'VV')
/*
//CTL3CNTL DD *
   OUTFIL FNAMES=OUT,INCLUDE=(81,2,CH,EQ,C'BB'),
     BUILD=(1,80)
/*
//
Back to top
View user's profile Send private message
beeeru

New User


Joined: 21 Oct 2006
Posts: 9

PostPosted: Mon Oct 23, 2006 7:20 pm    Post subject: Re: Check file 1 entries with file 2, if not found write to
Reply with quote

Hi Priyesh,

I appreciate all your efforts in helping me.
I cant delete any of the duplicate entries from file 2, if the corresponding record is not present in file1. So cant do step 2 of your solution.

Anyway, I am trying out your sulution and see whether I can play around to get the desired result.

Once again I am thankfull to Priyesh and Frank for there efforts.

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

New User


Joined: 21 Oct 2006
Posts: 9

PostPosted: Mon Oct 23, 2006 8:57 pm    Post subject: Re: Check file 1 entries with file 2, if not found write to
Reply with quote

Hi Frank & Priyesh,

Suggested solution by Frank is working....w/o any changes.
May be it was my fault that when I was testing, file1 entries are appended with some junk characters at the end.

I apologize for this.

Appreciate all your help, guys. You are wonderfull.

Thanks,
Beeru
Back to top
View user's profile Send private message
priyesh.agrawal

Senior Member


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

PostPosted: Mon Oct 23, 2006 9:02 pm    Post subject: Re: Check file 1 entries with file 2, if not found write to
Reply with quote

Quote:
Suggested solution by Frank is working....w/o any changes.

Nothing surprising... icon_lol.gif
Quote:
I cant delete any of the duplicate entries from file 2, if the corresponding record is not present in file1. So cant do step 2 of your solution.

That is why last step (S040) refers back to the original file-2.
Code:
//IN1     DD DSN=RECORDS.INPUT.FILE2,DISP=SHR
Back to top
View user's profile Send private message
beeeru

New User


Joined: 21 Oct 2006
Posts: 9

PostPosted: Mon Oct 23, 2006 11:18 pm    Post subject: Re: Check file 1 entries with file 2, if not found write to
Reply with quote

Hi Frank,

I am sorry to say that, the function is not working correctly icon_sad.gif

With your solution, the output file is not writing more than 2 duplicate records from file 2 and which are not in file 1.

ex:
file 1
1 aaa randy
2 bbb john
2 bbb john
3 ccc mary
4 ddd annie

file 2:
1 aaa randy
1 aaa randy
1 aaa randy
3 ccc mary
3 ccc mary
3 ccc mary
3 ccc mary
2 bbb john
5 eee ben
5 eee ben
5 eee ben
5 eee ben
5 eee ben

outfile:
5 eee ben
5 eee ben

actually it is supposed to print all 5 entries of file 2.

any help...is appreciated.

Note: Priyesh, with your solution too, I am getting the same issue. But in yur case I am getting only one record to outfile from file 2, even though they are more than 1 record in the file. Also looks like there is no keyword 'KEEPALLDUPS', so I deleted that option from your solution as it is default option.

Thanks,
Beeru
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: Tue Oct 24, 2006 1:09 am    Post subject:
Reply with quote

beeru,

I tested with the data you originally gave me which had two dups at the most, so I didn't notice that more than two wouldn't work. To get all of the dups, you just need to add WITHALL to the SPLICE operator in the job I gave you.

Code:

SPLICE FROM(T1) TO(OUT) ON(1,80,CH) -
  WITHALL WITH(1,81) KEEPNODUPS KEEPBASE USING(CTL3)
Back to top
View user's profile Send private message
priyesh.agrawal

Senior Member


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

PostPosted: Tue Oct 24, 2006 1:30 am    Post subject: Re: Check file 1 entries with file 2, if not found write to
Reply with quote

Hi Frank,

Could you please have a look on the last step of my job, would including "WITHALL" solve the purpose there?

Here Iwas trying to get all the records from FILE2 which are present in FILTERED File.
Code:
//**********************************************************
//* TAKES RECORDS FROM FILE2, PRESENT IN FILTERED FILE ONLY.
//**********************************************************
//S040     EXEC PGM=ICETOOL                   
//TOOLMSG   DD SYSOUT=*                       
//DFSMSG    DD SYSOUT=*                       
//IN1     DD DSN=RECORDS.INPUT.FILE2,DISP=SHR
//IN2     DD DSN=RECORDS.OUT.FILTERED,DISP=SHR   
//T1 DD DSN=&&TMP,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT    DD DSN=RECORDS.OUT.FINAL,     
//          DISP=(,CATLG,DELETE)       
//TOOLIN DD *                                 
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT) ON(1,80,CH) -
  WITH(1,81) KEEPALLDUPS USING(CTL3)
/*
//CTL1CNTL DD *
   INREC OVERLAY=(81:C'BB')
/*
//CTL2CNTL DD *
   INREC OVERLAY=(81:C'VV')
/*
//CTL3CNTL DD *
   OUTFIL FNAMES=OUT,INCLUDE=(81,2,CH,EQ,C'BB'),
     BUILD=(1,80)
/*
//


Should it be like below to work properly.
Code:
  WITHALL(1,81) USING(CTL3)
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: Tue Oct 24, 2006 2:01 am    Post subject:
Reply with quote

WITHALL is a separate keyword from WITH(p,m). You can't specify WITHALL(p,m). Also KEEPALLDUPS is NOT a valid keyword. KEEPNODUPS and KEEPBASE are valid keywords.

I don't know what you're trying to do, so it's hard to comment on how to do it. Show me an example of the records in each input file and what you expect for output, and I'll show you how to do it.
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 Read two inputs and write into output... murali.andaluri DFSORT/ICETOOL 5 Wed Jul 26, 2017 7:35 pm
No new posts Extra character appears in file when ... Balu5491 All Other Mainframe Topics 1 Wed Jul 26, 2017 2:39 pm
No new posts SSH - known_hosts file configuration vasanthz All Other Mainframe Topics 2 Wed Jul 26, 2017 2:10 am
This topic is locked: you cannot edit posts or make replies. Fetching data from BAI File arunsoods JCL & VSAM 1 Wed Jul 19, 2017 4:28 pm
No new posts Write out NODUPS but just from one file Jay Villaverde DFSORT/ICETOOL 8 Fri Jul 14, 2017 12:44 am


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