Merge two files and sum up the count in a single trailer



Support for NetApp SyncSort for z/OS, Visual SyncSort, SYNCINIT, SYNCLIST and SYNCTOOL

Merge two files and sum up the count in a single trailer

Postby gauravnnl » Tue Jan 13, 2015 5:08 pm

Hi All,

I am working on a SORT card and have come to a point where I would need some advise to proceed further. Also I did some searching within the forum but cannot solution that fit in my requirement.

My requirement :
I have two flat files with LRECEL = 96 and FB. Both files having same layout and having one header/trailer record. I want to sort both the files but in the output i need one header and one trailer.
Header on both files are same so can be taken one and skip one.But in the output trailer I want to sum up count value(Position is 18 and of length 7) and amount value(Position is 25 and of length 13) from both the trailers.

Below is sample input file.

***************************** Top of Data ******************************
129998888885     96030800101810999                                     
549998888885     455206140345159010181000000008031P4552999 0890989749100
549998888885     455206140345216810181000000014000P4552999 0890989749100
549998888885     455206140345293710181000000006000P4552999 0890989749100
549998888885     455206140345348910181000000029849P4552999 0890989749100
549998888885     455206140345764710181000000002500P4552999 0890989749100
549998888885     455206140345874410181000000005000P4552999 0890989749100
549998888885     455206140346237310181000000013471P4552999 0890989749100
549998888885     455206140346470010181000000010000P4552999 0890989749100
549998888885     455206140348372610181000000020000P4552999 0890989749100
549998888885     455206140348417910181000000002000P4552999 0890989749100
549998888885     455206140348469010181000000029156P4552999 0890989749100
549998888885     455206140349201610181000000006000P4552999 0890989749100
549998888885     455206140349810410181000000002000P4552999 0890989749100
549998888885     455206140349813810181000000000100P4552999 0890989749100
549998888885     455206140350572610181000000080681P4552999 0890989749100
929998888885     000001E000000022878Q                                   


Trailer copybook structure is
01  W1242-TRAILER-RECORD REDEFINES W1242-RECORD.
    05  FILLER                      PIC X(17).   
    05  W1242-TOTAL-COUNT           PIC S9(7).   
    05  W1242-TOTAL-AMOUNT          PIC S9(13). 
    05  FILLER                      PIC X(59).   


Header starts from 1
Detail Record starts from 5
Trailer starts from 9
Actual trailer count is 15 and Amount is -228788 in above sample file.
gauravnnl
 
Posts: 21
Joined: Tue Jul 15, 2014 2:25 pm
Has thanked: 3 times
Been thanked: 0 time

Re: Merge two files and sum up the count in a single trailer

Postby BillyBoyo » Tue Jan 13, 2015 5:58 pm

There are several ways to do it. There's TRLUPD, where you need to retain one header and update it. Or, if the information on the trailer is otherwise fixed, you can OMIT COND= the trailers and create a new one with TRAILER1. For consistency, doing it the way closest to how you've done the header is probably best, so if you can show that, there may be some more accurate suggestions.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: Merge two files and sum up the count in a single trailer

Postby gauravnnl » Tue Jan 13, 2015 6:58 pm

Thanks Billy...

As both the headers are same so I was thinking to keep one and omit other. Same way planning to update any one of the trailer by summing up count and amount field. As both these fields are signed according to copybook structure so wondering if possible to achieve the result in same format. Could you please provide any sample sortcard so that I can think further on. Many thanks...
gauravnnl
 
Posts: 21
Joined: Tue Jul 15, 2014 2:25 pm
Has thanked: 3 times
Been thanked: 0 time

Re: Merge two files and sum up the count in a single trailer

Postby BillyBoyo » Tue Jan 13, 2015 7:53 pm

If you specify ZDC for the COUNT and TOT/TOTAL fields, you should get the signed values.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: Merge two files and sum up the count in a single trailer

Postby gauravnnl » Wed Jan 14, 2015 1:08 pm

Hi Billy,

