SYSCS_UTIL.SYSCS_CHECK_TABLE system function

The SYSCS_UTIL.SYSCS_CHECK_TABLE function checks the specified table, ensuring that all of its indexes are consistent with the base table.

If the table and indexes are consistent, the method returns a SMALLINT with value 1. If the table and indexes are inconsistent, the function will throw an exception.

It is recommended that you run SYSCS_UTIL.SYSCS_CHECK_TABLE on the tables in a database offline after you back it up. Do not discard the previous backup until you have verified the consistency of the current one. Otherwise, check consistency only if there are indications that such a check is needed (for example, if you experience hardware or operating system failure), because a consistency check can take a long time on a large database. See "Checking database consistency" in the Derby Server and Administration Guide for more information.

Syntax

SMALLINT SYSCS_UTIL.SYSCS_CHECK_TABLE(IN SCHEMANAME VARCHAR(128),
IN TABLENAME VARCHAR(128))

An error will occur if either SCHEMANAME or TABLENAME are null.

Execute privileges

If authentication and SQL authorization are both enabled, only the database owner has execute privileges on this function by default. See "Enabling user authentication" and "Setting the SQL standard authorization mode" in the Derby Developer's Guide for more information. The database owner can grant access to other users.

Examples

Check a single table:

VALUES SYSCS_UTIL.SYSCS_CHECK_TABLE('SALES', 'ORDERS');

Check all tables:

SELECT schemaname, tablename, 
    SYSCS_UTIL.SYSCS_CHECK_TABLE(schemaname, tablename) 
  FROM sys.sysschemas s, sys.systables t 
  WHERE s.schemaid = t.schemaid;