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
 

 

Merging two files based on common field

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

New User


Joined: 11 Sep 2007
Posts: 12
Location: India

PostPosted: Thu Oct 04, 2007 3:57 pm    Post subject: Merging two files based on common field
Reply with quote

Hi,

I have 2 files and i want to merge the data based on a common key of the 2 files.

File a

000123 ABCD
000123 EFGH
000456 ABCD

FILEB

000123 XYZ
000456 XYZ
000456 UVW

O/P

000123 ABCD XYZ
000123 EFGH
000456 ABCD XYZ
00456 UVW

IS there a way in which i can get this done through ICETOOL or some other Utility.
Back to top
View user's profile Send private message

maruniral

New User


Joined: 11 Sep 2007
Posts: 12
Location: India

PostPosted: Thu Oct 04, 2007 4:09 pm    Post subject:
Reply with quote

Is there any utillity which would help me achieve this ??
Back to top
View user's profile Send private message
ParagChouguley

Active User


Joined: 03 Feb 2007
Posts: 175
Location: PUNE(INDIA)

PostPosted: Thu Oct 04, 2007 4:19 pm    Post subject:
Reply with quote

maruniral,

The number of your sample records are too less to understand your requirement. Please give more sample records. Along with that, if you could write what exactly you want, you requirement will be understood better.

--Parag
Back to top
View user's profile Send private message
maruniral

New User


Joined: 11 Sep 2007
Posts: 12
Location: India

PostPosted: Thu Oct 04, 2007 4:38 pm    Post subject: Reply to: Merging two files based on common field
Reply with quote

Hi,

Basically its a Many to Many relationship where i want to merge the 2 files based on a particular key. Also the o/p should contain all records from FILE A and any record match which is found from FILE B. The below example will help you understand.

File structure is

FILE A

Key (1-3 pos)
111 ABCD
111 EFGH
222 ABCD
333 PQRS
444 UVWX


FILE B
Key (1-3 Pos)

111 XYZ
222 PQR
222 STV
555 ABC


O/P
111 ABCD XYZ
111 EFGH
222 ABCD PQR
222 STV
333 PQRS
444 UVWX


Hope this is helpfull in understandting the query,
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 Oct 04, 2007 8:15 pm    Post subject:
Reply with quote

maruniral,

You still haven't made it clear what you're trying to do. You say it's a many-to-many relationship but you haven't explained that relationship. For example, FileA has:

111 ABCD
111 EFGH

and FileB has:

111 XYZ

and you show the output as:

111 ABCD XYZ
111 EFGH

So you're doing a join for the 111 ABCD record with the 111 XYZ record, but not doing a join for the 111 EFGH record. You need to explain the rules you're using to get that output from that input. Likewise, you need to explain the rules for the 222 keys.

Until you explain the rules completely, nobody can tell you whether or not (let alone how) it can be done.
Back to top
View user's profile Send private message
maruniral

New User


Joined: 11 Sep 2007
Posts: 12
Location: India

PostPosted: Fri Oct 05, 2007 10:34 am    Post subject: Reply to: Merging two files based on common field
Reply with quote

Frank Yaeger,

Rule is that i need all the records from FILEA. Also if there is a key match between FILEA and FILEB then i need all matched records from FILEB also but they should be merged with subsequent records from FILEA. So in case there are only 2 records from FILEA and there are 3 records from FILEB whose keys are same then the first 2 records from FILEB should be merged with the 2 records from FILEA and the 3rd record should come as it is.

Please let me know if you need more details.
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: Sat Oct 06, 2007 1:28 am    Post subject:
Reply with quote

I think this DFSORT/ICETOOL job will do what you want. I assumed your input files have RECFM=FB and LRECL=80, but the job can be changed appropriately for other attributes.

