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

SORT- Joinkeys - compare two files
IBM Mainframe Forums -> JCL & VSAM
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
MadanVS

New User


Joined: 28 Dec 2010
Posts: 25
Location: Chennai
PostPosted: Mon Mar 14, 2011 5:13 pm
 Reply with quote
I need to compare two files whose keys are of different length.

File 1: Key : First 12 chars. FB File

Code:

----+----1----+----2--
**********************
030931      9999-12-31
10634       9999-12-31
21912       9999-12-31
85675       9999-12-31
999128      9999-12-31
999835      9999-12-31


File 2 : Key : First 15 chars. VB File
Code:

----+----1----+----2----+----3
******************************
          10634      100010634
          21912      100021912
          85675      100085675
         999128     1000999128


Output: FB File
10634 100010634 9999-12-31
21912 100021912 9999-12-31
85675 100085675 9999-12-31
999128 1000999128 9999-12-31


Code i tried :
Code:

JOINKEYS FILES=F1,FIELDS=(1,12,A),
JOINKEYS FILES=F2,FIELDS=(4,15,A),
REFORMAT FIELDS=(F2:4,30,F1:13,10)
OPTION COPY                       





Error :

Code:

SYSIN :                                                                     
 JOINKEYS FILES=F1,FIELDS=(1,12,A),SORTED                                   
 JOINKEYS FILES=F2,FIELDS=(4,15,A),SORTED                                   
 REFORMAT FIELDS=(F2:4,30,F1:13,10)                                         
 OPTION COPY                                                                 
WER276B  SYSDIAG= 4502103, 9473810, 9473810, 5326800                         
WER164B  93,032K BYTES OF VIRTUAL STORAGE AVAILABLE, MAX REQUESTED,         
WER164B     12K BYTES RESERVE REQUESTED, 1,004K BYTES USED                   
WER146B  20K BYTES OF EMERGENCY SPACE ALLOCATED                             
WER473A  BOTH JOINKEYS STATEMENTS MUST HAVE CORRESPONDING KEY FIELDS OF EQUAL
WER211B  SYNCSMF  CALLED BY SYNCSORT; RC=0000                               
WER449I  SYNCSORT GLOBAL DSM SUBSYSTEM ACTIVE                               



Can you let me know how to rectify this?

Thanks
Madhan
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales
PostPosted: Mon Mar 14, 2011 5:19 pm
 Reply with quote
You are comparing 12 bytes against 15 bytes.
Back to top
View user's profile Send private message
MadanVS

New User


Joined: 28 Dec 2010
Posts: 25
Location: Chennai
PostPosted: Mon Mar 14, 2011 5:28 pm
 Reply with quote
Quote:
I need to compare two files whose keys are of different length.



Yes. Is it not possible to do the same?
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 792
Location: Pennsylvania
PostPosted: Mon Mar 14, 2011 5:35 pm
 Reply with quote
Quote:
Yes. Is it not possible to do the same?


Are we being punked here? Where is the camera?

When are the values EVER going to be the same?

Why not just use the first 12 bytes of the second file?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
PostPosted: Mon Mar 14, 2011 5:44 pm
 Reply with quote
MadanVS,

Code:

JOINKEYS FILES=F1,FIELDS=(1,12,A),SORTED         <<<means start at col 1 for 12 char                         
JOINKEYS FILES=F2,FIELDS=(4,15,A),SORTED         <<<means start at col 4 for 15 char


besides,

you tell us
Quote:
code tried:
Code:
JOINKEYS FILES=F1,FIELDS=(1,12,A),
JOINKEYS FILES=F2,FIELDS=(4,15,A),
REFORMAT FIELDS=(F2:4,30,F1:13,10)
OPTION COPY


yet the sysin display for syncsort has the 'sorted' parm.


you don't pay attention to detail.
Back to top
View user's profile Send private message
MadanVS

New User


Joined: 28 Dec 2010
Posts: 25
Location: Chennai
PostPosted: Mon Mar 14, 2011 5:47 pm
 Reply with quote
Quote:

Why not just use the first 12 bytes of the second file?


File 1 will have key as : -------10634
File 2 will has key as : 10634-------

Quote:
yet the sysin display for syncsort has the 'sorted' parm.


I tried different options,

Yeap both files are sorted based on keys.
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 792
Location: Pennsylvania
PostPosted: Mon Mar 14, 2011 5:54 pm
 Reply with quote
Quote:

File 1 will have key as : -------10634
File 2 will has key as : 10634-------


Those two fields do not look like a match to me.

This is going to need more 'creative' sort work than I care to provide.

Hint:
Step 1 - Use the Parse command to left justify the File 2 key into a 12 byte field.
Step 2 - use the Join keys on the two fields that ARE THE SAME LENGTH
Back to top
View user's profile Send private message
MadanVS

New User


Joined: 28 Dec 2010
Posts: 25
Location: Chennai
PostPosted: Mon Mar 14, 2011 6:46 pm
 Reply with quote
Hi daveporcelan
Quote:
Use the Parse command to left justify the File 2 key into a 12 byte field.


