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

Join on Keys
IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic Quick References
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
 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 Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
PostPosted: Thu Jul 30, 2009 10:04 pm
 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
 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 Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
PostPosted: Thu Jul 30, 2009 10:27 pm
 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
 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 Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
PostPosted: Wed Aug 05, 2009 9:10 pm
 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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 

Similar Topics
Topic Forum Replies
No new posts Use input file with OMIT rcd keys? DFSORT/ICETOOL 15
No new posts Join multiple records using splice DFSORT/ICETOOL 5
No new posts Join 2 files according to one key field. JCL & VSAM 3
No new posts JOIN STATEMENT PERFORMANCE. DFSORT/ICETOOL 12
No new posts Join files where value in one is betw... DFSORT/ICETOOL 6
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