OnLine 5.0 BUG -- create index problems

7.27.92 MAJOR BUG IN 5.0 -- tables MUST be locked in exclusive mode before creating indexes or risk trashing the systables rowid pointers into the indexes. This trashes the data- base requiring a dbexport/dbimport or restore from archive. Just a warning to OnLine 5.0 DBA's. The other day I needed to drop and recreate some indexes on most of the tables in one of my databases. I had done some table shuffling within extents and some of the tables had grown significantly since their original creation. I started a transaction, dropped the indexes and recreated them. I checked the sql script when finished as well as the engine log -- no errors recorded. I committed the transaction and went home. NOTE: Locks are tuned to 3000 and I was running a "tbstat -u -r 60" during the entire process and the greatest number of locks used was 685. The next morning, no one could access the database. I know, I know, I shouldn't have believed the log or the result code from the script and should have tried to run some applications but I figured the engine had better be able to create indexes correctly. After much research on my part as well as tech support, we were able to reproduce AND AVOID the problem with a test table. By not locking the table in exclusive mode, the index would be created but the rowid pointers would get screwed up. A tbcheck -ci showed the index as ok but a tbcheck -cI (which hits the rowids) would fail all over the place. Worse, it (tbcheck -cI) couldn't correct the problem because it couldn't drop the index to recreate it. I also couldn't manually drop the index and recreate it either; I got unknown rowid errors all over the place. The tech support engineer later found notes to support this. The only solution to the problem is to recreate the database either through a dbexport/dbimport or through restoration from archive. I chose the later as I always make it a practice to archive the system before and after I make any major changes to any of the databases. I must say I was pleased with the restoration process. I had never done a restore in the OnLine environment just Turbo. The restore ran very smoothly and most important of all, quickly! Anyway, I pass this on to perhaps help someone avoid the same trap we ran into. ------------------------------------------------------------- Carlton Doe cdoe@sunset.utah.edu attmail: attmail!alexis!carlton