IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Two files to merge and to update trailer count


IBM Mainframe Forums -> JCL & VSAM
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
sunil.maaraka

New User


Joined: 20 Apr 2011
Posts: 12
Location: India

PostPosted: Wed Jun 29, 2011 2:18 pm
Reply with quote

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..
Back to top
View user's profile Send private message
sunil.maaraka

New User


Joined: 20 Apr 2011
Posts: 12
Location: India

PostPosted: Wed Jun 29, 2011 2:35 pm
Reply with quote

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..
Back to top
View user's profile Send private message
prasanth_thavva

New User


Joined: 28 Jul 2005
Posts: 86
Location: Chennai

PostPosted: Wed Jun 29, 2011 3:15 pm
Reply with quote

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
Back to top
View user's profile Send private message
sunil.maaraka

New User


Joined: 20 Apr 2011
Posts: 12
Location: India

PostPosted: Wed Jun 29, 2011 3:52 pm
Reply with quote

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...
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA

PostPosted: Wed Jun 29, 2011 7:52 pm
Reply with quote

sunil.maaraka,
Quote:
I just need to convey it from input files to output file by SORT/MERGE with no duplicates.

What is the criteria to remove duplicates? Did you mean remove duplicate "detail" records or just HEADER and TRAILER records? If detail records,please explain how details are identified as duplicates?

Thanks,
Back to top
View user's profile Send private message
sunil.maaraka

New User


Joined: 20 Apr 2011
Posts: 12
Location: India

PostPosted: Wed Jun 29, 2011 8:01 pm
Reply with quote

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...
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA

PostPosted: Wed Jun 29, 2011 8:53 pm
Reply with quote

sunil.maaraka,
See if below helps...TESTED ON DFSORT...If header has current date, then you can replace HEADER1 like below..
Code:
HEADER1=(C'0000000000ROVISCU ',DATENS=(4MD)),

