IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Compare three files and get the output


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
purushottam

New User


Joined: 26 Feb 2007
Posts: 37
Location: pune

PostPosted: Tue Aug 16, 2011 9:06 pm
Reply with quote

This may be basics of DFSORT but I am not able to do it.

My requirement is as below :

I have three input files

Input1:

A.INPUT1.TXT (File format : FB, LREC 80, Key 14-34)
sample data :
Code:
000011111111199999999999999999999EF
000022222222288888888888888888888EF
000033333333377777777777777777777EF

Input2:

A.INPUT2.TXT(File format : FB, LREC 80, Key 14-34)
sample data :
Code:
000044444444499999999999999999999EN
000055555555588888888888888888888EN
000066666666611111111111111111111EN

Input3:
A.INPUT3.TXT (File format : FB, LREC 80, Key 14-34)
sample data :

Code:
000077777777799999999999999999999EA
000088888888877777777777777777777EA
000099999999933333333333333333333EA

It means all the three files can contain some records and it is possible that record with same key is available in other two file or just second file or none of other two.

Now as output I need to match based on Key(14-34) and get the below

Outputfile :
Code:
0000111111111EF0000444444444EN0000777777777EA99999999999999999999
0000222222222EF0000555555555EN0000000000000EA88888888888888888888
0000333333333EF0000000000000EN0000888888888EA77777777777777777777
0000000000000EF0000666666666EN0000000000000EA11111111111111111111
0000000000000EF0000000000000EN0000999999999EA33333333333333333333

I hope i have made it clear to get some help. I am using October, 2010 DFSORT functions.

Code:
ICE201I H RECORD TYPE IS F - DATA STARTS IN POSITION 1
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Aug 16, 2011 9:56 pm
Reply with quote

can there be dups within a file?

you only want matched? whether it be 2 or 3 or only 3?

the process would be match 2 files,
matches would be output,
everything else dropped (or do you also want a discard file?)

the double matches from the 2 files would then be matched to the third
creating a 3 match file,
and everything else:
no match
2 macthes
would be dropped.

Is that a good definition of your expectations?
Back to top
View user's profile Send private message
purushottam

New User


Joined: 26 Feb 2007
Posts: 37
Location: pune

PostPosted: Tue Aug 16, 2011 10:36 pm
Reply with quote

Correction in my first post is Key postions in all 3 files are 14 to 33 (20 character field)

File's will not have any duplicates. All the records available in 3 files should be in output file.

Case1: Key is available in all the files
File 1 has
Code:
000011111111199999999999999999999EF

File2 has
Code:
000044444444499999999999999999999EN
File3 has
Code:
000077777777799999999999999999999EA

Then output should be
First 13 postions of file 1 + 34 and 35 postions of file1 (Always 'EF') + First 13 position of file2 + 34 and 35 postions of file2 (Always 'EN') + First 13 position of file3 + 34 and 35 postions of file3 (Always 'EA') + 14 to 33 from any one of 3 files (As it is same)
Code:
0000111111111EF0000444444444EN0000777777777EA99999999999999999999

**
Case2: Key is available in only 2 files
File1
Code:
000033333333377777777777777777777EF

File2
Dont have any record with key as 77777777777777777777
File3
Code:
000088888888877777777777777777777EA

Then the output should be
First 13 postions of file 1 + 34 and 35 postions of file1(Always 'EF') + First 13 position of file2(As there is no record so it should be 13 zeros) + 34 and 35 postions of file2(There is no record then also EN) + First 13 position of file3 + 34 and 35 postions of file3 (Always 'EA') + 14 to 33 from any one of 2 files (Either file1 or file2)
Code:
0000333333333EF0000000000000EN0000888888888EA77777777777777777777

And similararly if record is there in File2 and File3 but not in File1. Or record is in File1 and File2 but not in File3
**
Case3: Key is available in only 1 files

Input1
Dont have any record with key as 33333333333333333333
Input2
Dont have any record with key as 33333333333333333333
Input3
Code:
000099999999933333333333333333333EA

Then the output should be

13 0's + EF + 13 0's + EN + First 13 position of file3 + 34 and 35 postions of file3(Always 'EA') + 14 to 33 from file3

Code:
0000000000000EF0000000000000EN0000999999999EA33333333333333333333


And similar if record is in file1 but not in file2 and file3 and so on

Hope this help !
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Tue Aug 16, 2011 11:30 pm
Reply with quote

purushottam,

Use the following DFSORT JCL which will give you the desired results. The output is sorted on the key.

Code:

