View previous topic :: View next topic
|
Author |
Message |
sunny_io
New User
Joined: 11 Jul 2007 Posts: 68 Location: noida
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
|
sunny_io
New User
Joined: 11 Jul 2007 Posts: 68 Location: noida
|
|
|
|
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 |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
|
sureshpathi10
Active User
Joined: 03 May 2010 Posts: 154 Location: Kuala Lumpur
|
|
|
|
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 |
|
|
sureshpathi10
Active User
Joined: 03 May 2010 Posts: 154 Location: Kuala Lumpur
|
|
|
|
Sorry My bad, I was forget about the count in Trailer |
|
Back to top |
|
|
sunny_io
New User
Joined: 11 Jul 2007 Posts: 68 Location: noida
|
|
|
|
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:
Header data file 2:
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Good Luck!
Someone will be here if there are questions or surprises . . .
d |
|
Back to top |
|
|
sunny_io
New User
Joined: 11 Jul 2007 Posts: 68 Location: noida
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Rather than a "copy" actually do a Sort.
Then what you want should be as you need it. |
|
Back to top |
|
|
|