Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Merge multiple files with H/T and produce only one H/T pair

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
sunny_io

New User


Joined: 11 Jul 2007
Posts: 68
Location: noida

PostPosted: Wed Jan 08, 2014 8:04 pm    Post subject: Merge multiple files with H/T and produce only one H/T pair
Reply with quote

Hi All,

Can you advise if DFSORT/ICETOOL can accomplish the following.

I have 3 files each having header, Detail and trailer records.
In final output file, I need 1 file, with single header, simply combined detail records and a single updated trailer record.

Header is identical in all 3 input files. The final output should just have one of those header. header is identified by rec type = 1

Detail records: There are 2 kinds of details records identified by rec type = 2 & 3. In output file, detail records of all files should simply be copied in order in which they already exist in input file.

Each file has trailer record ( rec type = 4) with count and amount total for each type of detail record in the file(detail rec has an amount field). Final output file should only have 1 trailer which has count and amount total for each type of detail record and this data should be accumulated from individual trailers present in input files.

layout:

Code:
01 FILE-REC.
   05  FILE-REC-TYPE PIC 9(02).
         88  FILE-REC-HEADER VALUE 1.
         88  FILE-REC-DETAIL-1 VALUE 2.         
         88  FILE-REC-DETAIL-2 VALUE 3.
         88  FILE-REC-TRAILER VALUE 4.
   05  FILE-RECORD X(48).
   05  FILE-HEADER REDEFINES FILE-RECORD.
         10 FILE-HEADER-DATA X(12).
   05  FILE-DETAIL-1 REDEFINES  FILE-RECORD.
         10 FILE-DETAIL-1-NAME PIC X(06).
         10 FILE-DETAIL-1-CURR PIC X(03).
         10 FILE-DETAIL-1-AMOUNT PIC 9(3).
   05  FILE-DETAIL-2 REDEFINES  FILE-RECORD.
         10 FILE-DETAIL-2-AMOUNT PIC 9(3).
         10 FILE-DETAIL-2-CURR PIC X(03).
         10 FILE-DETAIL-2-MERCHANT PIC X(6).
   05  FILE-TRAILER REDEFINES  FILE-RECORD.
         10 FILE-TRAILER-COUNT-DET-1 PIC 9(02).
         10 FILE-TRAILER-AMOUNT-DET-1 PIC 9(4).
         10 FILE-TRAILER-COUNT-DET-2 PIC 9(02).
         10 FILE-TRAILER-AMOUNT-DET-2 PIC 9(04).


Example input data: A space is indicated by a ^

file 1:
01HEADER
02SAMUALUSD199
02JAMES^USD200
03555USDWALMART
04020399010555

FILE 2:
01HEADER
02MALINAUSD299
03100USDKROGER
02JOSHUAUSD400
03799SDWALMART
04020699020899

EXPECTED OUTPUT:

01HEADER
02SAMUALUSD199
02JAMES^USD200
03555USDWALMART
02MALINAUSD299
03100USDKROGER
02JOSHUAUSD400
03799SDWALMART
04041098031454

Thank You for your time
Sunny
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Jan 08, 2014 9:57 pm    Post subject:
Reply with quote

Hello,

What kind of info is in the header?

One way to do what you want is to discard the input header and trailer records and generate new ones for the output. This depends on what exactly is needed in the h/t . . .
Back to top
View user's profile Send private message
sunny_io

New User


Joined: 11 Jul 2007
Posts: 68
Location: noida

PostPosted: Wed Jan 08, 2014 10:45 pm    Post subject: Details of Header and trailer
Reply with quote

Hi,

Header has some static data and sequence number (picked at time of file generation ) from a Db2 table.

The purpose of header and trailer data is reconciliation at a later stage so I don't think client would be happy if I discard the H/T made during detail record creation step.

I have this requirement when my job is being run as a multi steam job as opposed to single stream. Thats when I have the H/T in each stream output which has to be combined back as if there was no streaming at all so that downstream jobs can process as before.

Appreciate your time. Do you suppose I have the options to use DFSORT/ICETOOL for this problem ?

Sunny
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7225

PostPosted: Thu Jan 09, 2014 12:00 am    Post subject: Reply to: Merge multiple files with H/T and produce only one
Reply with quote

Yes, it should be fine.

Will you have a fixed, or variable, number of files?

