Re: Index problem with OnLine

In article k6b@cssun.mathcs.emory.edu, pp@q8.dk (Poul Pedersen) writes: > 06-Feb-1995 > > Subject: Index problem and error -252, -103 and -245 > ------------------------------------------------------ > > I have a problem..... If you can help, then please mail me ! > > > Problem description: > ==================== > I have a table with 3 indexes and as you can see > problems with my indexes. In the systables the nindexes > is 3, but in the tblspace the value nkeys is 5. > > What can create the shown problems ? > How can I avoid the problems ? > Is there any workaround ? > How can the nkeys value in tblspace be changed ? > > > Thanks from > ----------------------------------------------------------------- > Databaseadministrator Poul Pedersen > Kuwait Petroleum (Denmark) A/S > Hummeltoftevej 49 Mail: pp@q8.dk > 2830 Virum Voice: +45 45 98 45 94 > Denmark Fax: +45 42 85 14 18 > ----------------------------------------------------------------- Poul, I am only too familiar with this problem. Here's the solultion that I have used to fix the problem. Of course, this is only after the corruption has occured. I have been informed that index corruption should be fixed in 5.03. I will know this for sure once we have the opportunity to upgrade and operate under it for awhile. Here's what you need to do to fix the problem: Note: You MUST understand what you are doing and the risks that you are taking when you do the following steps. 1. Try to drop all indexes on the table 2. Verify nindexes value in the systables is different than the number of indexes on the table (you've already done this, I believe) 3. update systables set nindexes = 0 where tabname = "table name" 4. delete from sysindexes where tabid = (select tabid from systables where tabname = "table name") 5. tbmode -c (force a checkpoint) 6. tbcheck -pT database:table check to make sure the nkeys = 0. (If it isn't you have other problems, call Informix. If it is 0, move on to the next step.) 7. re-create all indexes on the table 8. run tbcheck -ci database, tbcheck -cc database, tbcheck -cd database to ensure everything is back to normal. If you experience index corruption and the log files say to run tbcheck -cI, don't. Instead you should identify the index that is corrupted, drop the index, and re-create it. I have found, many times, that tbcheck -cI doesn't always do the repairs cleanly. I hope this helps you. Of course, when doing these repairs it is best to limit access to your database and in particular the table in question. Good luck! Let me know if I can help you further with this. -Debbie Shelton DBA McCaw Cellular Communication, Inc. debbie.shelton@mccaw.com