Retrieve records in the order of alternate index

jacobdng · New User Joined: 27 Aug 2006 Posts: 51 Location: Taiwan

I need to retrieve certain records in the order of an alternate index.
The target VSAM file has 5 million records.
The first screen is as the following:

Robert Sample · Posted: Fri Jul 31, 2015 8:38 am

What do you mean by "best way"? When you retrieve records from a KSDS, you've got really three options:
1. sequentially read them from the first to the last which gives them to you in primary key sequence
2. randomly retrieve them based on something other than key sequence
3. use the alternate index path to retrieve them in the alternate index sequence (this option is not always available -- not all VSAM KSDS have alternate indexes defined)

Where is your problem? What have you tried? What is not working? Do you want to retrieve the data online (hint: 5 million records will take a LOT of enter keys), or through a batch program (and if batch, are you writing one yourself or using a utility)?

jacobdng · New User Joined: 27 Aug 2006 Posts: 51 Location: Taiwan

I want to retrieve records in the alternate index sequence using File-Aid. But, TSO had been logged off automatically long before the outcome appeared on the screen. I had been waiting for 45 minutes at least.

However, it took less that 15 minutes to retrieve the 64 records out of 4 million if I changed the dataset name to KSDS itself instead of the alternate index. The selecting criteria was only one field(date).

Did I miss something in the operation process of File-Aid when the dataset name is an alternate index?

PeterHolland · Posted: Mon Aug 03, 2015 12:41 pm

Did you use the alternate key?

Robert Sample · Posted: Mon Aug 03, 2015 6:04 pm

Is the selection field the alternate index? If not, you are attempting to sequentially read all 5 million records and that could take a while. You are not explaining very much about what you want to do, so the quality of your answers will be poorer than if you provided a full and complete explanation.

Have you contacted the vendor for assistance? They are MUCH more likely to be able to answer questions of this type since they know the product much better than anyone on this forum.

Bill Woodger · Posted: Mon Aug 03, 2015 6:30 pm

To serially process 5m records on the alternate index, assuming that each record has an alternate-index entry, requires 5m keyed-reads.

This is going to be very slow, and use a lot of IO and use a lot of CPU.

In short, don't do it.

It will be much more resource-efficient to read the file sequentially on the main key, SORT on the alternate key and process the output from the SORT.

The alternate-index is a key to a key. The data is then accessed using the key to the key to the data. Toss in three or more levels of index.

Keep your alternate-index usage to "look-up" and preferably only in the online or with as minimal actual accesses as possible.

jacobdng · New User Joined: 27 Aug 2006 Posts: 51 Location: Taiwan

Answer to Robert & Peter:
I did use the alternate index in the selection field.
Please refer to my initial description, there is a copy screen of File-Aid Browse, which I specifically use the name "NBSDDSN2.ZBNBTZM.PATH " as the brwosing target and it is the alternate index.

>>>>>>>>>>>>>>>

I understand that DFSORT should be involved in batch programming in terms of retrieving records out of huge volume.

I thought that File-Aid had magic to retrieve data swiftly in the order of an alternate index. Obviously, I was wrong.

Thanks for your info.

PeterHolland · Posted: Tue Aug 04, 2015 1:29 pm

My question was : Did you use the alternate key?

That is not the same as the "index"

jacobdng · New User Joined: 27 Aug 2006 Posts: 51 Location: Taiwan

Peter,

Please refer to my second quote :

The item (15,8) is a date field which is part of the alternate key (15,24).

Jacob