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
 

 

Join on Keys

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

New User


Joined: 11 Dec 2007
Posts: 82
Location: hyderabad

PostPosted: Thu Jul 30, 2009 3:49 pm    Post subject: Join on Keys
Reply with quote

Hi Frank/Kolusu,

Can you please tell me how can i achive this with a sort.

FileA FB 80 (column names are X Y Z, Key is combination of X&Y)
Code:
X                Y               Z
1234567890123    12345        ABCDEF
1234567890123    12456        ABDDEF
1234567891123    12345        ABXDEF
1234567891123    12346        ABCDEF


FileB FB 80 (column names are M Z, Key is combination Z)
Code:
M            Z
1234A        ABCDEF
1245B        ABDDEF
1234C        ABXDEF
1234D        ABYDEF
1235D        ABZDEF


i need to comapre on Z column and get the o/p like this

Code:
X                Y               Z             M
1234567890123    12345        ABCDEF        1234A
1234567890123    12456        ABDDEF        1245B
1234567891123    12345        ABXDEF        1234C
1234567891123    12346        ABCDEF        1234A



in FILEA starting postion of Z is 21 and in FileB starting position of Z is 10 and length of Z is 6 bytes
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: Thu Jul 30, 2009 10:04 pm    Post subject:
Reply with quote

Please give the starting position and length of each field in fileA, fileB and the output file.

Do the output records need to be in the same order as the input fileA records as shown in your example, or can the output records be in the field Z order?
Back to top
View user's profile Send private message
Mazahar

New User


Joined: 11 Dec 2007
Posts: 82
Location: hyderabad

PostPosted: Thu Jul 30, 2009 10:14 pm    Post subject:
Reply with quote

Frank,

Quote:
Please give the starting position and length of each field in fileA, fileB and the output file.


FileA, X (1 to 13 bytes) Y(15 to 5 bytes) Z(21 to 6bytes)
FileB, M (1 to 5 bytes) Z(10 to 6bytes) in between spaces

Quote:
Do the output records need to be in the same order as the input fileA records as shown in your example, or can the output records be in the field Z order?

Yes, the output records need to be in the same order as the input fileA records as shown in my example
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: Thu Jul 30, 2009 10:27 pm    Post subject:
Reply with quote

You can use a DFSORT/ICETOOL job like the following to do what you asked for. I assumed you wanted field M in the output record to start in position 28.

Code:

//S1   EXEC  PGM=ICETOOL
//TOOLMSG   DD  SYSOUT=*
//DFSMSG    DD  SYSOUT=*
//IN1 DD *
1234567890123 12345 ABCDEF
1234567890123 12456 ABDDEF
1234567891123 12345 ABXDEF
1234567891123 12346 ABCDEF
/*
//IN2 DD *
1234A    ABCDEF
1245B    ABDDEF
1234C    ABXDEF
1234D    ABYDEF
1235D    ABZDEF
/*
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//T2 DD DSN=&&T2,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//OUT DD SYSOUT=*
//TOOLIN DD *
COPY FROM(IN2) TO(T1) USING(CTL1)
COPY FROM(IN1) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(T2) ON(21,6,CH) WITHALL -
  WITH(1,19) WITH(81,8)
SORT FROM(T2) TO(OUT) USING(CTL3)
/*
//CTL1CNTL DD *
  INREC BUILD=(21:10,6,28:1,5,81:8X)
/*
//CTL2CNTL DD *
  INREC OVERLAY=(81:SEQNUM,8,ZD)
/*
//CTL3CNTL DD *
  SORT FIELDS=(81,8,ZD,A)
  OUTREC BUILD=(1,80)
/*
Back to top
View user's profile Send private message
Mazahar

New User


Joined: 11 Dec 2007
Posts: 82
Location: hyderabad

PostPosted: Wed Aug 05, 2009 5:16 pm    Post subject:
Reply with quote

Frank,

Thank you so much.

I hope i missed one point to mention, there might be records in both the files which are not having matching's.

The above solution provided is working fine, except if records are not having matchings, its writing those records also.

FileA FB 80 (column names are X Y Z, Key is combination of X&Y)
Code:
X                Y               Z
1234567890123    12345        ABCDEF
1234567890123    12456        ABDDEF
1234567891123    12345        ABXDEF
1234567891123    12346        ABCDEF
1234567891123    12347        ABCDEG


FileB FB 80 (column names are M Z, Key is combination Z)
Code:
M            Z
1234A        ABCDEF
1245B        ABDDEF
1234C        ABXDEF
1234D        ABYDEF
1235D        ABZDEF
1236A        XXXXX
1237B        YYYYYY


output is coming like this
Code:

X                Y               Z             M
1234567890123    12345        ABCDEF        1234A
1234567890123    12456        ABDDEF        1245B
1234567891123    12345        ABXDEF        1234C
1234567891123    12346        ABCDEF        1234A
1234567891123    12347        ABCDEG
                              XXXXX         1236A
                              YYYYYY        1237B


Can you please suggest me.
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: Wed Aug 05, 2009 9:10 pm    Post subject:
Reply with quote

Quote:
The above solution provided is working fine, except if records are not having matchings, its writing those records also.


I ran my job with the new input you supplied:

Code:

//IN1 DD *                         
1234567890123 12345 ABCDEF         
1234567890123 12456 ABDDEF         
1234567891123 12345 ABXDEF         
1234567891123 12346 ABCDEF         
1234567891123 12347 ABCDEG         
//IN2 DD *                         
1234A    ABCDEF                   
1245B    ABDDEF                   
1234C    ABXDEF                   
1234D    ABYDEF                   
1235D    ABZDEF                   
1236A    XXXXX                     
1237B    YYYYYY                   


and got this output:

Code:

1234567890123 12345 ABCDEF 1234A   
1234567890123 12456 ABDDEF 1245B   
1234567891123 12345 ABXDEF 1234C   
1234567891123 12346 ABCDEF 1234A   


You would NOT get the records that don't have a match with my job because we didn't use KEEPNODUPS.

If you're not getting the output I'm getting then either your input records do not have the layout you said they had, or you changed my job in some way.
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 Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
No new posts How can we have the varying lenth of ... Gunapala CN DFSORT/ICETOOL 6 Fri Oct 14, 2016 7:31 pm
No new posts Joinkeys with duplicated keys juares castro SYNCSORT 19 Fri Sep 23, 2016 5:58 am
No new posts Join key - Populate Zeros when Unpair... rexx77 DFSORT/ICETOOL 6 Thu May 12, 2016 12:22 am
No new posts Too many edit macro's and too few PF ... don.leahy TSO/ISPF 7 Wed Feb 17, 2016 3:00 am


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