Part II - Derby Administration Guide
Derby Server and Administration Guide
59
Part two: Derby Administration Guide
This section of the guide is divided into several administrative tasks.
Checking database consistency
If you experience hardware or operating system failure, you can use the
SYSCS_UTIL.SYSCS_CHECK_TABLE function to verify that the database is still
consistent.
Check consistency only if there are indications that such a check is needed because a
consistency check can take a long time on a large database.
The SYSCS_CHECK_TABLE function
The SYSCS_UTIL.SYSCS_CHECK_TABLE() function checks the consistency of a Derby
table. In particular, the SYSCS_UTIL.SYSCS_CHECK_TABLE function verifies the
following conditions:
· Base tables are internally consistent
· Base tables and all associated indexes contain the same number of rows
· The values and row locations in each index match those of the base table
· All BTREE indexes are internally consistent
You run this function in an SQL statement, as follows:
VALUES SYSCS_UTIL.SYSCS_CHECK_TABLE(
SchemaName, TableName)
where SchemaName and TableName are expressions that evaluate to a string data type.
If you created a schema or table name as a non-delimited identifier, you must present
their names in all upper case. For example:
VALUES SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'CITIES')
The SYSCS_UTIL.SYSCS_CHECK_TABLE function returns a smallint. If the table
is consistent (or if you run SYSCS_UTIL.SYSCS_CHECK_TABLE on a view),
SYSCS_UTIL.SYSCS_CHECK_TABLE returns a non-zero value. Otherwise, the function
throws an exception on the first inconsistency that it finds.
For a consistent table, the following result is displayed:
1
-----
1
1 row selected
Sample SYSCS_CHECK_TABLE error messages
This section provides examples of error messages that the
SYSCS_UTIL.SYSCS_CHECK_TABLE() function can return.
If the row counts of the base table and an index differ, error message X0Y55 is issued:
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
'T1_I' on table 'APP.T1' has 4 rows, but the base table has 5 rows.
The suggested corrective action is to recreate the index.
