Partitioning and RI Validation Errors | Teradata Vantage - Solving Partitioning and RI Validation Errors - Analytics Database - Teradata Vantage

Database Administration

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-11-03
dita:mapPath
pgf1628096104492.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
ujp1472240543947
lifecycle
latest
Product Category
Teradata Vantage™

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:
  • 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.
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.