Page 1 of 2

Need to Compare 2 files and select matching/MERGE records

PostPosted: Wed Jan 08, 2014 4:06 pm
by rockstar2020
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..

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

PostPosted: Wed Jan 08, 2014 4:10 pm
by rockstar2020
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.

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

PostPosted: Wed Jan 08, 2014 5:21 pm
by NicC
Have you looked at JOINKEYS?

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

PostPosted: Wed Jan 08, 2014 5:36 pm
by rockstar2020
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

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

PostPosted: Wed Jan 08, 2014 6:31 pm
by BillyBoyo
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.

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

PostPosted: Thu Jan 09, 2014 1:22 pm
by rockstar2020
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 -

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

PostPosted: Thu Jan 09, 2014 2:57 pm
by enrico-sorichetti
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

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

PostPosted: Thu Jan 09, 2014 3:05 pm
by rockstar2020
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

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

PostPosted: Thu Jan 09, 2014 7:37 pm
by dick scherrer
Hello,

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

This will need your system programmer and possibly Syncsort support.

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

PostPosted: Wed Jan 15, 2014 1:04 pm
by rockstar2020
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                                                   
/*