Is it possible to use INREC Parse along with Joinkeys?
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 792
Location: Pennsylvania
PostPosted: Mon Mar 14, 2011 6:50 pm
 Reply with quote
As far as I know, no you can not.

That is why I suggested two steps.

You have an example of the parse in your other post.

I am sure you can work this out with some effort on your part.
Back to top
View user's profile Send private message
MadanVS

New User


Joined: 28 Dec 2010
Posts: 25
Location: Chennai
PostPosted: Tue Mar 15, 2011 11:26 am
 Reply with quote
Hi
I reformatted one of my i/o file.

Now F1 is
Code:

----+----1----+----2----+----3----+----4-
***************************** Top of Data
          106349999-12-31-00.00.00.000   
          219129999-12-31-00.00.00.000   
          856759999-12-31-00.00.00.000   
         0309319999-12-31-00.00.00.000   


Key : 1,15 FB File

F2 is

Code:

----+----1----+----2----+----3-
***************************** T
          10634      100010634
          21912      100021912
          85675      100085675
         999128     1000999128


Key : 1,15 VB File


SortCard used:
Code:
JOINKEYS FILES=F1,FIELDS=(1,15,A),SORTED   
JOINKEYS FILES=F2,FIELDS=(4,15,A),SORTED   
REFORMAT FIELDS=(F2:4,30,F1:13,10)         
SORT FIELDS=COPY                           
END


Thou there are key matches I am getting empty file in my output.

Any idea?


Thanks
Madhan
Back to top
View user's profile Send private message
Thy

New User


Joined: 10 Jun 2005
Posts: 6
Location: France
PostPosted: Tue Mar 15, 2011 11:47 am
 Reply with quote
Hi,

If you want to keep only rows (keys) missing beetwen file1 and File2, you can user the UNPAIRED control statement:

JOINKEYS FILES=F1,FIELDS=(1,15,A),SORTED
JOINKEYS FILES=F2,FIELDS=(4,15,A),SORTED
JOIN UNPAIRED,ONLY, F1
END

Expected results:

----+----1----+----2--
**********************
030931 9999-12-31
999835 9999-12-31
Back to top
View user's profile Send private message
MadanVS

New User


Joined: 28 Dec 2010
Posts: 25
Location: Chennai
PostPosted: Tue Mar 15, 2011 11:52 am
 Reply with quote
Thanks for the reply Thy.

But i need only the matched records.

My output shud be as in the sortcard

JOINKEYS FILES=F1,FIELDS=(1,15,A),SORTED
JOINKEYS FILES=F2,FIELDS=(4,15,A),SORTED
REFORMAT FIELDS=(F2:4,30,F1:16,10)
SORT FIELDS=COPY
END
Back to top
View user's profile Send private message
rakesh1155

New User


Joined: 21 Jan 2009
Posts: 84
Location: India
PostPosted: Tue Mar 15, 2011 12:36 pm
 Reply with quote
Hi Madan,

If you are accounting for 4 bytes of VB file, Shouldn't the JOINKEYS for F2 start from position 5 and not 4 ??

JOINKEYS FILES=F2,FIELDS=(4,15,A),SORTED


It should be:
Code:
JOINKEYS FILES=F2,FIELDS=(5,15,A),SORTED


-Thanks,
Rakesh.
Back to top
View user's profile Send private message
Thy

New User


Joined: 10 Jun 2005
Posts: 6
Location: France
PostPosted: Tue Mar 15, 2011 12:36 pm
 Reply with quote
For VB file, you should start in pos 5 and not 4 (the 4 first pos are for RDW header).
Back to top
View user's profile Send private message
MadanVS

New User


Joined: 28 Dec 2010
Posts: 25
Location: Chennai
PostPosted: Tue Mar 15, 2011 12:43 pm
 Reply with quote
Thanks Rakesh & Thy

Got it!!
Back to top
View user's profile Send private message
MadanVS

New User


Joined: 28 Dec 2010
Posts: 25
Location: Chennai
PostPosted: Tue Mar 15, 2011 1:30 pm
 Reply with quote
By the way
I am using two steps to get the desired result:

Sort card in Step 1 :
Right justifying the KEY Part in file 1
Code:

 SORT FIELDS=COPY                                       
 OUTREC BUILD=(1:3X,4:1,12,JFY=(SHIFT=RIGHT),16:13,26)   


Sort card in step 2:
Code:
 JOINKEYS FILES=F1,FIELDS=(1,15,A) 
 JOINKEYS FILES=F2,FIELDS=(5,15,A) 
 REFORMAT FIELDS=(F2:4,30,F1:16,10)
 OPTION COPY                       


Is it possible to combine the sort cards and make it as a single step
?

Thanks
Madhan
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 -> JCL & VSAM

 

Similar Topics
Topic Forum Replies
No new posts Compare 2 files and retrive records f... DFSORT/ICETOOL 3
No new posts Compare 2 files(F1 & F2) and writ... JCL & VSAM 8
No new posts Need to set RC4 through JCL SORT DFSORT/ICETOOL 5
No new posts How to split large record length file... DFSORT/ICETOOL 10
No new posts Write line by line from two files DFSORT/ICETOOL 7
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us