Code:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//IN1 DD *
111        ABCD
111        EFGH
222        ABCD
333        PQRS
444        UVWX
//IN2 DD *
111                       XYZ
222                       PQR
222                       STV
555                       ABC
/*
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD DSN=...   output file (FB/80)
//TOOLIN   DD    *
COPY FROM(IN2) TO(T1) USING(CTL1)
COPY FROM(IN1) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT) ON(1,3,CH) KEEPNODUPS -
  WITHALL WITH(12,4) WITH(82,9) USING(CTL3)
/*
//CTL1CNTL DD *
  INREC OVERLAY=(81:C'BB',
    83:SEQNUM,8,ZD,START=0,INCR=2,RESTART=(1,3))
/*
//CTL2CNTL DD *
  INREC OVERLAY=(81:C'VV',
    83:SEQNUM,8,ZD,START=1,INCR=2,RESTART=(1,3))
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=OUT,OMIT=(81,2,CH,EQ,C'BB',AND,83,8,ZD,EQ,+0),
    IFOUTLEN=80,
    IFTHEN=(WHEN=(81,2,CH,EQ,C'BV',AND,83,8,ZD,NE,+1),
      OVERLAY=(27:3X))
/*


OUT would have:

Code:

111        ABCD           XYZ   
111        EFGH                 
222                       PQR   
222        ABCD           PQR   
333        PQRS                 
444        UVWX                 
Back to top
View user's profile Send private message
maruniral

New User


Joined: 11 Sep 2007
Posts: 12
Location: India

PostPosted: Mon Oct 08, 2007 4:19 pm    Post subject: Reply to: Merging two files based on common field
Reply with quote

Thanks a Lot Frank.
One more issue that i have is that FILEB can have multiple records with the same key but would not have any match with any Key in FILEA in which case i dont want those in the o/p.

Considering the same example
If FILEB has 2 records as 555 in the key field then i think that would come in the o/p.

Is there a way in which we can avoid this?
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: Tue Oct 09, 2007 12:55 am    Post subject:
Reply with quote

You didn't mention that requirement previously so I didn't set up the job to handle it. Now I'm wondering what else you haven't mentioned or if I really understand what you want. So to make things clearer, please tell me what your expected output records would be for these input records. (If I missed any variations, feel free to add them.)

Input file1

Code:

//IN1 DD *                   
111        ABCD               
111        EFGH               
222        ABCD               
333        PQRS               
444        UVWX               
999        ABCD               
999        EFGH               
999        QRST               
AAA        ABCD               
AAA        CDEF               
BBB        ABCD               
BBB        CDEF               
BBB        GHIJ   
CCC        CDEF               
CCC        GHIJ       
DDD        CDEF               
DDD        GHIJ         


Input file2

Code:

111                       XYZ           
222                       PQR           
222                       STV           
555                       ABC           
555                       CDE           
666                       ABC           
666                       CDE           
666                       GHI           
888                       ABC           
999                       RST           
999                       UVW     
CCC                       RST           
CCC                       UVW           
DDD                       RST           
DDD                       UVW           
DDD                       XYZ           


If I missed any variations, feel free to add them.
Back to top
View user's profile Send private message
maruniral

New User


Joined: 11 Sep 2007
Posts: 12
Location: India

PostPosted: Tue Oct 09, 2007 10:57 am    Post subject: Reply to: Merging two files based on common field
Reply with quote

I am sorry for not being able to explain my requirement in a clear manner.

O/p expected would be

Code:

111        ABCD           XYZ
111        EFGH
222        ABCD           PQR
222                       STV
333        PQRS
444        UVWX
999        ABCD           RST
999        EFGH           UVW
999        QRST
AAA        ABCD
AAA        CDEF
BBB        ABCD
BBB        CDEF
BBB        GHIJ
CCC        CDEF           RST
CCC        GHIJ           UVW
DDD        CDEF           RST
DDD        GHIJ           UVW
DDD                       XYZ
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: Tue Oct 09, 2007 11:31 pm    Post subject:
Reply with quote

This was a rather unusual and tricky one, but I believe the following DFSORT/ICETOOL job will do what you asked for:

Code:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//IN1 DD DSN=...  input file1 (FB/80)
//IN2 DD DSN=...  input file2 (FB/80)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//T2 DD DSN=&&T2,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD DSN=... output file (FB/80)
//TOOLIN   DD    *
SELECT FROM(IN1) TO(T1) ON(1,3,CH) FIRST USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
COPY FROM(IN1) TO(T2) USING(CTL3)
SPLICE FROM(T1) TO(T2) ON(1,3,CH) KEEPNODUPS -
  WITHALL WITH(1,81) USING(CTL4)
SPLICE FROM(T2) TO(OUT) ON(1,3,CH) ON(81,8,ZD) KEEPNODUPS -
  WITH(27,3) USING(CTL5)
/*
//CTL1CNTL DD *
  INREC OVERLAY=(81:C'BB')
/*
//CTL2CNTL DD *
  INREC OVERLAY=(81:C'VV')
/*
//CTL3CNTL DD *
  INREC OVERLAY=(81:SEQNUM,8,ZD,RESTART=(1,3))
/*
//CTL4CNTL DD *
  OUTFIL FNAMES=T2,INCLUDE=(81,2,CH,EQ,C'VB'),
    OVERLAY=(81:SEQNUM,8,ZD,RESTART=(1,3))
/*
//CTL5CNTL DD *
  OUTFIL FNAMES=OUT,BUILD=(1,80)
/*
Back to top
View user's profile Send private message
maruniral

New User


Joined: 11 Sep 2007
Posts: 12
Location: India

PostPosted: Wed Oct 10, 2007 4:21 pm    Post subject: Reply to: Merging two files based on common field
Reply with quote

Its working. Thanks a lot Frank!!
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 getting three output files out of JOI... migusd SYNCSORT 7 Mon Jun 05, 2017 11:30 pm
No new posts Unable to create multiple files using... mbattu COBOL Programming 3 Fri May 05, 2017 5:35 pm
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm
No new posts READ A PACKED "NEGATIVE" FI... jdesouza CA Products 3 Tue May 02, 2017 11:43 pm
No new posts Group Data based on a key Arun Raj DFSORT/ICETOOL 7 Thu Apr 27, 2017 11:29 pm


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