PROVE IT !!If you know it then PROVE IT !! Skill Proficiency Test

Solving Partitioning and RI Validation Errors

You can use the following procedures and tools to detect and correct errors in tables that use partitioning and referencing. (For details, see “Partitioned and Nonpartitioned Primary Indexes” and “FOREIGN KEY … REFERENCES Constraints” in Database Design.)

 
 
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:
Change the partitioning expression
Delete the rows causing the problem
Remove partitioning from the table
Drop the table
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:
Regenerate table headers
Re-evaluate partition expressions
Recalculate row hash values
Move rows to proper AMPs and row partitions
Update any SI, JI, and HI defined for the table
use the ALTER TABLE … REVALIDATE WITH DELETE/INSERT[INTO] statement.
WITH DELETE deletes any rows with a partition number that is null or outside the valid range.
WITH INSERT [INTO] deletes any rows with a partition number that is null or outside the valid range and inserts them into save_table.
Note: REVALIDATE changes the table version.
reset the state of RI tables after an ARC RESTORE operation
run the ARC utility REVALIDATE REFERENCES FOR command. See Teradata Archive/Recovery Utility Reference for details.
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 DISTINCT childtablename.*
FROM childtablename,parenttablename WHEREchildtablename.fkcol NOT IN (SELECT pkcol FROMparenttablename)
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.

Add a Comment

Your email address will not be published. Required fields are marked *