Rearranging records from multiple files into one



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

Rearranging records from multiple files into one

Postby Aki88 » Thu Oct 08, 2015 1:59 pm

Hello,

We have 'n' PS files DD concatenated, each file has a header record, 'm' number of details records and one tail record.
The final output required is - one head record, all the 'm' details records from all 'n' files sorted on their key, and one tail record with the record count.

This can be easily achieved using multiple SORT steps to split and the merge the files again; the point I am stuck at is when I am trying to build the final output in one single DFSORT step.
What I am able to achieve in one go is:
--> SORT on the key
--> Build the detail records sorted on the key minus the head and tail records using OMIT
--> Build the trailer record with the total record count

Point where I am stuck is building a 'single' header record with the date picked from the input file (all the input files will have same date generated on any given day, so header can be picked from any 1 input file).

Sample data (this is how all the concatenated files sorted on their key would look like); here 'X' records are header records - date inserted is generated by an application program, 'Z' are the details records, '9's are the tail records - 7th byte onwards is the record count of each 'n' file generated by the application program.

Any pointers are much appreciated.

Thanks.

XXX000000DD/MM/YYYY
XXX000000DD/MM/YYYY
XXX000000DD/MM/YYYY
XXX000000DD/MM/YYYY
XXX000000DD/MM/YYYY
ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
999999000000080
999999000000080
999999000000080
999999000000080
999999000000080


Desired output:

XXX000000DD/MM/YYYY
ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
999999000000320
Aki88
 
Posts: 371
Joined: Tue Jan 28, 2014 1:52 pm
Has thanked: 33 times
Been thanked: 35 times

Re: Rearranging records from multiple files into one

 

Re: Rearranging records from multiple files into one

Postby BillyBoyo » Thu Oct 08, 2015 2:22 pm

Don't use INCLUDE/OMIT COND= on the headers (just the trailers).

In INREC, use IFTHEN=(WHEN=(logicalexpression) to put a SEQNUM in an extension, the logialexpression to be for the headers.

Use OUTFIL with INCLUDE= to keep only the first header (extended SEQNUM of 1), and the data.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: Rearranging records from multiple files into one

Postby Aki88 » Thu Oct 08, 2015 2:38 pm

Hello Billy,

My bad, I lost you there at the INREC :(
Below is my current card, relatively a very simple solution:

SORT FIELDS=(1,19,CH,A)                                   
OUTFIL FILES=TEST,REMOVECC,                                 
       OMIT=((1,3,CH,EQ,C'XXX'),OR,(1,3,CH,EQ,C'999')),   
       TRAILER1=(1:C'999999',7:COUNT=(M11,LENGTH=9),16:42X)


This is giving me the below o/p:

ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
999999000000320


Header needs to be added to this; can you kindly explain the solution again; my apologies, having a rough day, even simple solutions are looking Greek and Latin today. :(

Thank you.
Aki88
 
Posts: 371
Joined: Tue Jan 28, 2014 1:52 pm
Has thanked: 33 times
Been thanked: 35 times

Re: Rearranging records from multiple files into one

Postby Aki88 » Thu Oct 08, 2015 3:24 pm

Thanks a ton Billy!

A quick break and clarity prevails :)

Final solution:

SORT FIELDS=(1,19,CH,A)                                       
INREC OVERLAY=(58:SEQNUM,9,ZD)                               
OUTFIL FILES=TEST,REMOVECC,                                     
       OMIT=(((1,3,CH,EQ,C'XXX'),AND,(58,9,ZD,NE,1)),OR,     
              (1,3,CH,EQ,C'999')),                           
       TRAILER1=(1:C'999999',7:COUNT-1=(M11,LENGTH=9),16:42X)


Thanks.
Aki88
 
Posts: 371
Joined: Tue Jan 28, 2014 1:52 pm
Has thanked: 33 times
Been thanked: 35 times

Re: Rearranging records from multiple files into one

Postby BillyBoyo » Thu Oct 08, 2015 3:36 pm

When you're getting nowhere, a break is always a good idea :-)

You don't need the trailers from the files at all, as you generate one later:

  OMIT COND=(1,3,CH,EQ,C'999')


Saves SORTing a few records and simplifies your OMIT= on OUTFIL.

Nine digits for your sequence number is unrealistic, there's a limit to the number of DD statements in a step. Even if you're going crazy with datasets, five is the most you'll need.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: Rearranging records from multiple files into one

Postby Aki88 » Thu Oct 08, 2015 3:48 pm

Aah, yes; OMIT, tweaked that one and the SEQNUM; thanks again Billy.

Regards.
Aki88
 
Posts: 371
Joined: Tue Jan 28, 2014 1:52 pm
Has thanked: 33 times
Been thanked: 35 times


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post