Two files to merge and to update trailer count

sunil.maaraka · New User Joined: 20 Apr 2011 Posts: 12 Location: India

Hi

I have a requirement like there are two files with same layout.
Each file is having header and trailer and trailer contains record count of the detail records. the requirement is i have to merge those two files and the output should contain only one header and one trailer and the trailer count should be updated(count field is 9 length is 9 bytes). pls Kindly help..

the input file layouts are as follows

Input File 1:

0000000000ROVISCU 20110306
VISRO-10000003 * TEST4900001
VISRO-10000007 * TEST5300002
9999999999000000002

Input File 2:

0000000000ROVISCU 20110306
VISRO-10017720 * TEST9600001
VISRO-10019952 * TEST8100002
VISRO-10021884 * TEST0200001
VISRO-10022244 * TEST9400057
9999999999000000004

Required output file is,

Output file:

0000000000ROVISCU 20110306
VISRO-10000003 * TEST4900001
VISRO-10000007 * TEST5300002
VISRO-10017720 * TEST9600001
VISRO-10019952 * TEST8100002
VISRO-10021884 * TEST0200001
VISRO-10022244 * TEST9400057
9999999999000000006

could u pls help..

sunil.maaraka · New User Joined: 20 Apr 2011 Posts: 12 Location: India

Hi,

Sorry i missed to mention file LRECL.

The files LRECL used is 1027.

record id for header is '0000000000' (10 bytes)
record id for trailer is '9999999999' (10bytes)

trailer count field length is 9 bytes.

Please help..

prasanth_thavva · New User Joined: 28 Jul 2005 Posts: 86 Location: Chennai

Just try below control card

//SYSIN DD *

SORT FIELDS=(1,5,CH,A)
INCLUDE COND=(1,5,CH,EQ,C'VISRO')
OUTFIL FILES=01,

HEADER1=(1:C'XXXX'),

TRAILER1=(1:C'YYYY',COUNT=(M10,LENGTH=9))
/*

XXXX , YYY in header and trailer has to be change accordingly

sunil.maaraka · New User Joined: 20 Apr 2011 Posts: 12 Location: India

Hi,

The input files are already having header and trailer. I just need to convey it from input files to output file by SORT/MERGE with no duplicates. and the trailer count should be updated which is there in triler record...

pls help...

sqlcode1 · Active Member Joined: 08 Apr 2010 Posts: 577 Location: USA

sunil.maaraka,

sunil.maaraka · New User Joined: 20 Apr 2011 Posts: 12 Location: India

Hi,

Both input files are having header and trailer. if i merge those two files i will be getting 2 headers and 2 trailer records in output. so dont want 2 headers and 2 trailers to be present in output dataset. so in output only 1 header and 1 trailer is accepted. and i would like to add the first file count which is there in first file trailer record with count in second file trailer record.

pls help...

sqlcode1 · Active Member Joined: 08 Apr 2010 Posts: 577 Location: USA

sunil.maaraka,
See if below helps...TESTED ON DFSORT...If header has current date, then you can replace HEADER1 like below..

prasanth_thavva · New User Joined: 28 Jul 2005 Posts: 86 Location: Chennai

Sql code1 code one way of acheiving result in my control card based on data record in your infile i have included only records which starts with "VISRO" , so you will be getting count as many data records available in input file.

thanks sqlcode1 for getting us the exact control card. which will taken care of Lrecl (BUILD) and some additional parameters like REMOVECC...etc.

sunil.maaraka · New User Joined: 20 Apr 2011 Posts: 12 Location: India

Hi,

I ran the sort step using the above sort card you suggested. its working fine.

But actually one requirement is missing. The header date is not TSO/ISPF current date, actually As per our process while generating the header record the business date is used to populate in a daily running program.

So, could you pls help instead of hard coding or populating with TSO/ISPF current date, is there any way to get date from omitted header record and populate it to output header.

Please help...

prasanth_thavva · New User Joined: 28 Jul 2005 Posts: 86 Location: Chennai

Hi,

If you look at sqlcode1 code , you will be observing in trailer statement

"//SYSIN DD *
OMIT COND=(01,10,CH,EQ,C'0000000000',OR,
01,10,CH,EQ,C'9999999999')
SORT FIELDS=COPY,EQUALS
OUTFIL REMOVECC,BUILD=(1,1027),
HEADER1=(C'0000000000ROVISCU 20110306'),
TRAILER1=(C'9999999999',COUNT=(M11,LENGTH=9))
/* "

Like wise ("Underlined") , Can you try in header by adding date format , google it for syncsort.

&DATE4...etc

gcicchet · Posted: Thu Jun 30, 2011 12:23 pm

Hi,

you can try this

Arun Raj · Posted: Thu Jun 30, 2011 12:42 pm

Hello Gerry,

Since we are just COPYing, do we need an EQUALS here?

sunil.maaraka · New User Joined: 20 Apr 2011 Posts: 12 Location: India

Hi Jerry,

I ran it, Its working fine, its populating date from previously omitted header.

But the count value is wrongly as the count is added for header also.

for example.

OUTPUT

0000000000ROVISCU 20110306
VISRO-10000003 * TEST4900001
VISRO-10000007 * TEST5300002
VISRO-10017720 * TEST9600001
VISRO-10019952 * TEST8100002
VISRO-10021884 * TEST0200001
VISRO-10022244 * TEST9400057
9999999999000000007

The though the detail records 6 only it is showing 7 as count in the output.

how can i less one from count in output to show.

could you help pls...

gcicchet · Posted: Thu Jun 30, 2011 1:01 pm

Hi Arun,

I just used sqlcode1's JCL and amended it, but you are right, there is no need to use EQUALS

Gerry

sunil.maaraka · New User Joined: 20 Apr 2011 Posts: 12 Location: India

Hi All,

Its working as required

. I really very happy to see this much sincere help...

Its very happy to do conversation with you all..

Thank you once again...!

have a nice day...

Arun Raj · Posted: Thu Jun 30, 2011 1:45 pm

Good that it's working fine, Can you post the final solution here?

sunil.maaraka · New User Joined: 20 Apr 2011 Posts: 12 Location: India

Hi Jerry,

Could you please give an idea about how that omit condition works in below.

OUTFIL REMOVECC,BUILD=(1,1030),OMIT=(1028,10,ZD,GT,+1,AND,
1,10,CH,EQ,C'0000000000'),

Please help...

sunil.maaraka · New User Joined: 20 Apr 2011 Posts: 12 Location: India

sorry,

OUTFIL REMOVECC,BUILD=(1,1027),OMIT=(1028,10,ZD,GT,+1,AND, 1,10,CH,EQ,C'0000000000'),

Pls...

enrico-sorichetti · Posted: Thu Jun 30, 2011 7:15 pm

here is another way of doing it ( TESTED For 80 byte records )

sqlcode1 · Active Member Joined: 08 Apr 2010 Posts: 577 Location: USA

Arun Raj · Posted: Thu Jun 30, 2011 8:20 pm

Here's another way of doing it.

gcicchet · Posted: Fri Jul 01, 2011 8:44 am

Hi,

the only thing I don't agree with some of the solutions is why do we need to sort the files if it can be avoided, especially if the files happen to be large.

Gerry

Arun Raj · Posted: Fri Jul 01, 2011 9:46 am

Hi Gerry,

I agree to your point that if the data volume is huge, it's better to avoid a SUM.

Bill Woodger · Posted: Sat Jul 02, 2011 4:05 am