//STEP0100 EXEC PGM=SORT                                             
//SYSOUT   DD SYSOUT=*                                               
//SORTIN   DD *                                                       
$$
//         DD DSN=your 80 byte file 1,DISP=SHR
//         DD *                                                       
$$
//         DD DSN=your 80 byte file 2,DISP=SHR
//         DD *                                                       
$$
//         DD DSN=your 80 byte file 3,DISP=SHR
//SORTOUT  DD SYSOUT=*                                               
//SYSIN    DD *                                                       
  INREC IFTHEN=(WHEN=INIT,BUILD=(3C'0000000000000  ',81:1,80)),       
  IFTHEN=(WHEN=INIT,OVERLAY=(14:C'EF',29:C'EN',44:C'EA')),           
  IFTHEN=(WHEN=GROUP,BEGIN=(81,2,CH,EQ,C'$$'),PUSH=(161:ID=1,SEQ=8)),
  IFTHEN=(WHEN=(161,1,ZD,EQ,1,AND,162,8,ZD,GT,1),OVERLAY=(01:81,13)),
  IFTHEN=(WHEN=(161,1,ZD,EQ,2,AND,162,8,ZD,GT,1),OVERLAY=(16:81,13)),
  IFTHEN=(WHEN=(161,1,ZD,EQ,3,AND,162,8,ZD,GT,1),OVERLAY=(31:81,13)) 
                                                                     
  SORT FIELDS=(94,20,CH,A),EQUALS                                     
  SUM FIELDS=(1,13,16,13,31,13),FORMAT=ZD                             
 
  OUTFIL OMIT=(81,3,CH,EQ,C'$$'),                                     
  BUILD=(1,45,94,22)                                                 
//*


The output from this job is
Code:

0000000000000EF0000666666666EN0000000000000EA11111111111111111111EN
0000000000000EF0000000000000EN0000999999999EA33333333333333333333EA
0000333333333EF0000000000000EN0000888888888EA77777777777777777777EF
0000222222222EF0000555555555EN0000000000000EA88888888888888888888EF
0000111111111EF0000444444444EN0000777777777EA99999999999999999999EF
Back to top
View user's profile Send private message
purushottam

New User


Joined: 26 Feb 2007
Posts: 37
Location: pune

PostPosted: Wed Aug 17, 2011 1:59 pm
Reply with quote

Wow !! It worked perfectly.

Thanks Much !!
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA

PostPosted: Wed Aug 17, 2011 8:10 pm
Reply with quote

Kolusu,
Unless I am missing something,not sure if $$ delimiter is needed here because each file can only have one value at 34th position. Also, I could be wrong here but that is your assumption as well.
Keeping the same in mind, wouldn't below method (on a similar lines of your provided solution) be suffice?

Code:
//STEP0100 EXEC PGM=SORT                                     
//SYSOUT   DD SYSOUT=*                                       
//SORTIN   DD *                                               
000011111111199999999999999999999EF                           
000033333333377777777777777777777EF                           
000022222222288888888888888888888EF                           
000044444444499999999999999999999EN                           
000066666666611111111111111111111EN                           
000055555555588888888888888888888EN                           
000077777777799999999999999999999EA                           
000099999999933333333333333333333EA                           
000088888888877777777777777777777EA                           
//SORTOUT  DD SYSOUT=*                                       
//SYSIN    DD *                                               
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(81:39C'0',34,02)),         
        IFTHEN=(WHEN=(34,2,CH,EQ,C'EF'),OVERLAY=(081:1,13)), 
        IFTHEN=(WHEN=(34,2,CH,EQ,C'EN'),OVERLAY=(094:1,13)), 
        IFTHEN=(WHEN=(34,2,CH,EQ,C'EA'),OVERLAY=(107:1,13))   
  SORT FIELDS=(14,20,CH,A),EQUALS                             
  SUM FIELDS=(81,13,ZD,94,13,ZD,107,13,ZD)                   
  OUTFIL BUILD=(81,13,C'EF',94,13,C'EN',107,13,C'EA',14,20)   
/*                                                           

OUTPUT
Code:
0000000000000EF0000666666666EN0000000000000EA11111111111111111111
0000000000000EF0000000000000EN0000999999999EA33333333333333333333
0000333333333EF0000000000000EN0000888888888EA77777777777777777777
0000222222222EF0000555555555EN0000000000000EA88888888888888888888
0000111111111EF0000444444444EN0000777777777EA99999999999999999999

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

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Wed Aug 17, 2011 9:45 pm
Reply with quote

sqlcode1 wrote:
Kolusu,
Unless I am missing something,not sure if $$ delimiter is needed here because each file can only have one value at 34th position. Also, I could be wrong here but that is your assumption as well.
Keeping the same in mind, wouldn't below method (on a similar lines of your provided solution) be suffice?


Sqlcode,

I am not sure as to why you think that I assumed that each file has ONLY 1 value at pos 34. I used fixed constants on the output where OP specifically mentioned about it. I did NOT assume the values on the input and arrange the values based on it. I don't like to assume based on the sample data posted here. I posted a generic solution instead of relying on the sample data. The moment you have a different value other than EF on the first file , your solution will fail. May be the right question you need to ask is OP if your assumption is correct or not as I have no clue about the data.
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA

PostPosted: Wed Aug 17, 2011 10:28 pm
Reply with quote

Kolusu,
My statement/assumption was based on below comment from OP....
Quote:
First 13 postions of file 1 + 34 and 35 postions of file1 (Always 'EF') + First 13 position of file2 + 34 and 35 postions of file2 (Always 'EN') + First 13 position of file3 + 34 and 35 postions of file3 (Always 'EA') + 14 to 33 from any one of 3 files (As it is same)


However, I didn't mean to question your solution at all. I was just trying to seek your input on saving couple of IFTHENs.

Thanks,
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts Write line by line from two files DFSORT/ICETOOL 7
No new posts Sortjoin and Search for a String and ... DFSORT/ICETOOL 1
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Merge two VSAM KSDS files into third ... JCL & VSAM 6
No new posts Joinkeys - 5 output files DFSORT/ICETOOL 7
Search our Forums:

Back to Top