Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
How to use JOINKEYS

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
Vinod Y

New User


Joined: 13 May 2008
Posts: 4
Location: India

PostPosted: Mon Sep 29, 2008 12:17 am    Post subject: How to use JOINKEYS
Reply with quote

Hi,

I have two input files, File1 & File2.I want to join the matcing records to the Output file.(Input files with RECL=50 and Output file with RECL=80)
I want to compare File1 {Key1(11,6) + Key(1,7)}
with File2 {Key(1,13)} .If this matches i will join File1 and File2
Code:
JOIN UNPAIRED,F1                                     
  REFORMAT FIELDS=(F1:1,19,F2:19,20)


File1:
Code:
----+----1----+----2----+----3--
1234567 ABCDEF 1111111111
2222222 BBBBBB 4444444444


File2:
Code:
----+----1----+----2----+----3--
ABCDEF1234567 WWWWWWWWW
BBBBBB2222222 YYYYYYYYYY


Out:
Code:
----+----1----+----2----+----3----+----
1234567 ABCDEF 11111111111 WWWWWWWWW
2222222 BBBBBB 4444444444  YYYYYYYYYY
Back to top
View user's profile Send private message

Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2285
Location: @my desk

PostPosted: Mon Sep 29, 2008 9:12 am    Post subject:
Reply with quote

Vinod,
Quote:
I want to compare File1 {Key1(11,6) + Key(1,7)}
with File2 {Key(1,13)} .If this matches i will join File1 and File2

The key positions you mentioned above and that given in the "Code" does n't seem to match. Also you dont need a JOIN UNPAIRED,F1 unless you want to write unmatched records also from file1

If you need only the matching records in your output you can use a Syncsort job like this.
Code:
//STEP00   EXEC PGM=SORT                                               
//SYSOUT   DD SYSOUT=*                                                 
//SORTOUT  DD SYSOUT=*                                                 
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
//SORTJNF1 DD *                                                         
1234567 ABCDEF 1111111111                                               
2222222 BBBBBB 4444444444                                               
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
//SORTJNF2 DD *                                                         
ABCDEF1234567 WWWWWWWWW                                                 
BBBBBB2222222 YYYYYYYYY                                                 
//SYSIN DD *                                                           
 JOINKEYS FILE=F1,FIELDS=(9,6,A,1,7,A)                                 
 JOINKEYS FILE=F2,FIELDS=(1,6,A,7,7,A)                                 
 SORT FIELDS=COPY                                                       
 REFORMAT FIELDS=(F1:1,25,F2:14,9)         

SORTOUT
Code:
1234567 ABCDEF 1111111111 WWWWWWWW
2222222 BBBBBB 4444444444 YYYYYYYY
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Mon Sep 29, 2008 9:19 am    Post subject:
Reply with quote

Hi,

Off hand code,
Code:
//SYSIN    DD    *                     
  JOINKEYS FILES=F1,FIELDS=(9,6,A,1,7,A)
  JOINKEYS FILES=F2,FIELDS=(1,6,A,7,7,A)
  REFORMAT FIELDS=(F1:1,25,F2:14,9)   
  SORT FIELDS=COPY                     
/*                                     
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Mon Sep 29, 2008 9:23 am    Post subject:
Reply with quote

Agreed with Arun
Quote:
I want to compare File1 {Key1(11,6) + Key(1,7)}
with File2 {Key(1,13)} .If this matches i will join File1 and File2
It shoud be, per given example
File1 {Key1(9,6) + Key(1,7)

PS. Well, I'm a regular victim of this, didn't see your response Arun, kept open the thread for a longer time.. icon_redface.gif
Back to top
View user's profile Send private message
Vinod Y

New User


Joined: 13 May 2008
Posts: 4
Location: India

PostPosted: Tue Sep 30, 2008 2:08 am    Post subject:
Reply with quote

Thanks Arun and Anuj....i got the desired result. icon_cool.gif
Sorry for the typo error icon_biggrin.gif
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Tue Sep 30, 2008 8:08 am    Post subject:
Reply with quote

You are welcome, not to worry about the typos.. icon_smile.gif

-Ad
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 JOINKEYS Paired and Unpaired Sort on ... MFwhiz DFSORT/ICETOOL 7 Tue Nov 07, 2017 2:46 am
No new posts Weirdo results with JOINKEYS Balaryan DFSORT/ICETOOL 4 Wed Oct 18, 2017 9:04 pm
No new posts JOINKEYS Paired and Unpaired Sort MFwhiz DFSORT/ICETOOL 4 Fri Sep 29, 2017 1:48 am
No new posts getting three output files out of JOI... migusd SYNCSORT 7 Mon Jun 05, 2017 11:30 pm
No new posts Joinkeys with condition scdinesh DFSORT/ICETOOL 10 Tue Feb 14, 2017 12:20 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us