Use the following procedures and tools to detect and correct errors in tables that use partitioning and referencing. (For more information, see Teradata Vantage™ - Database Design, B035-1094.)
IF you want to … | THEN … |
---|---|
correct a partitioning expression that is causing transaction rollbacks due to an evaluation error (such as divide by zero) | do one of the following:
|
find invalid table states or internal structures | run the CheckTable utility LEVEL 3 command. |
regenerate only the headers in a table with partitioning | use the ALTER TABLE ... REVALIDATE statement. |
validate a column-partitioned table or join index | use the REVALIDATE option of the ALTER TABLE request. |
for a table with partitioning:
|
use the ALTER TABLE ... REVALIDATE WITH DELETE/INSERT [INTO] statement.
REVALIDATE changes the table version.
|
find corrupt rows after running an update or delete operation using WITH NO CHECK OPTION on tables with RI constraints | submit the RI Validation Query, structured as:SELECT DISTINCTchildtablename .* FROM childtablename,parenttablename WHERE childtablename .fkcol NOT IN (SELECT pkcol FROM parenttablename ) AND childtablename .fkcol IS NOT NULL; This query reports every row in the Child table with an FK value that does not have a matching PK value. (FK nulls are excluded because it is not possible to determine the values they represent.) |
purify a Child table for which corrupt rows were reported by the RI Validation Query | delete from the Child table any reported rows as soon as possible to maintain the integrity of your database. |