Fetch the last three occurences from a VSAM file

ashishwach · New User Joined: 07 Nov 2008 Posts: 19 Location: India

Hi all,
In our requirement, there is a sequential file containing about 100 million records(basically contains the loan no) which we are reading sequentially in our program.
Now, corresponding to each record in the input file, we are reading a VSAM file that may contain around 500 records for one loan no read from the input file. Key field for the VSAM file is loan no + a date.
And, the requirement says that we need to pull the data from VSAM file pertaining to the last three records of any loan no.

So forthis reqmt, I have couple of ways to achieve it -

One way to achieve this would be use Start BR and then use READ till we hit the last three records and fetch the data corr to last three records.
But since, the volume of data is very huge, it may take a lot of time.

Second way would be use READ previous after pointing to a record greater than that in the input file, but i am not sure about the case where we will be pointing to the last record in the input file for whichit correspond to EOF in VSAM, so will be a problem handling that condition..

And then, I heard something about Alternate Index but doesn't really know about it..
Will appreciate if someone can throw light on that or is there any better way to achieve that..

Debabrata Pruseth · Posted: Fri Mar 06, 2009 2:44 pm

Let me know if i get the requirement wrong. The key to your VSAM file is LOAN NBR + DATE . Now as you have mentioned that for each loan number from the input record there will be 500 records in the VSAM file hence the VSAM file contains the loan records for 500 different dates , sorted on DATE in ascending order , and you want to extract the last three records.

One way is that , in the FIRST STEP you copy the VSAM file to a new VSAM file , sorted on loan number ascending and date in descending . Use this file in your program in the SECOND STEP , just do a STARTBR on loan number and dates as LOW-VALUES. Read sequentially the first three records and process.
Delete the VSAM file in the THIRD STEP.

ashishwach · New User Joined: 07 Nov 2008 Posts: 19 Location: India

Thanks Debabrata,

We had this solution in mind but in a little twisted manner...
we thought of unloading the VSAm to a sequential file and then reloading it into another vSAM with key in descending order...almost similar to the way u suggested..
but the concern here is..would it not be much costlier in terms of resources that it would take..

expat · Posted: Fri Mar 06, 2009 5:13 pm

ashishwach · New User Joined: 07 Nov 2008 Posts: 19 Location: India

Well.. after unloading the VSAM to a sequential, we need to first sort it based on key field in descending order..then we can load that onto a vsam file...

regarding the new vsam file containing only last three records, we can't do that.. because it checks for one more field which is transaction-type, and if it is 'M' (monetary), then only we will populate data for that record..
So basically, it can be three records or more than three from the VSAM..

dick scherrer · Posted: Fri Mar 06, 2009 9:53 pm

Hello,

To load the vsam file "descending" calculate the complement of the date and use that in the key rather than the actual date. The vsam key will ascend, but the date will descend.

If you load another vsam file to do this work, i believe you can limit the population to 3 per loan. When building the file to load, discard the "more than 3" if they are not needed.

ashishwach · New User Joined: 07 Nov 2008 Posts: 19 Location: India

Hi Ppl,
I am not able to load the data in VSAM file when key is in descending order..I am getting out of order sequence error....
When I checked in other thread, I found that to load data in VSAM, key has to be in ascending order otherwise you can't load the data..

Please confirm if it is correct...

expat · Posted: Tue Mar 10, 2009 4:14 pm

Correct

ashishwach · New User Joined: 07 Nov 2008 Posts: 19 Location: India

Thanks expat..
In that case, is there any way to do following -
Key in my input VSAM file is -
1. Loan no
2. Date
Data in the file -
1. Tran Type

We want an output VSAM file that will have last three occurences only for each loan no but with Tran Type = M. We can write a program to do that but if that can be achieved through SORT or something, would be good!!

ashishwach · New User Joined: 07 Nov 2008 Posts: 19 Location: India

I also tried thru sort using SEQNUM as one outrec option and include a sequence no at the end of the file...and later on I thought that based on that SEQNUM I will keep only those records with SEQNUM as 1 or 2 or 3.
But, there is no option to restart the sequence from 1 when the loan no changes.

dick scherrer · Posted: Tue Mar 10, 2009 8:08 pm

Hello,

To repeat:

ashishwach · New User Joined: 07 Nov 2008 Posts: 19 Location: India

Hello dick scherrer

I don't have an idea to calculate the complement of the date in job..is it done through sort itself..

Meanwhile, we have achieved this using ICETOOL..
SELECT FROM(INDD) TO(OUTDD) ON(1,13,PD) FIRST(3)

Before this, we unloaded the VSAM onto a seq file with key in desc order using include to keep the records we wanted..

Although we are able to do this way now..but just wanted to know more abt the way you suggested.. pls let me know!!

Thanks

Robert Sample · Posted: Thu Mar 12, 2009 4:55 pm

The easiest complement of a date in YYYYMMDD format is 99999999 - date.