Merge the records based on Header



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

Merge the records based on Header

Postby nagarajan_v » Tue Jul 30, 2013 4:13 pm

Hi All,

I have 2 input files, both file will have person names(header) and their records under the name.I need to merge or join the records based on the same header (person names). Input file 2 is not in sorted order. But my output file should be in sorted order based on the person names. Both files are having fixed length. Can i achieve this by using DFSORT?. Can anybody help on this?.

E.g My both input files will look following

Input file 1:

Karthik
DOB Designation Current company Salary
11/11/85 BA XXX 999.99
Mohan
DOB Designation Current company Salary
12/12/85 PA YYY 888.88

Input file 2:

Mohan
DOB Designation Current company Salary
12-DEC-85 PA YYY 888.88
Karthik
DOB Designation Current company Salary
11-NOV-85 BA XXX 999.99

I am expecting following file

Output file:

Karthik
DOB Designation Current company Salary
11/11/85 BA XXX 999.99
11-NOV-85 BA XXX 999.99

Mohan
DOB Designation Current company Salary
12/12/85 PA YYY 888.88
12-DEC-85 PA YYY 888.88

Thanks,
Nagarajan V
nagarajan_v
 
Posts: 7
Joined: Tue Jul 30, 2013 3:51 pm
Has thanked: 2 times
Been thanked: 0 time

Re: Merge the records based on Header

 

Re: Merge the records based on Header

Postby BillyBoyo » Tue Jul 30, 2013 4:55 pm

Are there always three records in the groups on the input files?
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: Merge the records based on Header

Postby NicC » Tue Jul 30, 2013 4:57 pm

Hello and Welcome to the forum.
First, it would make things clearer if you used the code tags to make your data more readable. Search for 'code tags' and you will find plenty of references throughout the forum including how to use them.
Next, whoever will be providng suggested solutions will need to know for each file (both input and output) the record formats and record lengths and the start position and lengths of each key field. they would also need to know if these rows
DOB Designation Current company Salary
(note the use of the code tags there) are part of your data or not. They would also need to know if each set of records on file one will have a matching set on file 2 and also if there can be duplicates.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 2690
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisys (almost)
Has thanked: 4 times
Been thanked: 105 times

Re: Merge the records based on Header

Postby nagarajan_v » Tue Jul 30, 2013 7:41 pm

Hi BillyBoyo,

It can have more than 3 records but less than 5000 records (exclusive of header)..
nagarajan_v
 
Posts: 7
Joined: Tue Jul 30, 2013 3:51 pm
Has thanked: 2 times
Been thanked: 0 time

Re: Merge the records based on Header

Postby nagarajan_v » Tue Jul 30, 2013 8:17 pm

Hi NicC,

Sorry I may not clear in my last posts. Below is the proper requirement.

Both Input and output file is FB and 80 bytes length, but first 30 bytes only will have values, remaining area will be spaces.There is no key field in both input files and may have 100 records for one person in each file. I just want to append the corresponding records for the respective Person names in output file.

Input file 1: ( it will have KO in the 12th position of the file)

Karthik
P1087624245KO12349878112/03/12
B9868624876KO86746745131/05/11
Mohan
A9798766284KO89833468125/07/10
G6788762437KO98234573116/07/09

Input file 2:( it will have PFQ in the 12th position of the file)

Mohan
R5313413434PFQ4534356517/07/08
H6245245245PFQ9776876409/07/11
Karthik
N7876608987PFQ9678645408/05/10
B9752905598PFQ9856724503/04/11

Output file:

Karthik
P1087624245KO12349878112/03/12
B9868624876KO86746745131/05/11
N7876608987PFQ9678645408/05/10
B9752905598PFQ9856724503/04/11
Mohan
A9798766284KO89833468125/07/10
G6788762437KO98234573116/07/09
R5313413434PFQ4534356517/07/08
H6245245245PFQ9776876409/07/11

Let me know if you have more clarifications.

Thanks,
Nagarajan
nagarajan_v
 
Posts: 7
Joined: Tue Jul 30, 2013 3:51 pm
Has thanked: 2 times
Been thanked: 0 time

Re: Merge the records based on Header

Postby skolusu » Tue Jul 30, 2013 10:16 pm

nagarajan_v,

