Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Fetch the last three occurences from a VSAM file

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
ashishwach

New User


Joined: 07 Nov 2008
Posts: 19
Location: India

PostPosted: Fri Mar 06, 2009 2:17 pm    Post subject: Fetch the last three occurences from a VSAM file
Reply with quote

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..
Back to top
View user's profile Send private message

Debabrata Pruseth

New User


Joined: 11 Dec 2008
Posts: 59
Location: Pune , India

PostPosted: Fri Mar 06, 2009 2:44 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
ashishwach

New User


Joined: 07 Nov 2008
Posts: 19
Location: India

PostPosted: Fri Mar 06, 2009 4:59 pm    Post subject:
Reply with quote

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..
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8593
Location: Back in jolly old England

PostPosted: Fri Mar 06, 2009 5:13 pm    Post subject:
Reply with quote

Quote:
we thought of unloading the VSAm to a sequential file and then reloading it into another vSAM with key in descending order...

And how would you accomplish loading the VSAM file with the key in descending order.

Have you considered using a second VSAM file which is populated from the original VSAM but contains only the last 3 records of each account.
Back to top
View user's profile Send private message
ashishwach

New User


Joined: 07 Nov 2008
Posts: 19
Location: India

PostPosted: Fri Mar 06, 2009 8:36 pm    Post subject:
Reply with quote

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..
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Mar 06, 2009 9:53 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
ashishwach

New User


Joined: 07 Nov 2008
Posts: 19
Location: India

PostPosted: Tue Mar 10, 2009 3:50 pm    Post subject:
Reply with quote

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...
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8593
Location: Back in jolly old England

PostPosted: Tue Mar 10, 2009 4:14 pm    Post subject:
Reply with quote

Correct
Back to top
View user's profile Send private message
ashishwach

New User


Joined: 07 Nov 2008
Posts: 19
Location: India

PostPosted: Tue Mar 10, 2009 4:49 pm    Post subject:
Reply with quote

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!!
Back to top
View user's profile Send private message
ashishwach

New User


Joined: 07 Nov 2008
Posts: 19
Location: India

PostPosted: Tue Mar 10, 2009 4:53 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Mar 10, 2009 8:08 pm    Post subject:
Reply with quote

Hello,

To repeat:
Quote:
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.

Using INCLUDE/OMIT you can keep or discard records selectively.
Back to top
View user's profile Send private message
ashishwach

New User


Joined: 07 Nov 2008
Posts: 19
Location: India

PostPosted: Thu Mar 12, 2009 3:23 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 7997
Location: Bellevue, IA

PostPosted: Thu Mar 12, 2009 4:55 pm    Post subject:
Reply with quote

The easiest complement of a date in YYYYMMDD format is 99999999 - date.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Changing of LRECL of a file abdulrafi DFSORT/ICETOOL 2 Fri Mar 24, 2017 3:25 pm
No new posts splitting a file abdulrafi DFSORT/ICETOOL 4 Fri Mar 24, 2017 11:51 am
No new posts Receive a file using PCOMM macro Harald.v.K IBM Tools 0 Thu Mar 23, 2017 6:50 pm
No new posts Export flat file data into excel sheet murali.andaluri DFSORT/ICETOOL 2 Mon Mar 20, 2017 5:39 pm
No new posts Formatting VB File Learncoholic DFSORT/ICETOOL 3 Mon Mar 20, 2017 12:29 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us