db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Apache Wiki <wikidi...@apache.org>
Subject [Db-derby Wiki] Update of "DatabaseConsistencyCheck" by StanleyBradbury
Date Fri, 23 Jun 2006 21:55:22 GMT
Dear Wiki user,

You have subscribed to a wiki page or wiki category on "Db-derby Wiki" for change notification.

The following page has been changed by StanleyBradbury:
http://wiki.apache.org/db-derby/DatabaseConsistencyCheck

New page:
## Submitted June 23, 2006
== 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';
}}}

 1. 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...>);
}}}

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

=== 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
}}}

Mime
View raw message