Checking the Consistency of All Tables

Derby supplies the system function SYSCS_UTIL.SYSCS_CHECK_TABLE to check the consistency of a table's indexes against the base table. The following process can be used when it is desirable to check the consistency of all tables in the database.

If no exception is reported you are done, all is well. If an exception is thrown the query aborts and there are tables that have not been checked. Note the name of the table listed in the exception and rerun the query excluding the problem table.

Continue to exclude problem tables from the select until it completes without exceptions. All tables have been checked. The list of tables excluded are the ones that require rebuilding.

SYSCS_CHECK_TABLE performs more checks in debug (sane) builds, so if the above procedure does not reveal any inconsistencies and you still suspect something is wrong, you may want to repeat the steps with a debug build of Derby.

Identifying the Problematic Index With Just a Conglomerate Number

With an insane build if something very unexpected is wrong you may get just a NullPointerException or the errors will identify only the conglomerate number. If this occurs, it is good to switch to a debug/sane build which will provide more information. Use the lib debug distribution for your version from the derby downloads page and repeat the process above. Now you may get an error that includes a Container and will reveal the conglomerate number, in this case 7905:

Repairing Corrupt Indexes found by the consistency check

If you found corrupt indexes in the consistency check, you may be able to drop and recreate them to get them back into a consistent state.

First exit ij or the jvm accessing the database, and make a tar zip copy of the database, preserving date stamps if possible. This can be used to try to understand the root cause of the corruption and to start over if you make a mistake or make things worse. A frequent cause is corruption after restore if proper online database backup procedures were not used. Ask derby-user@apache.org for help understanding how the corruption occurred.

Without knowing the exact cause of the corruption, it is quite possible there is other corruption unseen. The only way to be 100% certain the database is ok is to manually check the data, export it, create a new database using the dblook output and import the data.