SORT FIELDS: Two passes to be changed to one



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

SORT FIELDS: Two passes to be changed to one

Postby Aki88 » Tue Jul 14, 2015 12:58 pm

Hello,

I have an input sequential file of LRECL= 700, with approx 60 million records upwards; this data is first sorted on key1, and written to file1.
In another step, this very same file is sorted again, but this time on key2, and written to file2.

Files 1 and 2 are used by two different process streams further down the line.

Is there a way, by which I can get the above two processing done by 1 pass only, i.e. get file1 and file2 to output in one go instead of from two different steps.
Basically we do not want to read the same data again top down to sort it on another key.

Sample SORT cards are a simple:

SORT 1 -
SORT FIELDS=(1,22,CH,A)

SORT 2 -
SORT FIELDS=(69,22,CH,A)


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

Re: SORT FIELDS: Two passes to be changed to one

 

Re: SORT FIELDS: Two passes to be changed to one

Postby BillyBoyo » Tue Jul 14, 2015 2:51 pm

What is the original order of the data? Can the data be kept in one or other order? (the 1,22 position for the first SORT is suspicious).

Yes you want to SORT large amounts of data as little as possible. No you can't put the same data in two different key orders (unless the key data happen to be the same anyway).

Keep the main data in one or other order. The processing of the data needs to be looked at with a "design" hat on.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: SORT FIELDS: Two passes to be changed to one

Postby Aki88 » Tue Jul 14, 2015 3:54 pm

<edited>

Hello Billy,

BillyBoyo wrote:Keep the main data in one or other order. The processing of the data needs to be looked at with a "design" hat on.


Very true; the challenge is, the i/p data is the o/p of multiple legacy VSAM files, which are being merged and subsequently data is culled out from them as one single sequential file - which is unsorted.

If I understand the setup correctly, this was why the subsequent SORTs were introduced at a later stage.

The first SORT arranges the data on the primary key of the VSAM files.
The second SORT rearranges the data on another field's value.

The idea of retaining at least one format of data was the first thing which'd come to my mind; but going back to the current setup, this is defeated.
So in either case I end up reading my file twice.

Having said that, to summarize, we can not do the sorting on seperate keys, in one pass? :(

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

Re: SORT FIELDS: Two passes to be changed to one

Postby BillyBoyo » Tue Jul 14, 2015 4:51 pm

If you have a file containing two discrete types of data you can, in one SORT, get them both into order.

For instance, RecordType1 needs to be in sequenced on one field, RecordType2 needs to sequenced on another.

You extend the record temporarily using INREC with a new sort key, and populate that sort key depending on the RecordType. Then you SORT on the extended key and use OUTREC or OUTFIL to return the data to its original size.

What you can't do is put the same record into two different sequences.

However, your situation is still unclear. If the data comes from a KSDS there is no need to SORT on the key to get the data in sequence. It is already in sequence because the KSDS is logically in sequence.

Need more information from you :-)
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: SORT FIELDS: Two passes to be changed to one

Postby Aki88 » Tue Jul 14, 2015 5:09 pm

Hello Billy,

My apologies for not putting across the details correctly; they are:

1. Data from multiple VSAM files is being put into one sequential file, in a simple top-down order; the data in sequential file is no longer sorted because records from two or more VSAM files can be out of sequence when brought together. Duplicates will not exist at this point, as the data is unique in all the different VSAM files. Key structure is exactly same though for all the VSAM files.
2. This sequential file is now fed to two different DFSORT steps, each step executing a different SORT FIELDS (details of same given earlier).
3. During operation mentioned in point 2, the first SORT step arranges data on the basis of the VSAM key, second SORT step does it on a different key - both steps o/p two different files.

I am trying to eliminate the two seperate SORT steps mentioned in point-3; and collate them into one single step, giving two differently SORTed files from a common input.

Hope I was able to put it across more clearly this time. :oops:

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

Re: SORT FIELDS: Two passes to be changed to one

Postby BillyBoyo » Tue Jul 14, 2015 5:22 pm

OK, is this an ongoing process? The old systems are still providing data?

If not ongoing, then SORT the data once into key order. Then you can have your different SORT each time it is needed.

If it is ongoing, I'd wonder about using MERGE to create the file, using multiple SORTINnn files, one for each VSAM file. After the MERGE, the output file is in sequence. When adding data, use MERGE again, keeping the data in sequence but relying on the fact that it is already in sequence. You'd still need the second SORT when that order was needed.

Whether it is worth revisiting anything depends on how long the process is going to continue vs the "cost" of doing the SORTs.

You cannot put the same record in two different sequences in one SORT.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: SORT FIELDS: Two passes to be changed to one

Postby Aki88 » Tue Jul 14, 2015 5:40 pm

Hello Billy,

It is an ongoing process.

BillyBoyo wrote:Whether it is worth revisiting anything depends on how long the process is going to continue vs the "cost" of doing the SORTs.


True that, which was why the query stuck to doing it using an intrinsic function; else a logic can indeed be built around it to give desired results.

This, though can be a DFSORT Wishlist item :)

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

Re: SORT FIELDS: Two passes to be changed to one

Postby BillyBoyo » Tue Jul 14, 2015 6:18 pm

Well, you can't just wish for things :-)
How would it work? You have a file with 10 records. For the first sort key, record one on the unsorted file needs to be in position eight, for the second sort the same record needs to be in position three.

Means you have to duplicate, and differentiate, the records. Means you double the size of your file.

For savings, you have to look at what has mingled/mangled the data in the first place. You're not going to get any wishful magic if you want the same record in two sequences.

For the process, you can of course duplicate the data. Apply updates in key order to both. No more big sorts. But, you already have the existing system for which no-one seems to have cared about the resources. To serially copy multiple files with the same key so that you have to SORT to get the data, which was in key order, into key order, is a stupidity. Sometimes, looking at the back-end of a stupidity can bring savings. The best savings are by not doing the stupidity in the first place.

There's no easy "out" on this one. I'm sure there are things which could be done to make considerable savings, just not where you're hoping.

These users thanked the author BillyBoyo for the post:
Aki88 (Tue Jul 14, 2015 7:51 pm)
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post