DatabaseConsistencyCheck

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.

1. Integrate the system function into a data dictionary query run it.

   SELECT schemaname || '.' || tablename as TableName,
      SYSCS_UTIL.SYSCS_CHECK_TABLE(schemaname, tablename) AS OK
   FROM sys.sysschemas s, sys.systables t
   WHERE s.schemaid = t.schemaid
      and t.tabletype = 'T';

2. 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.

   SELECT schemaname || '.' || tablename as TableName,
      SYSCS_UTIL.SYSCS_CHECK_TABLE(schemaname, tablename) AS OK
   FROM sys.sysschemas s, sys.systables t
   WHERE s.schemaid = t.schemaid
      and t.tabletype = 'T'
      and tablename not in (<tableX>,<tableY>,<table...>);

3. 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 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.

EXAMPLE :

ij> 
SELECT schemaname || '.' || tablename as TableName,
   SYSCS_UTIL.SYSCS_CHECK_TABLE(schemaname, tablename) AS OK
FROM sys.sysschemas s, sys.systables t
WHERE s.schemaid = t.schemaid
   and t.tabletype = 'T';

TABLENAME
        |OK
----------------------------------------------------------------------------------------
--------------------
APP.AIRLINES
        |1
ERROR X0Y55: The number of rows in the base table does not match the number of rows in at least 1 of the indexes on the
table. Index 'SQL051121040009780' on table 'APP.COUNTRIES' has 116 rows, but the base table has 117 rows.  The suggested
 corrective action is to recreate the index.

ij> 
SELECT schemaname || '.' || tablename as TableName,
   SYSCS_UTIL.SYSCS_CHECK_TABLE(schemaname, tablename) AS OK
FROM sys.sysschemas s, sys.systables t
WHERE s.schemaid = t.schemaid
   and t.tabletype = 'T'
   and tablename not in ('COUNTRIES');

TABLENAME
        |OK
-----------------------------------------------------------------------------------
--------------------
APP.AIRLINES
        |1
APP.CITIES
        |1
APP.FLIGHTS
        |1
APP.FLIGHTAVAILABILITY
        |1
APP.MAPS
        |1
APP.FLIGHTS_HISTORY
        |1

6 rows selected