Use the VALIDATE CHECKSUM statement to validate the checksum on each page of a database. The VALIDATE CHECKSUM statement ensures
that database pages have not been modified on disk. When a database is created with checksums enabled, a checksum is calculated
for each database page before it is written to disk. VALIDATE CHECKSUM reads each database page directly from disk—not via
the database server's cache—and calculates the checksum for each page. If the calculated checksum for a page does not match
the stored checksum for that page, an error occurs and information about the invalid page appears in the database server messages
window. The VALIDATE CHECKSUM statement is not recommended for databases that have checksums disabled because it reads the
entire database from disk.
Use the VALIDATE DATABASE statement to ensure that the free map correctly identifies pages as either allocated or free and
that no BLOBs have been orphaned. VALIDATE DATABASE also performs checksum validation and verifies that each database page
belongs to the correct object. For example, on a table page, the table ID must identify a valid table whose definition must
include the current page in its set of table pages. The VALIDATE DATABASE statement brings pages into the database server's
cache in sequential order. This results in their validation, as the database server always verifies the contents and checksums
of pages brought into the cache. If you start database validation while the database cleaner is running, the validation does
not run until the database cleaner is finished running.
The VALIDATE TABLE statement validates the specified table and all of its indexes by checking that the set of all rows and
values in the base table matches the set of rows and values contained in each index. VALIDATE TABLE also traverses all the
table's BLOBs, verifies BLOB allocation maps, and detects orphaned BLOBs. The VALIDATE TABLE statement checks the physical
structure of the table's index pages and verifies the order of the index hash values, and the index's uniqueness requirements
(if any are specified). For foreign key indexes, unless the WITH EXPRESS CHECK clause is specified, each value is looked up
in the primary key table to verify that referential integrity is intact. Because the VALIDATE TABLE statement, like VALIDATE
DATABASE, uses the database server's cache, the database server also verifies the checksums and basic validity of all pages
in use by a table and its indexes.
The VALIDATE INDEX statement performs the same operations as the VALIDATE TABLE statement except that it only validates the
specified index and its underlying table; other indexes are not checked. For foreign key indexes, unless the WITH EXPRESS
CHECK clause is specified, each value is looked up in the primary key table to verify that referential integrity is intact.
Specifying the WITH EXPRESS CHECK clause disables referential integrity checking and can therefore significantly improve performance.
If the specified index is not a foreign key index, WITH EXPRESS CHECK has no effect.
Use the VALIDATE TEXT INDEX statement to verify that the positional information for the terms in the index is intact. If the
positional information is not intact, an error is generated and you must rebuild the text index. If the text index is either
auto or manual, you can rebuild the text index by executing the REFRESH TEXT INDEX statement.
If the generated error concerns an immediate text index, you must drop the immediate index and create a new one.
Validating a table or an entire database should be performed while no connections are making changes to the database; otherwise,
errors may be reported indicating some form of database corruption even though no corruption actually exists.