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

How to Join the fields from two differenent files.
IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
Imrankhan

New User


Joined: 28 May 2007
Posts: 6
Location: Chennai
PostPosted: Thu Apr 30, 2009 6:16 pm
 Reply with quote
Hi,
I want to Join the fields from two differenent files based on the Key(Key can be duplicate) using ICETOOL.
Important note: Consider the FileA contains unique key and FileB contains Dupilcates.
Please find below for the scenario & expected output,
FileA
------
key1|Field1
AAA|001
BBB|002

FileB
-----
Key2|Field2
AAA|100
AAA|200
AAA|300
BBB|100
BBB|200

Output should like
Key|Field2|Field1
AAA|100|001
AAA|200|001
AAA|300|001
BBB|100|002
BBB|200|002

Can anyone help me how to do it using ICETOOL??
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 Apr 30, 2009 9:19 pm
 Reply with quote
You can use a DFSORT job like the following to do what you asked for. I assumed your input files have RECFM=FB and LRECL=80.

Code:

//S1    EXEC  PGM=SORT
//SYSOUT    DD  SYSOUT=*
//SORTIN DD *
AAA|001
BBB|002
/*
//       DD *
AAA|100
AAA|200
AAA|300
BBB|100
BBB|200
/*
//SORTOUT DD SYSOUT=*
//SYSIN    DD    *
  OPTION EQUALS
  SORT FIELDS=(1,3,CH,A)
  OUTREC IFTHEN=(WHEN=INIT,OVERLAY=(81:SEQNUM,8,ZD,RESTART=(1,3))),
    IFTHEN=(WHEN=GROUP,BEGIN=(81,8,ZD,EQ,1),PUSH=(8:4,4))
  OUTFIL OMIT=(81,8,ZD,EQ,1),BUILD=(1,80)
/*
Back to top
View user's profile Send private message
Imrankhan

New User


Joined: 28 May 2007
Posts: 6
Location: Chennai
PostPosted: Wed May 13, 2009 12:49 am
 Reply with quote
Hi Frank,
I cannot use the code specified by you. PUSH is giving me the syntax error. I think PUSH option will work fine only in updated DFSORT utility.
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 May 13, 2009 12:56 am
 Reply with quote
PUSH has been available with DFSORT since July, 2008 via z/OS DFSORT V1R5 PTF UK90013. Ask your System Programmer to install that PTF (it's free).
Back to top
View user's profile Send private message
nelson.pandian

Active User


Joined: 09 Apr 2008
Posts: 133
Location: Phoenix, AZ
PostPosted: Wed May 13, 2009 3:07 pm
 Reply with quote
Hi Imran,

The following DFSORT job using SPLICE will give you decire output.
Code:
//STEP01   EXEC PGM=ICETOOL                                       
//*                                                               
//TOOLMSG      DD  SYSOUT=*                                       
//DFSMSG       DD  SYSOUT=*                                       
//*                                                               
//I1       DD  *                                                 
AAA|001                                                           
BBB|002                                                           
/*                                                               
//I2       DD  *                                                 
AAA|100                                                           
AAA|200                                                           
AAA|300                                                           
BBB|100                                                           
BBB|200                                                           
/*                                                               
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(TRK,(5,5)),DISP=(MOD,PASS)     
//O1       DD  SYSOUT=*                                           
//TOOLIN   DD  *    *** CONSTANT CONTROL CARDS ***               
  COPY FROM(I1) TO(T1) USING(CTL1)                               
  COPY FROM(I2) TO(T1) USING(CTL2)                       
  SPLICE FROM(T1) TO(O1) ON(1,3,CH) WITHALL WITH(1,81) USING(CTL3)                       
/*                                                       
//CTL1CNTL DD *                                           
  INREC OVERLAY=(81:C'11',83:5,3)                         
/*                                                       
//CTL2CNTL DD *                                           
  INREC OVERLAY=(81:C'22')                               
/*                                                       
//CTL3CNTL DD *                                           
  OUTFIL FNAMES=O1,INCLUDE=(81,2,CH,EQ,C'21'),           
         BUILD=(1,7,8:C'|',9:83,3,68X)                       
/*                                                       
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 Compare 2 files(F1 & F2) and writ... JCL & VSAM 8
No new posts Write line by line from two files DFSORT/ICETOOL 7
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Merge two VSAM KSDS files into third ... JCL & VSAM 6
No new posts Joinkeys - 5 output files DFSORT/ICETOOL 7
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