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

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

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:

You can get the schema and index name with the following system table query.

SELECT C.CONGLOMERATENUMBER, C.CONGLOMERATENAME, S.SCHEMANAME  FROM SYS.SYSCONGLOMERATES C, sys.sysschemas s  WHERE CONGLOMERATENUMBER = 7905 AND s.schemaid = C.schemaid ;

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.

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

  2. Run dblook to get the ddl for the database, e.g.
    •  java org.apache.derby.tools.dblook -d jdbc:derby:mydb > mydb_ddl_sql.out 

  3. For each index that was problematic in the consistency check, drop the index, e.g.
    •  DROP INDEX APP.SQL051121040009780 

    • Then look in the dblook output for the index name for the exact syntax to recreate it.
  4. Do an offline compress of all the tables. Below is some sample code for doing this:

import java.sql.*;

public class CompressAll {

    public static void main(String[] args) throws Exception {
        if (args.length == 0) {
            System.err.println("Usage: java CompressAll <url>");
            System.exit(1);
        }
        String url = args[0];
        Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
        Connection conn = DriverManager.getConnection(url);
        Statement s = conn.createStatement();
        ResultSet rs = s.executeQuery("SELECT SCHEMANAME, TABLENAME FROM sys.sysschemas s, sys.systables t WHERE s.schemaid = t.schemaid   and t.tabletype = 'T'");

        CallableStatement cs = conn.prepareCall ("CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE(?, ?, ?)");      
        while (rs.next() ) {
            String schema = rs.getString(1);
            String table = rs.getString(2);
            System.out.println("Now compressing " + schema + " " + table);
            cs.setString(1,schema);
            cs.setString(2,table);
            cs.setShort(3, (short) 1);
            cs.execute();           
        }
        // finally shutdown the database
        try {
            DriverManager.getConnection(url +";shutdown=true");
        }
        catch (SQLException se) {
            if (se.getSQLState().equals("08006")) {
                System.out.println("Compress and shutdown complete");
            } else throw se;
        }
    }
}
  1. 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.

DatabaseConsistencyCheck (last edited 2013-11-15 17:12:36 by KatheyMarsden)