Code:
//STEP0001  EXEC PGM=SORT                         
//SYSOUT    DD SYSOUT=*                           
//SYSPRINT  DD SYSOUT=*                           
//SYSUDUMP  DD SYSOUT=*                           
//SORTIN    DD *                                 
0000000000ROVISCU 20110306                       
VISRO-10000003 * TEST4900001                     
VISRO-10000007 * TEST5300002                     
9999999999000000002                               
0000000000ROVISCU 20110306                       
VISRO-10017720 * TEST9600001                     
VISRO-10019952 * TEST8100002                     
VISRO-10021884 * TEST0200001                     
VISRO-10022244 * TEST9400057                     
9999999999000000004                               
//SORTOUT  DD  SYSOUT=*                           
//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))   
/*                                               

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

Thanks,
Back to top
View user's profile Send private message
prasanth_thavva

New User


Joined: 28 Jul 2005
Posts: 86
Location: Chennai

PostPosted: Thu Jun 30, 2011 8:11 am
Reply with quote

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.

icon_biggrin.gif
Back to top
View user's profile Send private message
sunil.maaraka

New User


Joined: 20 Apr 2011
Posts: 12
Location: India

PostPosted: Thu Jun 30, 2011 11:54 am
Reply with quote

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...
Back to top
View user's profile Send private message
prasanth_thavva

New User


Joined: 28 Jul 2005
Posts: 86
Location: Chennai

PostPosted: Thu Jun 30, 2011 12:15 pm
Reply with quote

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
Back to top
View user's profile Send private message
gcicchet

Senior Member


Joined: 28 Jul 2006
Posts: 1702
Location: Australia

PostPosted: Thu Jun 30, 2011 12:23 pm
Reply with quote

Hi,

you can try this
Code:
  OMIT COND=(01,10,CH,EQ,C'9999999999')                                 
  INREC BUILD=(1,1027,SEQNUM,10,ZD)                                     
  SORT FIELDS=COPY,EQUALS                                               
  OUTFIL REMOVECC,BUILD=(1,1027),OMIT=(1028,10,ZD,GT,+1,AND,           
         1,10,CH,EQ,C'0000000000'),                                     
  TRAILER1=(C'9999999999',COUNT=(M11,LENGTH=9))                         



Gerry
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Thu Jun 30, 2011 12:42 pm
Reply with quote

Hello Gerry,

Since we are just COPYing, do we need an EQUALS here?
Back to top
View user's profile Send private message
sunil.maaraka

New User


Joined: 20 Apr 2011
Posts: 12
Location: India

PostPosted: Thu Jun 30, 2011 12:50 pm
Reply with quote

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...
Back to top
View user's profile Send private message
gcicchet

Senior Member


Joined: 28 Jul 2006
Posts: 1702
Location: Australia

PostPosted: Thu Jun 30, 2011 1:01 pm
Reply with quote

Hi Arun,

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


Gerry
Back to top
View user's profile Send private message
sunil.maaraka

New User


Joined: 20 Apr 2011
Posts: 12
Location: India

PostPosted: Thu Jun 30, 2011 1:16 pm
Reply with quote

Hi All,

Its working as required icon_biggrin.gif. 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... icon_smile.gif
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Thu Jun 30, 2011 1:45 pm
Reply with quote

Good that it's working fine, Can you post the final solution here?
Back to top
View user's profile Send private message
sunil.maaraka

New User


Joined: 20 Apr 2011
Posts: 12
Location: India

PostPosted: Thu Jun 30, 2011 2:41 pm
Reply with quote

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...
Back to top
View user's profile Send private message
sunil.maaraka

New User


Joined: 20 Apr 2011
Posts: 12
Location: India

PostPosted: Thu Jun 30, 2011 2:42 pm
Reply with quote

sorry,

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

Pls...
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10833
Location: italy

PostPosted: Thu Jun 30, 2011 7:15 pm
Reply with quote

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

Code:
 000027   OMIT   COND=(01,10,CH,EQ,C'9999999999')                               
 000028   SORT   FIELDS=(81,1,CH,A,1,14,CH,A)                           
 000029   INREC  IFTHEN=(WHEN=GROUP,BEGIN(1,10,CH,EQ,C'0000000000'),           
 000030                  PUSH=(82:ID=8)),                                       
 000031          IFTHEN=(WHEN=(1,10,CH,EQ,C'0000000000'),                       
 000032                  OVERLAY=(81:C'1')),                                   
 000033          IFTHEN=(WHEN=NONE,                                             
 000034                  OVERLAY=(81:C'2'))                                     
 000035   OUTFIL REMOVECC,BUILD=(1,80),                                         
 000036          OMIT=(01,10,CH,EQ,C'0000000000',AND,82,8,ZD,GT,1),             
 000037          TRAILER1=('9999999999',COUNT-1=(M11,LENGTH=9))                 


input
Code:
 000011 0000000000ROVISCU 20110306                                             
 000012 VISRO-20000003 * TEST4900001                                           
 000013 VISRO-20000007 * TEST5300002                                           
 000014 9999999999000000002                                                     
 000015 0000000000ROVISCU 20110306                                             
 000016 VISRO-10000003 * TEST4900001                                           
 000017 VISRO-10000007 * TEST5300002                                           
 000018 9999999999000000002                                                     
 000019 0000000000ROVISCU 20110306                                             
 000020 VISRO-10017720 * TEST9600001                                           
 000021 VISRO-10019952 * TEST8100002                                           
 000022 VISRO-10021884 * TEST0200001                                           
 000023 VISRO-10022244 * TEST9400057                                           
 000024 9999999999000000004                                                     

output

Code:
0000000000ROVISCU 20110306                                                     
VISRO-10000003 * TEST4900001                                                   
VISRO-10000007 * TEST5300002                                                   
VISRO-10017720 * TEST9600001                                                   
VISRO-10019952 * TEST8100002                                                   
VISRO-10021884 * TEST0200001                                                   
VISRO-10022244 * TEST9400057                                                   
VISRO-20000003 * TEST4900001                                                   
VISRO-20000007 * TEST5300002                                                   
9999999999000000008                                                             
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA

PostPosted: Thu Jun 30, 2011 7:19 pm
Reply with quote

Quote:
Hello Gerry,

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

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

Arun,
That was left over from my testing where I was initially sorting the records. Thanks for the correction.

Thanks,
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Thu Jun 30, 2011 8:20 pm
Reply with quote

Here's another way of doing it.
Code:
//SYSIN    DD *                                                     
  INREC IFTHEN=(WHEN=INIT,                                           
                OVERLAY=(1028:SEQNUM,8,ZD,9C'0')),                   
        IFTHEN=(WHEN=(1,10,CH,EQ,C'0000000000'),                     
                OVERLAY=(1028:17C'0')),                             
        IFTHEN=(WHEN=(1,10,CH,EQ,C'9999999999'),                     
                OVERLAY=(1028:8C'9',11,9))                           
  SORT FIELDS=(1028,8,CH,A)                                         
  SUM FIELDS=(1036,9,ZD)                                             
  OUTREC IFOUTLEN=1027,                                             
         IFTHEN=(WHEN=(1,10,CH,EQ,C'9999999999'),OVERLAY=(11:1036,9))
Back to top
View user's profile Send private message
gcicchet

Senior Member


Joined: 28 Jul 2006
Posts: 1702
Location: Australia

PostPosted: Fri Jul 01, 2011 8:44 am
Reply with quote

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
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Fri Jul 01, 2011 9:46 am
Reply with quote

Hi Gerry,

I agree to your point that if the data volume is huge, it's better to avoid a SUM.
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Sat Jul 02, 2011 4:05 am
Reply with quote

sunil.maaraka wrote:
[...]
OUTFIL REMOVECC,BUILD=(1,1027),OMIT=(1028,10,ZD,GT,+1,AND, 1,10,CH,EQ,C'0000000000'),

[...]


1028,10,ZD is the record sequence number that has been generated by the control card. If it is greater than one, and the record is a header, omit it. Thus any and all header records which are not the first record that was read will be excluded. The first record, if it is a header, will be used to create the output header.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> JCL & VSAM

 


Similar Topics
Topic Forum Replies
No new posts Merge files with a key and insert a b... DFSORT/ICETOOL 6
No new posts Count the number of characters in a f... CA Products 1
No new posts Split large FB file based on Key coun... DFSORT/ICETOOL 4
No new posts Merge 2 lines based on Space from a S... DFSORT/ICETOOL 5
No new posts COBOL program logic for comparisipn o... COBOL Programming 2
Search our Forums:

Back to Top