Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

SORT- Joinkeys - compare two files

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
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    Post subject: SORT- Joinkeys - compare two files
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: 8593
Location: Back in jolly old England

PostPosted: Mon Mar 14, 2011 5:19 pm    Post subject:
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    Post subject: Reply to: SORT- Joinkeys help needed
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: 645
Location: Pennsylvania

PostPosted: Mon Mar 14, 2011 5:35 pm    Post subject:
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    Post subject:
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    Post subject:
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: 645
Location: Pennsylvania

PostPosted: Mon Mar 14, 2011 5:54 pm    Post subject:
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    Post subject:
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: 645
Location: Pennsylvania

PostPosted: Mon Mar 14, 2011 6:50 pm    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> JCL & VSAM All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts High CPU consumption Job using IAM fi... aswinir JCL & VSAM 8 Thu Dec 01, 2016 8:28 pm
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts How to convert the VBM file to VB or... Sulabh Agrawal JCL & VSAM 4 Fri Nov 18, 2016 1:04 pm
No new posts Match or compare two files in VB Format anatol DFSORT/ICETOOL 14 Thu Nov 03, 2016 7:41 pm
No new posts Joinkeys operation when key1 and key2... Susanta DFSORT/ICETOOL 5 Wed Nov 02, 2016 5:35 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us