MERGE and Add a record



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

MERGE and Add a record

Postby Sushmita V » Mon Feb 20, 2012 10:45 am

Hi,

I have two files:

FILE1:

NAME|DOB|ADDRESS
GEORGE|19841010|1ST STREET
TEENIEE|19821010|3RD STREET

FILE2:

NAME|DOB|ADDRESS
TOM|19801111|FRANKLIN BLVD
JERRY|19800101|ABC BLVD
JESSICA|19880203|LONDON

I need these two files to be merged and my output should be:

NAME|DOB|ADDRESS
GEORGE|19841010|1ST STREET
TEENIEE|19821010|3RD STREET
TOM|19801111|FRANKLIN BLVD
JERRY|19800101|ABC BLVD
JESSICA|19880203|LONDON

I can use simple SORT=COPY in my sysin card but it gives me below results:

NAME|DOB|ADDRESS
NAME|DOB|ADDRESS

GEORGE|19841010|1ST STREET
TEENIEE|19821010|3RD STREET
TOM|19801111|FRANKLIN BLVD
JERRY|19800101|ABC BLVD
JESSICA|19880203|LONDON

Please suggest!

Thanks & Regards,
Sushmita
Sushmita V
 
Posts: 12
Joined: Thu Jan 19, 2012 9:26 pm
Location: Chennai, India
Has thanked: 0 time
Been thanked: 0 time

Re: MERGE and Add a record

 

Re: MERGE and Add a record

Postby BillyBoyo » Mon Feb 20, 2012 12:48 pm

SORT=COPY?

Can you show what you actually used to create that output?
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: MERGE and Add a record

Postby skolusu » Mon Feb 20, 2012 10:05 pm

Sushmita V,

Use the following DFSORT JCL which will give you the desired results. I assumed that your both input files are of the same LRECL and RECFM . ie LRECL=80 and RECFM=FB

//STEP0100 EXEC PGM=SORT                                           
//SYSOUT   DD SYSOUT=*                                             
//SORTIN   DD *                                                     
NAME|DOB|ADDRESS                                                   
GEORGE|19841010|1ST STREET                                         
TEENIEE|19821010|3RD STREET                                         
//         DD *                                                     
NAME|DOB|ADDRESS                                                   
TOM|19801111|FRANKLIN BLVD                                         
JERRY|19800101|ABC BLVD                                             
JESSICA|19880203|LONDON                                             
//SORTOUT  DD SYSOUT=*                                             
//SYSIN    DD *                                                     
  OPTION COPY                                                       
  OUTREC IFTHEN=(WHEN=(1,5,CH,EQ,C'NAME|'),OVERLAY=(81:SEQNUM,1,ZD))
  OUTFIL OMIT=(81,1,ZD,EQ,2),BUILD=(1,80)                           
//*     
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: MERGE and Add a record

Postby Nik22Dec » Tue Feb 21, 2012 7:08 pm

Hi,

What Skolusu has suggested is as usual absolutely correct. :D

IMHO, You can also try using -

Sum Fields = None


The only caveat is that the input files should not have the same/duplicate records otherwise, one of them will get ignored.
Thanks,
Nik
User avatar
Nik22Dec
 
Posts: 68
Joined: Mon Dec 26, 2011 6:38 pm
Has thanked: 2 times
Been thanked: 0 time

Re: MERGE and Add a record

Postby BillyBoyo » Tue Feb 21, 2012 7:23 pm

Two extra caveats. Syntax. OPTION COPY.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: MERGE and Add a record

Postby Frank Yaeger » Wed Feb 22, 2012 12:36 am

Nik22Dec,

As BB points out, you would need to do a SORT or a MERGE to use SUM FIELDS=NONE. That would be less efficient then Kolusu's COPY solution.
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1080
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 14 times

Re: MERGE and Add a record

Postby Nik22Dec » Wed Feb 22, 2012 2:27 pm

BillyBoyo wrote:Two extra caveats. Syntax. OPTION COPY.


I am sorry Billy but, I am not sure if I understood your post clearly. Can you please elaborate.

Frank Yaeger wrote:Nik22Dec,

As BB points out, you would need to do a SORT or a MERGE to use SUM FIELDS=NONE. That would be less efficient then Kolusu's COPY solution.


Thanks for your reply Frank. Yes, a SORT or a Merge is defintely required. Thanks for pointing out at the efficiency aspect of my solution. I wasn't aware of that.
Thanks,
Nik
User avatar
Nik22Dec
 
Posts: 68
Joined: Mon Dec 26, 2011 6:38 pm
Has thanked: 2 times
Been thanked: 0 time

Re: MERGE and Add a record

Postby BillyBoyo » Wed Feb 22, 2012 3:12 pm

Nik,

If we provide something, it is nice (read obligatory) if it gets through the syntax checking.

The SUM is not a possible solution in a COPY operation. If you SORT the files, you 1) might get other duplicates (your caveat) 2) destroy the order of the file. If you MERGE the files, the record in question is not contiguous with its twin, so would not come under the SUM anyway.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: MERGE and Add a record

Postby Nik22Dec » Mon Feb 27, 2012 11:55 am

Hi Billy,

Yes! I agree!

I wanted to give TS a simpler idea which he can use with little effort. Unfortunately, I was under the wrong impression that the order of the records wouldn't mattter & that spoiled the entire thing. :oops: Thanks for pointing that out.
Thanks,
Nik
User avatar
Nik22Dec
 
Posts: 68
Joined: Mon Dec 26, 2011 6:38 pm
Has thanked: 2 times
Been thanked: 0 time


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post