Tool to check corrupted index

Hi all, My client have a situation where after reboot, informix keep on dumping af file and complaining about inconsistency of indexes. Honestly i do not have a full idea what has been done at customer site but i do suspect that there is an abnormal shutdown. So we use the tool onchecl -cI <dbname> to scan the entire db with the hope to know all the corrupted indexes. But problem is after scanning for x number of tables with corrupted indexes informix will have a PANIC shutdown. The informix version is IDS 9.30.FC6 running on SUN OS. My question is is there a tool out there where i can detect all the corrupted indexes without causing informix to have af file dump? oncheck utility just couldn't suite our needs.

No such tool. IDS doesn't know that an index is corrupted, so it cannot tell you in a sysmaster query for example. I would just run the oncheck -cDI (DO include the 'D' so that the indexes are checked for missing rowids - without the D only internal consistency of the index is verified not completeness) for one table at a time and repair any corrupted indexes that each run finds (rebuild the indexes manually in dbaccess or sqlcmd DO NOT let oncheck fix them it's too slow!) before performing the oncheck run for the next table. That should keep the server from crashing. Note that I have found that indexes mainly get corrupted in databases that have logging turned off when the server crashes or the system crashes or is shutdown without bringing the IDS engine offline first. Turn on logging and you will find MANY fewer trashed indexes. Art S. Kagel ----- Original Message ----- From: Patrick Ley <ids@iiug.org> At: 3/09 10:18:36 Hi all, My client have a situation where after reboot, informix keep on dumping af file and complaining about inconsistency of indexes. Honestly i do not have a full idea what has been done at customer site but i do suspect that there is an abnormal shutdown. So we use the tool onchecl -cI <dbname> to scan the entire db with the hope to know all the corrupted indexes. But problem is after scanning for x number of tables with corrupted indexes informix will have a PANIC shutdown. The informix version is IDS 9.30.FC6 running on SUN OS. My question is is there a tool out there where i can detect all the corrupted indexes without causing informix to have af file dump? oncheck utility just couldn't suite our needs. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Hi Art S. Kagel Thanks to the fast respond. I have ~6500 tables within the database and scan and fix one by one is way too slow. In fact i adopt the same strategy but customer is pressuring us to have quicker way to recover the server. Off caused restore from backup would be easy but this is not the option at all (according to customer). Hence i'm looking for a tools to help to reduce the time taken to completely recover the database.

Then just run 'til it crashes, restart fix what was already found and iterate. I don't know what else to suggest. Some oncheck's will run with the server offline, don't remember if -cDI is one such. Worth a try. Art ----- Original Message ----- From: Patrick Ley <ids@iiug.org> At: 3/09 10:44:55 Hi Art S. Kagel Thanks to the fast respond. I have ~6500 tables within the database and scan and fix one by one is way too slow. In fact i adopt the same strategy but customer is pressuring us to have quicker way to recover the server. Off caused restore from backup would be easy but this is not the option at all (according to customer). Hence i'm looking for a tools to help to reduce the time taken to completely recover the database. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Final suggestion, get a dbschema (or better myschema into separate table and index/constraint files both because it separates out the indexes and constraints for you and because it will create named indexes for every constraint), drop all the constraints and indexes, rebuild ALL of the indexes from scratch using the schema, and finally reestablish the constraints (if they give you trouble because of bad data turn filtering on). Art S. Kagel ----- Original Message ----- From: Art Kagel <ids@iiug.org> At: 3/09 10:50:31 Then just run 'til it crashes, restart fix what was already found and iterate. I don't know what else to suggest. Some oncheck's will run with the server offline, don't remember if -cDI is one such. Worth a try. Art ----- Original Message ----- From: Patrick Ley <ids@iiug.org> At: 3/09 10:44:55 Hi Art S. Kagel Thanks to the fast respond. I have ~6500 tables within the database and scan and fix one by one is way too slow. In fact i adopt the same strategy but customer is pressuring us to have quicker way to recover the server. Off caused restore from backup would be easy but this is not the option at all (according to customer). Hence i'm looking for a tools to help to reduce the time taken to completely recover the database. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.