Need to Compare 2 files and select matching/MERGE records



Support for NetApp SyncSort for z/OS, Visual SyncSort, SYNCINIT, SYNCLIST and SYNCTOOL

Need to Compare 2 files and select matching/MERGE records

Postby rockstar2020 » Wed Jan 08, 2014 4:06 pm

Hello All,

I'm new to the forum and I've requirement where I got two files with differnt record length as shown below
FIle 1
------
John 123 SRO
Mark 124 DGE
MAtt 456 SRT
JULE 999 DRT

File 2
------
111JOHN123aaaaaaaaa
012LOLE444bbbbbbbbb
222MARK124ccccccccc
333JOHN145ddddddddd
444MATT456eeeeeeeee
555JOHN111fffffffff
666MIKE145ggggggggg

oUTPUT FILE
-----------
John 123 SRO 111aaaaaaaaa
Mark 124 DGE 222ccccccccc
MAtt 456 SRT 444eeeeeeeee

Note-
File 1 and FIle 2 have differnt record lengths but FB
FIle 1 will not have dupplicates but File 2 can have, I mean for example both files can have 'JOHN'(as a field) but FIle 2 will not have duplicae for 'JOHN' and '123' combined

I tried SPLICE with ICETOOL but i didnt get the output.

THanks and Appreciate your help..
rockstar2020
 
Posts: 32
Joined: Wed Jan 08, 2014 3:51 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Need to Compare 2 files and select matching/MERGE records

 

Re: Need to Compare 2 files and select matching/MERGE record

Postby rockstar2020 » Wed Jan 08, 2014 4:10 pm

Forgot to say...
In above example, John along 123 is the key that should be there in both files and if so i need to write to the output file. Please let me know if how can i proceed.
rockstar2020
 
Posts: 32
Joined: Wed Jan 08, 2014 3:51 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Need to Compare 2 files and select matching/MERGE record

Postby NicC » Wed Jan 08, 2014 5:21 pm

Have you looked at JOINKEYS?
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 2690
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisys (almost)
Has thanked: 4 times
Been thanked: 105 times

Re: Need to Compare 2 files and select matching/MERGE record

Postby rockstar2020 » Wed Jan 08, 2014 5:36 pm

Actually I've not used SPLLICE/SELECT or JOINKEYS before. I tried JOINKEYS but couldn't really able to understand / use / implement to cover my logic.

Thanks
rockstar2020
 
Posts: 32
Joined: Wed Jan 08, 2014 3:51 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Need to Compare 2 files and select matching/MERGE record

Postby BillyBoyo » Wed Jan 08, 2014 6:31 pm

I'd suggest you leave SPLICE to one side as a beginner.

If you have a look here, dfsort-icetool-icegener/topic9664.html#p48087, you'll see a link to the documentation for JOINKEYS. There are many JOINKEYS examples here.

You will also be able to find documentation and examples for ICETOOL's SELECT operator.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: Need to Compare 2 files and select matching/MERGE record

Postby rockstar2020 » Thu Jan 09, 2014 1:22 pm

Hello All,
I tried JOINKEYS for the first time but got the below error. please help.
JCL
---
000008 //STEP02 EXEC PGM=SORT
000009 //SORTJNF1 DD DSN=xxxxxxxxxx,DISP=SHR
000010 //SORTJNF2 DD DSN=yyyyyyyyyyy,DISP=SHR
000011 //SORTOUT DD DSN=zzzzzzzzzzz,DISP=(NEW,CATLG,DELETE),
000012 // LRECL=23,RECFM=F,SPACE=(TRK,(1,1))
000013 //SYSOUT DD SYSOUT=*
000014 //SYSIN DD *
000015 JOINKEYS FILE=F1,FIELDS=(1,6,A)
000016 JOINKEYS FILE=F2,FIELDS=(5,10,A)
000017 REFORMAT FIELDS=(F1:1,6,8,2,F2:12,15)
000018 OPTION COPY
000019 /*

FIle 1
----+----1----+----2-
*********************
AAAAAA 11
BBBBBB 11
CCCCCC 11
DDDDDD 11
EEEEEE 11

----+----1----+---
******************
111 AAAAAA PHYS
222 BBBBBB CHEM
333 CCCCCC MATH
444 DDDDDD BIOL
555 EEEEEE ARTS

Error below -- I got Macc=0 though
--------------

WER436I UNEQUAL MAINTENANCE APPLIED TO GLOBAL DSM AND SYNCSORT LIBRARIES
WER186I SVC 109-018 IS INCORRECT VERSION OR NOT A SYNCSORT SVC; SVC NOT USED -
rockstar2020
 
Posts: 32
Joined: Wed Jan 08, 2014 3:51 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Need to Compare 2 files and select matching/MERGE record

Postby enrico-sorichetti » Thu Jan 09, 2014 2:57 pm

WER436I UNEQUAL MAINTENANCE APPLIED TO GLOBAL DSM AND SYNCSORT LIBRARIES
WER186I SVC 109-018 IS INCORRECT VERSION OR NOT A SYNCSORT SVC; SVC NOT USED -


looks like there is a problem in Your installation
work with Your support
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2644
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 130 times

Re: Need to Compare 2 files and select matching/MERGE record

Postby rockstar2020 » Thu Jan 09, 2014 3:05 pm

I read JOINKEYS and SPLICE will not work for less than SYNCSORT 1.2 versions from below link
http://ibmmainframes.com/about32844.html

I found our shop is using SYNCSORT 1.4 though i'm not sure, got below messages from DFSMSG
SYNCSORT FOR Z/OS 1.4.0.1NI
WER169I RELEASE 1.4 BATCH 0520 TPF LEVEL 0.1.

If Its installation issue or we cannot install the correct versions, is there any work around for my requirement by using other methods. Please let me.

Thanks
rockstar2020
 
Posts: 32
Joined: Wed Jan 08, 2014 3:51 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Need to Compare 2 files and select matching/MERGE record

Postby dick scherrer » Thu Jan 09, 2014 7:37 pm

Hello,

You need to have the product fixed. Someone has "broken" it.

This will need your system programmer and possibly Syncsort support.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: Need to Compare 2 files and select matching/MERGE record

Postby rockstar2020 » Wed Jan 15, 2014 1:04 pm

Hello All,
i was able to work it out finally. But i need one more help. Can you please let me know how to insert SPACES using REFORMAT. As u could see i used 5,1 to populate spaces temporarily.
Also let me know the use of outfiles in the below code.
Thank you
//STEP02 EXEC PGM=SORT                                                 
//SORTJNF1 DD DSN=xxxx,DISP=SHR           
//SORTJNF2 DD DSN=yyyy,DISP=SHR     
//SORTOF01 DD DSN=zzzz,DISP=(NEW,CATLG,DELETE),             
//   LRECL=180,RECFM=FB,SPACE=(TRK,(30,30))                           
//SYSOUT DD SYSOUT=*                                                   
//SYSIN DD *                                                           
  JOINKEYS FILE=F1,FIELDS=(1,14,A,23,6,A)                             
  JOINKEYS FILE=F2,FIELDS=(1,14,A,15,6,A)                             
  REFORMAT FIELDS=(F1:1,126,5,1,F2:31,26,5,1,57,26)                   
  SORT FIELDS=COPY                                                     
    OUTFIL FILES=01                                                   
/*                                                                   
rockstar2020
 
Posts: 32
Joined: Wed Jan 08, 2014 3:51 pm
Has thanked: 0 time
Been thanked: 0 time

Next

Return to Syncsort/Synctool

 


  • Related topics
    Replies
    Views
    Last post