JCL syncsort using Joinkeys to compare two files.



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

JCL syncsort using Joinkeys to compare two files.

Postby Kaushik_2016 » Sat Jan 16, 2016 12:46 pm

I need help to write JCL syncsort using Joinkeys to compare two files.
I want to eliminate records from File1 depending on File2:-
1. First 9bytes of File1 will match with the First 9bytes of the FIle2.If not matched,it should not be present in the output file.
2.9th byte can be space in the both file.

File1:
CFEISEAA1HELLO
CFEISEBB1HI
CEEISEZZ MODI

File2:
CFEISEBB1
CEEISEZZ+(SPACE)

Output File:
CFEISEBB1HI
CEEISEZZ MODI
Kaushik_2016
Kaushik_2016
 
Posts: 4
Joined: Sat Jan 16, 2016 12:09 am
Has thanked: 0 time
Been thanked: 0 time

Re: JCL syncsort using Joinkeys to compare two files.

Postby BillyBoyo » Sat Jan 16, 2016 4:48 pm

How did Kaushik_2016 get into the Output File?

Apart from that, it is a really simple JOINKEYS with a nine-byte key (it is of no consequence that that ninth byte may be space or not).

Don't specify a JOIN statement, and you'll only get the matches. Your REFORMAT statement only need include the data from F1.

Have a look at your manual. Choose the simplest example. Lots of examples also here.

Each JOINKEYS statement automatically sorts the data, which you need to be done for the matching to work.

You then show your output in the original order. If this is what you need, you need to keep an original sequence number. Confirm that please, as it complicates things considerably like needing to know which SyncSORT version you have, and whether it has JNFnCNTL files - as far as I am aware, they are not documented, but may well be supported, and if you are up-to-date enough and they are not there, they can be "patched".
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: JCL syncsort using Joinkeys to compare two files.

Postby Kaushik_2016 » Sun Jan 17, 2016 6:45 pm

First I want thank you Billy for the quick response.I tried so many combinations but I am not getting the expected output.

Yes,you are right! I want to show the output in the original order.
For an Example,
1. 1st 9th byte of File1(CFEISEBB1)is matching with the File2 . So CFEISEBB1HI should come in the output file.
2. 1st 9th byte of File1(CFEISEAA1) is not matching with the File2. So CFEISEAA1HELLO should not come in the output file.The whole line should be removed from the Fil1.

File Description with the details:-

1. File1 is a VB file and record length of 14004. 1st 9bytes records of File1 need to match with the 1st 9bytes of records
of File2.Basically I want to eliminate the records from File1, depending on the 1st 9bytes of File2.

2. File2 is also a VB file and record length of 99.


File1:-

CFEISEAA1HELLO
CFEISEBB1HI
CFEISECC1UDAY
CEEISEZZ MODI
CFEISEBB1RUPAK
CEEISEMM KAUSHIK
CFEISEBB1SUNDARBAN
CEEISEZZ UJJAL
CFEISEBB1HI


File2:-

CFEILA
CEEISEZZ
CFEISEBB1
CFEILI   
CFEISERR1



Output File records:-

CFEISEBB1HI
CEEISEZZ MODI
CFEISEBB1RUPAK
CFEISEBB1SUNDARBAN
CEEISEZZ UJJAL
CFEISEBB1HI



Please find below the code that I tried but not getting the result:-

//STEP01 EXEC PGM=SYNCSORT
//SYSOUT DD SYSOUT=*
//SORTJNF1 DD DSN= File1, DISP=SHR
//SORTJNF2 DD DSN= File2, DISP=SHR
//SORTOUT DD DSN= OutputFile,vol=(,,,99),
//           DISP=(NEW,CATLG,DELETE),
//           UNIT=SYSALLDA
//SYSIN DD *
JOINKEYS File=F1,FIELDS(5,9,A)                            (I start it from 5th position as the 1st 4th
JOINKEYS File=F2,FIELDS(5,9,A)                            bytes are blocked for FB file)
JOIN UNPAIRED,F1,F2
REFORMAT FIELDS=(F1:1,14004,F2:1,99,?)
OPTION COPY
OUTFIL FNAMES=SORTOUT,INCLUDE=(14104,1,CH,EQ,C'1'),
BUILD=(1,14004)
//*



Billy, Please help me.
Kaushik_2016
 
Posts: 4
Joined: Sat Jan 16, 2016 12:09 am
Has thanked: 0 time
Been thanked: 0 time

Re: JCL syncsort using Joinkeys to compare two files.

Postby BillyBoyo » Mon Jan 18, 2016 4:25 pm

