IBM Mainframe Forum

by **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

by **BillyBoyo** » Tue Jul 30, 2013 4:55 pm

Are there always three records in the groups on the input files?

by **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

Select all

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.

by **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)..

by **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

by **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.

by **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

by **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

Select all

//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)
//*

by **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.

by **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.

IBM Mainframe Forum

Merge the records based on Header

Merge the records based on Header

Re: Merge the records based on Header

Re: Merge the records based on Header

Re: Merge the records based on Header

Re: Merge the records based on Header

Re: Merge the records based on Header

Re: Merge the records based on Header

Re: Merge the records based on Header

Re: Merge the records based on Header

Re: Merge the records based on Header