creating alternate index with more than 32,767 pointers

abin · Active User Joined: 14 Aug 2006 Posts: 198

Hi ,

I am trying to create an alternate index using following commands
DEFINE AIX (NAME (xxxx.yyyy.zzzz.zzzz1) -
RELATE (xxxx.yyyy.zzzz.zzzz) -
CYL (300 100) -
VOL(PRS*,*,*,*,*) -
SHAREOPTIONS(2) -
KEYS (10 231) -
UPGRADE) -
DATA (NAME (xxxx.yyyy.zzzz.zzzz.DATA) -
CISZ (8192)) -
INDEX (NAME (xxxx.yyyy.zzzz.zzzz.INDEX))

I defined the path as follows
DEFINE PATH (NAME (xxxx.yyyy.zzzz.zzzz.PATH) -
PATHENTRY (xxxx.yyyy.zzzz.zzzz1) -
UPDATE -
)

When I try to build alternate index using following commands
BLDINDEX INFILE (BASEDD) -
OUTFILE (AIXDD)

I am getting the following error

IDC1646I 46425 EXCESS PRIME KEY VALUES FOR AIX KEY 40404040404040404040

I searched IBM manual and found the following reason
The fact that the pointers are ordered by arrival time implies that immediately after an alternate index has been built (and as long as it remains unchanged), its pointers are in prime-key order. The maximum number of pointers that can be associated with a given alternate key is 32,767, provided the maximum possible record length for spanned records is not exceeded.

It is true that my VSAm file contains some 9 lakh records and except one record all other records contains spaces in the alternate key field.

But my requirement is to create the a;ternate index now and then add new records with proper values in the alternate index field.

Can you suggest any way of doing this.

Thanks.

expat · Posted: Fri May 18, 2007 5:47 pm

If 32,767 is the limit, then I would say that there is no way that you can do it.

abin · Active User Joined: 14 Aug 2006 Posts: 198

Ok ,

Then could You please sugest some other method of achieving the goal.

Thanks.

expat · Posted: Fri May 18, 2007 6:13 pm

Whether or not it may affect your search results, I could only think of populating the AIX key with some form of dummy data to remove the excess number of duplicate AIX keys.

abin · Active User Joined: 14 Aug 2006 Posts: 198

Hi,

I feel thats not a good idea when no of records with duplicate alternate index is around 9 lakh.

Please correct if I am wrong.

Thanks.

expat · Posted: Fri May 18, 2007 6:36 pm

Yeah, that's why I did not suggest it in the first reply.

abin · Active User Joined: 14 Aug 2006 Posts: 198

Thanks ,

I'll try to find out some other solution for the problem.

Will get back.

dick scherrer · Posted: Fri May 18, 2007 11:40 pm

Hello,

When/how will the actual data for that key be put into the records? Is there some process that will run creating a set of records to update the file putting this value into the record?

If this is going to be done wholesale, wait until the data is non-blank, then build the alternate. Until the data is present, the alternate key will be of no use anyway.

enrico-sorichetti · Posted: Sat May 19, 2007 1:31 am

please avoid using indian only units of measure...
the forum is watched by non indian people who do not know what a lakh is

regards

abin · Active User Joined: 14 Aug 2006 Posts: 198

dick scherrer · Posted: Sat May 19, 2007 7:21 pm

Hello,

Might you make a rule that any key that starts with ##$$ is a non-entry? If you have secondary key that will be populated as new records are added, they would be directly accessible, but the others would never be read randomly anyway.

You could start with ##$$00001, ##$$00002, etc and spread the initial load over many "dummy" values to get around the 32k limit.

Not pretty, but might get you where you need to be.

abin · Active User Joined: 14 Aug 2006 Posts: 198

Hi All,

Even if it is giving an error message the alternate index is created and I am able to use it.

. I suppose, the error message is just a warning nessage saying that some thing bad happened.

Could anybody please explain the scenario, may be from their experiance.

Thanks,
Abin