Remove the JOIN statement. You only need matched records, your JOIN says matched, plus unmatched on F1 plus unmatched on F2.

If your F2 is fixed-length records, you probably want to start your key at position one.

Once the match has occurred, you do not need the F2 data, so don't include it in the REFORMAT statement. You also don't need the match-marker (the ?), since you only want matched records.

REFORMAT FIELDS=(F1:1,4,5)

That says "from F1, include the Record Descriptor word, because I want a variable length REFORMAT record, then include all data from position five to the end of the current record (which means all the data).

Since your REFORMAT record is now how you want the output to look, you don't need OUTFIL at all.

If no name is given, SORTOUT is the default for OUTFIL, so no need to specify it. Your REFORMAT was building fixed-length records, so your output would have been fixed-length 14004-byte records. Why your JCL is like that for the output file, I don't know - VOLUMES? Why?
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: JCL syncsort using Joinkeys to compare two files.

Postby Kaushik_2016 » Mon Jan 18, 2016 9:20 pm

Billy, can you please write a sample code depending on this input - output? I am still not getting it. I am sorry.
Kaushik_2016
 
Posts: 4
Joined: Sat Jan 16, 2016 12:09 am
Has thanked: 0 time
Been thanked: 0 time

Re: JCL syncsort using Joinkeys to compare two files.

Postby BillyBoyo » Mon Jan 18, 2016 10:49 pm

 OPTION COPY
 JOINKEYS FILE=F1,FIELDS=(5,9,A)  key for variable-length recs on file
 JOINKEYS FILE=F2,FIELDS=1,9,A)   key-start for fixed-length key file
*                                  no JOIN statement
 REFORMAT FIELDS=(F1:1,4,5)


That's it. Read through my previous whilst looking at this.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: JCL syncsort using Joinkeys to compare two files.

Postby Kaushik_2016 » Tue Jan 19, 2016 12:25 am

Billy, I tried your code. But I am getting the wrong result. The reason is that I need to add sequence before the 1st file.I tried but still not getting proper result.
. can you please help?

By using your code, I am getting the below output that is not correct:-
CFEISEBB1MODI
CEEISEZZ HI
CFEISEBB1RUPAK
CFEISEBB1SUNDARBAN
CEEISEZZ HI
CFEISEBB1UJJAL

It seems the output got messed up.

I added the sequence also in the code. OUTFIL VTOF,OUTREC=(1,SEQNUM,9,ZD,1,14004) - wrote this code for adding sequence. and it's worked.

But I dont understand how to remove the sequence again to get the correct output. I tried like this:-
JOINKEYS File=F1,FIELDS(5,9,A)
JOINKEYS File=F2,FIELDS(5,9,A)
REFORMAT FIELDS=(F1:1,4,5)
SORT FIELDS=(1,9,ZD,A)
OUTFIL OUTREC=(10,14004)

Thanks,
Kaushik
Kaushik_2016
 
Posts: 4
Joined: Sat Jan 16, 2016 12:09 am
Has thanked: 0 time
Been thanked: 0 time

Re: JCL syncsort using Joinkeys to compare two files.

Postby BillyBoyo » Tue Jan 19, 2016 4:12 pm

Sorry, I forgot about the original sequence for the output.

You need to add a sequence number to the unsorted file. Ideally you will be able to use JNF1CNTL for that.

To extend a variable-length record, you do it at the beginning of the record:

//JNF1CNTL DD *
  INREC BUILD=(1,4,SEQNUM,9,ZD,5)


That will create a new version of each record, starting with the RDW (it is necessary for variable-length records that the RDW from an existing variable-length record is used. Except for the REFORMAT statement, where you may have two sources of the RDW, or you can create a fixed-length record including data from variable-length records, it is easy: just 1,4 does it), then adding the sequence number (nine digits will allow for a maximum of 999,999,999 records, if you have significantly fewer, make your sequence number smaller, but always ensure it is large enough for the actual data), and then the data from the input record, starting at position five and automatically going to the end of each record, SORT knows the size).

You leave your REFORMAT statement the same. You change your COPY to a SORT on the sequence number. Then you need to get rid of the sequence number. This can be done in OUTREC or OUTFIL.

  OUTREC BUILD=(1,4,14)


That says, make a new record, with the RDW (it's variable-length) and the data starting from position 14 to the end of the current record, which is everything except the sequence number you added earlier.

You include the JNF1CNTL in your step. If that does not work, post your SyncSORT version.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times


Return to Syncsort/Synctool

 


  • Related topics
    Replies
    Views
    Last post