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
 

 

Optimal usage of Joinkeys

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
Gururajan Balaji

New User


Joined: 10 Jan 2011
Posts: 6
Location: Chennai

PostPosted: Fri Jan 21, 2011 3:59 pm    Post subject: Optimal usage of Joinkeys
Reply with quote

Hi All,
I Have to compare 2 files on (1,9 bytes) and get matching records from file2.

First file lrecl=10,recfm=fb -> Has no dups on key (May contain 1 million records)
second file lrecl=274,recfm=fb --> has dups on key (may contain 500 million records or more)
both the files are sorted on key.

I use the below sort card for comparison.

Code:

  JOINKEYS FILE=F1,FIELDS=(1,9,A),SORTED         
  JOINKEYS FILE=F2,FIELDS=(4,9,A),SORTED         
  REFORMAT FIELDS=(F2:1,274)                     
  SORT FIELDS=(4,9,CH,A) 


The problem is file2 is a very huge file (may contain 500 million records) and hence the job runs for a very long time.
Is there a way to optimize this sort? (Like allocating maximum size for buffer).
Few of my colleagues are suggesting me to go for COBOL instead of SORT for such a huge file comparison.
Could some one tell me which method could be optimal?
Back to top
View user's profile Send private message

daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 662
Location: Pennsylvania

PostPosted: Fri Jan 21, 2011 7:01 pm    Post subject:
Reply with quote

If file two is already sorted on the key, then why not use

Code:

  SORT FIELDS=COPY


to do a COPY instead of a SORT?

I would think this in itself will improve performance.

I do not think Cobol would work nearly as well.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Sat Jan 22, 2011 2:20 am    Post subject:
Reply with quote

In addition to doing a COPY instead of a SORT (as suggested by Dave), I would also try reversing the files so the file with dups and more records is used as F1 to see if that performs better:

Code:

...
//FB274 DD DSN=...  input file2 (FB/274)
//FB10 DD DSN=...  input file1 (FB/10)
...
  JOINKEYS F1=FB274,FIELDS=(4,9,A),SORTED
  JOINKEYS F2=FB10,FIELDS=(1,9,A),SORTED
  REFORMAT FIELDS=(F1:1,274)
  SORT FIELDS=COPY
Back to top
View user's profile Send private message
Gururajan Balaji

New User


Joined: 10 Jan 2011
Posts: 6
Location: Chennai

PostPosted: Mon Jan 24, 2011 2:39 pm    Post subject:
Reply with quote

Thanks Dave & Frank.
Will there be an Improvement in performance if I use PARM='CORE=MAX'?
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Tue Jan 25, 2011 12:13 am    Post subject:
Reply with quote

CORE=MAX is equivalent to MAINSIZE=MAX which is the shipped default for DFSORT so you won't see any difference unless your site has changed the shipped default. Since I don't know what your site defaults are, I can't tell you if CORE=MAX will have any effect.
Back to top
View user's profile Send private message
David Eisenberg

New User


Joined: 15 Nov 2007
Posts: 39
Location: New York

PostPosted: Tue Jan 25, 2011 8:01 pm    Post subject: Reply to: Optimal usage of Joinkeys
Reply with quote

>I would also try reversing the files so the file with dups and more records is used as F1 to see if that performs better<

Frank,

Can you tell us a little more about what prompted this suggestion? Are there generally-applicable performance considerations when choosing which file should be F1 and which should be F2 in a JOIN when one (or both) of the files is very large?

Thanks,

David
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Tue Jan 25, 2011 11:54 pm    Post subject:
Reply with quote

The only thing I can say is that using F1 for the file that has "more" duplicates may result in more efficient processing. I can't really be more specific than that as it depends on a lot of factors. The only way to know for sure is to try it both ways.
Back to top
View user's profile Send private message
David Eisenberg

New User


Joined: 15 Nov 2007
Posts: 39
Location: New York

PostPosted: Wed Jan 26, 2011 12:11 am    Post subject: Reply to: Optimal usage of Joinkeys
Reply with quote

Frank,

That's helpful; thanks.

David
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 -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Need inputs on Space requirement and... ashek15 IMS DB/DC 0 Sat Apr 01, 2017 8:26 am
No new posts Joinkeys with condition scdinesh DFSORT/ICETOOL 10 Tue Feb 14, 2017 12:20 am
No new posts Joinkeys with decimal scdinesh DFSORT/ICETOOL 3 Fri Feb 10, 2017 4:46 am
No new posts Joinkeys operation when key1 and key2... Susanta DFSORT/ICETOOL 5 Wed Nov 02, 2016 5:35 pm
No new posts Joinkeys operation betwen VB and FB file Susanta SYNCSORT 4 Sun Sep 25, 2016 9:49 pm


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