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 Join the fields from two differenent files.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
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    Post subject: How to Join the fields from two differenent files.
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 Moderator


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

PostPosted: Thu Apr 30, 2009 9:19 pm    Post subject:
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    Post subject: Reply to: How to Join the fields from two differenent files.
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 Moderator


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

PostPosted: Wed May 13, 2009 12:56 am    Post subject:
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    Post subject:
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    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 SORT BUT RETAIN HIGHEST VALUE ON NON-... leondan22 DFSORT/ICETOOL 2 Thu Dec 14, 2017 8:13 pm
No new posts Merging 2 records at multiple rows wi... Bijesh DFSORT/ICETOOL 2 Wed Dec 06, 2017 1:50 am
No new posts Compare VSAM files using DFSORT pshongal DFSORT/ICETOOL 5 Thu Nov 23, 2017 10:24 am
No new posts Split files upto certain limit based ... bubbu75 DFSORT/ICETOOL 5 Tue Nov 14, 2017 11:11 pm
No new posts Copy selected columns from 2 input fi... Poha Eater DFSORT/ICETOOL 3 Thu Nov 02, 2017 3:43 pm

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