1. Concatenate both files to SORTIN
2. Using INREC IFTHEN=(WHEN=INIT overlay a constant "A" at pos 91.
3. on the same INREC add IFTHEN=(WHEN=GROUP with BEGIN=(12,2,SS,NE,C'KO,PF') and push the names (karthik, mohan ...) on to position 81 i.e Push=(81:1,10)
4. on the same INREC add IFTHEN=(WHEN=(12,2,SS,EQ,C'KO,PF') overlay the constant to "B" at pos 91.

5. Now SORT on the 11 bytes at position 81 in the ascending Sequence with EQUALS option.

6. The output of step5 will give merge the group records together but you still need to omit the duplicate header records. In order to do that Using OUTREC IFTHEN=(WHEN=(91,1,CH,EQ,C'A'), overlay position 92 with a SEQuence number of 5 bytes with RESTART=(81,10)

7. Using OUTFIL Omit the records which have a seqnum > 1 at position 92 and use BUILD to write out the fist 80 bytes.
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

These users thanked the author skolusu for the post:
nagarajan_v (Wed Jul 31, 2013 2:47 pm)
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: Merge the records based on Header

Postby nagarajan_v » Wed Jul 31, 2013 2:54 pm

Thanks Kolusu for your reply.

Since i am new to DFSORT, Could you please provide me the exact code for this requirement.

I assure you that there will not be duplicate header records (person names), Person name will be unique in each input file.

Thanks,
Nagarajan V
nagarajan_v
 
Posts: 7
Joined: Tue Jul 30, 2013 3:51 pm
Has thanked: 2 times
Been thanked: 0 time

Re: Merge the records based on Header

Postby skolusu » Wed Jul 31, 2013 10:48 pm

nagarajan_v wrote:Thanks Kolusu for your reply.

Since i am new to DFSORT, Could you please provide me the exact code for this requirement.

I assure you that there will not be duplicate header records (person names), Person name will be unique in each input file.

Thanks,
Nagarajan V


Nagarajan V,

*Sigh* I pretty much gave you the entire code and yet you want the complete job? If you're not familiar with DFSORT and DFSORT's ICETOOL, I'd suggest reading through "z/OS DFSORT: Getting Started". It's an excellent tutorial, with lots of examples, that will show you how to use DFSORT, DFSORT's ICETOOL and DFSORT Symbols. You can access it online, along with all of the other DFSORT books, from:

http://www.ibm.com/support/docview.wss? ... g3T7000080

Here is the complete code. I assumed the names (karthik , mohan ) are a maximum of 10 bytes and the names start at position 1. If you have different length and position then you need to change the job
//STEP0100 EXEC PGM=SORT                                               
//SYSOUT   DD SYSOUT=*                                                 
//SORTIN   DD *                                                         
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
KARTHIK                                                                 
P1087624245KO12349878112/03/12                                         
B9868624876KO86746745131/05/11                                         
MOHAN                                                                   
A9798766284KO89833468125/07/10                                         
G6788762437KO98234573116/07/09                                         
//         DD *                                                         
MOHAN                                                                   
R5313413434PFQ4534356517/07/08                                         
H6245245245PFQ9776876409/07/11                                         
KARTHIK                                                                 
N7876608987PFQ9678645408/05/10                                         
B9752905598PFQ9856724503/04/11                                         
//SORTOUT  DD SYSOUT=*                                                 
//SYSIN    DD *                                                         
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(91:C'A')),                           
        IFTHEN=(WHEN=GROUP,BEGIN=(12,2,SS,NE,C'KO,PF'),PUSH=(81:1,10)),
        IFTHEN=(WHEN=(12,2,SS,EQ,C'KO,PF'),OVERLAY=(91:C'B'))           
                                                                       
  SORT FIELDS=(81,11,CH,A),EQUALS                                       
  OUTREC IFTHEN=(WHEN=(91,1,CH,EQ,C'A'),                               
  OVERLAY=(92:SEQNUM,5,ZD,RESTART=(81,10)))                             
  OUTFIL BUILD=(1,80),OMIT=(92,5,ZD,GT,1)                               
//*
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

These users thanked the author skolusu for the post:
nagarajan_v (Thu Aug 01, 2013 6:49 pm)
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: Merge the records based on Header

Postby nagarajan_v » Thu Aug 01, 2013 6:48 pm

Thanks Kolusu,

Your code is working perfectly. I became a fan of DFSORT. I started reading the DFSORT getting started book. It is a fantastic manual with lot of examples. Thanks once again.
nagarajan_v
 
Posts: 7
Joined: Tue Jul 30, 2013 3:51 pm
Has thanked: 2 times
Been thanked: 0 time

Re: Merge the records based on Header

Postby skolusu » Fri Aug 02, 2013 11:07 pm

nagarajan_v wrote:Thanks Kolusu,

Your code is working perfectly. I became a fan of DFSORT. I started reading the DFSORT getting started book. It is a fantastic manual with lot of examples. Thanks once again.


Glad you got it working. I suggest you go back to my hints post and compare it against the complete job and see if you can write the code on your own based on those hints. DFSORT is powerful utility and am glad you have become a fan of it.
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


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post