You can start with a simple IFTHEN=(WHEN=(logicalexpression to identify your headers, and and append/insert a SEQNUM.

On OUTFIL, have INCLUDE/OMIT= which identifies the headers and only retains those with a sequence number of one.

Perhaps you can post the SYSOUT from a SORT step, so that we can see if you have IFTRAIL available to you?
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Jan 09, 2014 12:41 am    Post subject:
Reply with quote

Hello,

It will help if you psot some actual header data for the 2 files. What you have shown has nothing in the header to preserve.

If 2 headers have different data, how do you propose to have both in the output?

The trailer appears to have values that can be calculated rather than copied from the input.

I'm sure that SORT can do what you want, but a clear understanding of "the rules" is needed.
Back to top
View user's profile Send private message
sureshpathi10

Active User


Joined: 03 May 2010
Posts: 154
Location: Kuala Lumpur

PostPosted: Thu Jan 09, 2014 8:43 am    Post subject:
Reply with quote

Here is the code that Mr. Bill suggested. I've created it based on the example input provided.

Code:

//S1       EXEC PGM=SORT 
//TOOLMSG  DD SYSOUT=*   
//SYSOUT   DD SYSOUT=*   
//DFSMSG   DD SYSOUT=*   
//SORTIN   DD *           
01HEADER                 
02SAMUALUSD199           
02JAMES^USD200           
03555USDWALMART           
04020399010555           
01HEADER                 
02MALINAUSD299           
03100USDKROGER           
02JOSHUAUSD400           
03799SDWALMART           
04020699020899           
/*                       
//SORTOUT  DD SYSOUT=*   
//SORTOUT  DD DUMMY       
//SYSIN    DD *                                   
  SORT FIELDS=COPY                                 
  INREC IFTHEN=(WHEN=(1,2,CH,EQ,C'01'),           
                OVERLAY=(20:SEQNUM,1,ZD)),         
        IFTHEN=(WHEN=(1,2,CH,EQ,C'04'),           
                OVERLAY=(20:SEQNUM,1,ZD))         
  OUTFIL OMIT=(1,2,CH,EQ,C'01',AND,20,1,ZD,GT,1,OR,
               1,2,CH,EQ,C'04',AND,20,1,ZD,EQ,1)   
/*


Output:
Code:

01HEADER           1
02SAMUALUSD199     
02JAMES USD200     
03555USDWALMART     
02MALINAUSD299     
03100USDKROGER     
02JOSHUAUSD400     
03799SDWALMART     
04020699020899     2


Please let me know if there is any changes or simplification.
Back to top
View user's profile Send private message
sureshpathi10

Active User


Joined: 03 May 2010
Posts: 154
Location: Kuala Lumpur

PostPosted: Thu Jan 09, 2014 9:00 am    Post subject:
Reply with quote

Sorry My bad, I was forget about the count in Trailer icon_confused.gif
Back to top
View user's profile Send private message
sunny_io

New User


Joined: 11 Jul 2007
Posts: 68
Location: noida

PostPosted: Thu Jan 09, 2014 11:39 am    Post subject: Quick reply
Reply with quote

Hi,

Thanks Bill Suresh and Dick, for the response. I am including relevant details below for your perusal.

Quote:
It will help if you post some actual header data for the 2 files. What you have shown has nothing in the header to preserve.
If 2 headers have different data, how do you propose to have both in the output?


Header data file 1:
Code:
01G361300900002


Header data file 2:
Code:
01G361300900002


Both headers are exactly same. The sequence numbers in both files is same as it gets flipped to next value immediately after the multistream or single stream job ends.

Layout:
Code:
 01 FILE-REC.
   05  FILE-REC-TYPE PIC 9(02).
         88  FILE-REC-HEADER VALUE 1.
         88  FILE-REC-DETAIL-1 VALUE 2.         
         88  FILE-REC-DETAIL-2 VALUE 3.
         88  FILE-REC-TRAILER VALUE 4.
   05  FILE-RECORD X(48).
   05  FILE-HEADER REDEFINES FILE-RECORD.
         10 FILE-INITIATE-SYS X(03).
         10 FILE-JULIAN-DATE X(05).
         10 FILE-SEQUENCE-NO 9(05).


Quote:
The trailer appears to have values that can be calculated rather than copied from the input.

That is true, I think since the data handled is transaction data so to catch transmission errors if at all, relying on already produced trailer is what client prefers. In another job that processes the final file as input, trailer totals are used for reconcilliation against accumulated data (count, amt) in details records thus if any totals mismatch, its assumed there was a transmission error or some streamed input data is missing and incidents are opened for support team.
If my sort basically processes the multiple trailers and produces a single trailer, I am at a better position to explain to client that we are in sycnh with their development standards.

Quote:
I'm sure that SORT can do what you want, but a clear understanding of "the rules" is needed.

Happy to hear that its possible especially the trailer count/amount update.
I am reading the manual to get familiar and get what Bill and Suresh suggested earlier.

Thanks.
Sunny
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Jan 09, 2014 7:11 pm    Post subject:
Reply with quote

Good Luck!

Someone will be here if there are questions or surprises . . . icon_cool.gif

d
Back to top
View user's profile Send private message
sunny_io

New User


Joined: 11 Jul 2007
Posts: 68
Location: noida

PostPosted: Fri Jan 10, 2014 12:15 pm    Post subject: Queries
Reply with quote

Hi All,
So Far I read that SUM shall not function along with COPY control field.

Further the requirement I have is :
1) To only copy 1 header to output which is accomplished by use of inserting sequence numbers and using OMIT option as suggested by Dick.

2) To Sum only a subset of the input records ( The trailers ). Since one has already used SORT control field to indicate COPY, Is there a way to also perform SUM operation only for trailer records in this step or a new step is necessary.

Thanks.
Sunny
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Jan 10, 2014 7:19 pm    Post subject:
Reply with quote

Hello,

Rather than a "copy" actually do a Sort.

Then what you want should be as you need it.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts High CPU consumption Job using IAM fi... aswinir JCL & VSAM 8 Thu Dec 01, 2016 8:28 pm
No new posts Match or compare two files in VB Format anatol DFSORT/ICETOOL 14 Thu Nov 03, 2016 7:41 pm
This topic is locked: you cannot edit posts or make replies. How to use 2 input files in control c... Gunapala CN DFSORT/ICETOOL 23 Thu Oct 13, 2016 3:42 pm
No new posts Adding records from two files into on... shiitiizz SYNCSORT 4 Mon Sep 19, 2016 8:41 pm
No new posts Icetool or Joinkeys example to compar... ishant chauhan DFSORT/ICETOOL 21 Sat Aug 20, 2016 2:40 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us