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
 

 

creating alternate index with more than 32,767 pointers

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

Active User


Joined: 14 Aug 2006
Posts: 198

PostPosted: Fri May 18, 2007 5:21 pm    Post subject: creating alternate index with more than 32,767 pointers
Reply with quote

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.
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 May 18, 2007 5:47 pm    Post subject:
Reply with quote

If 32,767 is the limit, then I would say that there is no way that you can do it.
Back to top
View user's profile Send private message
abin

Active User


Joined: 14 Aug 2006
Posts: 198

PostPosted: Fri May 18, 2007 6:07 pm    Post subject:
Reply with quote

Ok ,

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

Thanks.
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 May 18, 2007 6:13 pm    Post subject:
Reply with quote

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

Active User


Joined: 14 Aug 2006
Posts: 198

PostPosted: Fri May 18, 2007 6:29 pm    Post subject:
Reply with quote

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.
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 May 18, 2007 6:36 pm    Post subject:
Reply with quote

Yeah, that's why I did not suggest it in the first reply.
Back to top
View user's profile Send private message
abin

Active User


Joined: 14 Aug 2006
Posts: 198

PostPosted: Fri May 18, 2007 6:53 pm    Post subject:
Reply with quote

Thanks ,

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

Will get back.
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 May 18, 2007 11:40 pm    Post subject:
Reply with quote

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

Global Moderator


Joined: 14 Mar 2007
Posts: 10274
Location: italy

PostPosted: Sat May 19, 2007 1:31 am    Post subject: Re: creating alternate index with more than 32,767 pointers
Reply with quote

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

Active User


Joined: 14 Aug 2006
Posts: 198

PostPosted: Sat May 19, 2007 10:16 am    Post subject: Re: creating alternate index with more than 32,767 pointers
Reply with quote

Quote:
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?


The answer is no. The current values in the alternate key is spaces. However, newly inserted records will have non space values in the place.

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

I'll keep it in mind
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: Sat May 19, 2007 7:21 pm    Post subject:
Reply with quote

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

Active User


Joined: 14 Aug 2006
Posts: 198

PostPosted: Mon May 21, 2007 10:01 pm    Post subject:
Reply with quote

Hi All,

Even if it is giving an error message the alternate index is created and I am able to use it. icon_cool.gif . 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
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 SQL - select data available in index Nileshkul DB2 3 Mon Jun 26, 2017 1:30 am
No new posts setting new limit Key values for inde... srilata83 DB2 1 Fri Feb 10, 2017 9:24 pm
No new posts Order BY AND Index Nileshkul DB2 2 Sat Dec 31, 2016 6:33 pm
No new posts Creating an 'Enter' event with IMS MF... dileep chirasani Java & MQSeries 1 Sat Aug 06, 2016 2:23 am
No new posts Creating a dynamic sort card in JCL u... ChitraChhabra DFSORT/ICETOOL 4 Wed Aug 03, 2016 6:15 pm


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