HOW TO RECREATE INDEX WHILE OTHER USER ON FLY

[remsor]

Hello VFP Experts,

I have an application with 10 user at the same time, they are sharing data's with each other. My question is how do i recreate the inde or the cdx of table with out forcing user to log out or exit the application.

Thanks

 

[rgbean]

You can't - the tables must be USEd EXCLUSIVE to create indexes. If all the user's are simply querying - not adding or changing data, then you could make a copy of the table, rebuild indexes on it, and then just temporarily shut everyone down to rename the table(s) and index(s).

Rick

 

[jimoo]

Rick correct. You need exclusive access.

The only way to allow users to use the system while reindexing is to close the tables when they enter / exit a module, and then check to see if they can get access to the tables prior to entering the module and prior to reindexing. If you cannot (someone has it) you will need to prompt them to try later. That's about the best you can do in a live environment short of making everyone exit, but it has its drawbacks because the overhead with open/closing tables.




Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[mm0000]

Actually eclusive use of the table is only required for reinexing and not for creating indexes. Indexes can be created by using tables in shared mode.

 

[Mike Gagnon]

mm0000

Actually eclusive use of the table is only required for reinexing and not for creating indexes. Indexes can be created by using tables in shared mode.

I'm not sure what documentation or testing environment you have but the following throws an error.

USE c:\apptest\data\addresses.dbf SHARED
INDEX ON  birthdate TAG date && File must be opened exclusively

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[badukist]

Yes, but

USE c:\apptest\data\addresses.dbf SHARED
INDEX ON  birthdate to date.idx

works without errors. I think the index is only local and I don't know if is updated from table if someone else change records.

 

[Mike Gagnon]

badukist

My question is how do i recreate the inde or the cdx of table with out forcing user to log out or exit the application.

I was responding to the original post.


Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[badukist]

Ok, my mistake

Anyway, the IDX created is not usable , I've tested, is not updated with changes from other users.

 

[Steve Bowman]

the create of IDX not being structural is only opened when requested in the USE syntax. I use this type of IDX when I need temporary indexes like


use myTable shared
cTmpIndex=sys(3)+'idx'
index on myTable.myField to &cTmpIndex
cOldOrder=set('ORDER')
set order to &cTmpIndex
OTHER STUFF
set order to &cOldOrder
delete file &cTmpIndex


for resumore uses as in large sets of query and or many scan for situation where sometimes the overhead of creating the INDEX is faster then the overhead on the NON OPTIMIZED data seeks

overall if the IDX is in use shared you can not create on the fly anyway.

when I need to reindex files in an application the is in use I create a small timer controled application that I can run on a server attempting exclusive use every 15 mins or so and when I get it I quickly re-create the index(s) and close the dbf or create an app thet creates semiphor files instructing the app to release tables and loop until the semifor file is removed.

Steve Bowman
steve.bowman@ultraex.com

 

[Evbcs]

I frequently use the ".IDX" method above for creating temporary indexes. I make the IDX file name unique for each user. When you are done with the IDX, be sure to have a housekeeping routine to delete the IDX file.