SORT Help on DATE filed.



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

SORT Help on DATE filed.

Postby Analyst_Kumar » Tue Nov 24, 2015 11:07 am

Hi All,

I have a Transaction Data file with 5 million records. And the key information of the file is as follows.
It holds the data for a calendar month and the data gets appended every day mid-night

LRECL = 9855
RECFM=VB
DATE field starts at position 3. And it looks like 'YYYYMMDD'.
For Example, Transaction Data file for November 2015 has the records starting from 1/Nov/2015 till today. latest data is at the bottom of the file.

I have 2 scenarios here.
One is to extract the data from the input-file (5 million records) for a specific date, mostly yesterday (1 day before current day)

I use the following code
SORT FIELDS=COPY
INCLUDE COND=(7,8,CH,EQ,C'20151123')


Second is to extract the data from the input-file (5 million records) starting from a specific date.

I use the following piece of code
SORT FIELDS=COPY
INCLUDE COND=(7,8,CH,EQ,C'20151116',OR,
                        7,8,CH,EQ,C'20151117',OR,
                        7,8,CH,EQ,C'20151118',OR,
                        7,8,CH,EQ,C'20151119',OR,
                        7,8,CH,EQ,C'20151120',OR,
                        7,8,CH,EQ,C'20151123')


Query: Above piece of codes are working fine to my requirement. But these are processing 4.8 million records just to extract 20,000 records unnecessarily. Is there a better way of doing it.
Analyst_Kumar
 
Posts: 3
Joined: Fri May 29, 2015 9:57 pm
Has thanked: 0 time
Been thanked: 0 time

Re: SORT Help on DATE filed.

 

Re: SORT Help on DATE filed.

Postby BillyBoyo » Tue Nov 24, 2015 12:26 pm

You can use GT or GE, LE or LT, express ranges or groups with AND and OR.

However, what magic do you feel should work to get your task done without reading the records prior to the ones you want?
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: SORT Help on DATE filed.

Postby Aki88 » Tue Nov 24, 2015 12:50 pm

Hello,

Analyst_Kumar wrote:I have a Transaction Data file with 5 million records.
....
It holds the data for a calendar month and the data gets appended every day mid-night
....
....

Is there a better way of doing it.


In continuation to what Billy has already mentioned, change the process-design a tad bit.
Fwiw, it is a li'l weird that we first append data right at the end of a file, and then the very next day go ahead and extract the very same data, again - from the end of the very same file.
A possible solution would be to simply write a GDG version; from your existing process; and later append this GDG version to your transaction file. Have GDG limit set to 2; this way you are not eating up much additional space, also you have two day old data readily available; this removes the need to run one of your SORTs altogether.

For the second case, wherein you have a few random dates, like Billy said - you dont have much to go on with, other than reading the file and figuring out if the date is the one you need.

Hth.
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