Below is my Sortcard..
//SYSIN    DD *                                         
   INREC IFTHEN=(WHEN=INIT,                             
                  OVERLAY=(98:SEQNUM,4,ZD,20C'0')),     
          IFTHEN=(WHEN=GROUP,BEGIN=(1,1,CH,EQ,C'1'),   
                               END=(1,1,CH,EQ,C'9'),   
                  PUSH=(97:ID=1)),                     
          IFTHEN=(WHEN=(1,1,CH,EQ,C'9'),               
                  OVERLAY=(98:4C'9',18,20))             
    SORT FIELDS=(98,4,CH,A)                             
    SUM FIELDS=(102,7,ZD,109,13,ZD)                     
                                                       
    OUTFIL IFOUTLEN=96,                                 
          OMIT=(1,1,CH,EQ,C'1',AND,97,1,ZD,NE,1),       
          IFTHEN=(WHEN=(1,1,CH,EQ,C'9'),               
            OVERLAY=(18:102,20))                       
/*                                                     


Trailer of my first input file is
929998888885     000001E000000022878Q   


Trailer of my 2nd input file is
929998888885     000001B000000022878Q   


Trailer of output file is below
929998888885     00000270000000457576   


Rest output is fine. I have used ZD still the output is not in signed byte. Could you please check the above card. Thanks for your time !!!
gauravnnl
 
Posts: 21
Joined: Tue Jul 15, 2014 2:25 pm
Has thanked: 3 times
Been thanked: 0 time

Re: Merge two files and sum up the count in a single trailer

Postby NicC » Wed Jan 14, 2015 3:25 pm

Billy said ZDC ot ZD. Did you try that?
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Re: Merge two files and sum up the count in a single trailer

Postby gauravnnl » Wed Jan 14, 2015 4:21 pm

Yes i tried that... Its giving error. Below is the error. I used ZDC in Sum fields.

SUM FIELDS=(102,7,ZDC,109,13,ZDC)
£
INVALID OR MISSING FIELD FORMAT - REASON CODE IS 01

If anyone can let me know how this can be used correctly to get desired output please...
gauravnnl
 
Posts: 21
Joined: Tue Jul 15, 2014 2:25 pm
Has thanked: 3 times
Been thanked: 0 time

Re: Merge two files and sum up the count in a single trailer

Postby BillyBoyo » Wed Jan 14, 2015 6:12 pm

If you are using SUM, you need to look at the setting of ZDPRINT. Consult the manual, and it will explain. It is an option which controls how a positive zoned-decimal field is presented after summing.

Is your data already in order? If yes, then it would seem you are SORTing just to allow the use of SUM. You may want to consider MERGE instead. Saves SORTing, and you can use SUM. Or abandon the SUM and use the OUTFIL reporting functions, including COUNT and TOT/TOTAL on TRAILER1, or consider TRLUPD even. It is here that ZDC would give you benefit. It is not allowed in SUM.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: Merge two files and sum up the count in a single trailer

Postby gauravnnl » Wed Jan 14, 2015 7:00 pm

Yes, u r correct...records are already in sorted order and I used this only to do trailer SUM.

I even tried TRLUPD nd TRAILER1 option, but no luck. As I am not aware from where trailer populates 2287Q(may be from some other calculation). I can use count in trailer1 but not sure how total/tot can be used in this situation.

I would be happy to share the sortcard of TRLUPD nd TRAILER1 but unfortunately left the office. Wondering if I would be able to deliver it tomorrow as already delayed :( . Would appreciate if u can help in this matter, so that I can take it further tomorrow to a solution... Many thanks.
gauravnnl
 
Posts: 21
Joined: Tue Jul 15, 2014 2:25 pm
Has thanked: 3 times
Been thanked: 0 time

Re: Merge two files and sum up the count in a single trailer

Postby BillyBoyo » Wed Jan 14, 2015 8:50 pm

OK, if you don't know where to source the total amount from, you can only use the existing figures. You should be able to note that this should be changed in the future.

Use MERGE, so have SORTIN01 and SORTIN02.

The trailers are identifiable, and there are two of them.

Use WHEN=GROUP to identify the trailer, and add a sequence number (can be one digit, as it only needs to have 1 or 2).

Have a second WHEN=GROUP on the sequence number being 1. PUSH the count and amount to an extension on the record, with RECORDS=2 so it does the same to the following (trailer) record.

Use an IFTHEN=(WHEN=(logicalexpression to identify the second header, take the extended count and amount and ADD them to the existing values on that record. Use OUTFIL OMIT= for the value of 1 (so all records unaffected by the GROUPs will have space) and you should be about there, just use BUILD to set the record to the original length/drop the extended items from the WHEN=GROUPs.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Next

Return to Syncsort/Synctool

 


  • Related topics
    Replies
    Views
    Last post