17.10 - Check Levels - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Utilities

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Configuration
Publication ID
B035-1102-171K
Language
English (United States)

CheckTable offers several levels of data integrity checking. Each successive level performs most or all of the checks from the lower levels, and performs additional checks that are more thorough. For example, level-two checking performs checks similar to those from the pendingop and first level, and adds additional checks. Higher check levels are generally more resource intensive, and require more time.

At each level of checking, CheckTable inspects specific internal data structures. If CheckTable detects errors during these checks, it displays error messages that describe the nature of the errors. The message may be followed by additional information to show the location of the problem: AMP, subtable (primary data, fallback data, or index), and row or range of rows, if applicable.

For more information about specific CheckTable error messages by number, see Teradata Vantage™ - Database Messages, B035-1096. For more information on the Hilbert R-tree structure used by geospatial indexes, see Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142.

The following table indicates the specific internal data structures that are checked by each type of level check, and suggests when to use each level.

Check Level Internal Data Structures Checked When to use this level
Pendingop
  • Data dictionary (if database DBC is checked)
  • Table dictionary
  • Table header
Use pendingop checking to check for tables with the following pending operations:
  • FastLoad
  • MultiLoad
  • Restore
  • Rebuild
  • Replicate copy
  • System reconfiguration
One
  • Data dictionary (if database DBC is checked)
  • Table dictionary
  • Table header
  • Obsolete subtables
  • Unique secondary indexes
  • Nonunique secondary indexes
  • ParentCount
  • ChildCount
  • Subtable of a given table
  • Base global temporary tables
  • Data subtables
  • Large object subtables
  • Reference indexes
  • System-defined join indexes (SJIs) for temporal tables with temporal unique constraints
Use level-one checking only to isolate specific tables with errors. Then perform level-two or level-three checks on those specific tables.
When level-one checks use the DOWN ONLY option, the CheckTable results show only the subtables that have been marked as down.
Two
  • Data subtables
  • Large object subtables
  • Unique secondary indexes
  • Nonunique secondary indexes
  • Reference indexes
  • System-defined join indexes (SJIs) for temporal tables with temporal unique constraints
Use level-two checking when checks by level one fail, and you require a detailed checking of consistency of row IDs, the checksum of primary and fallback rows, and hash codes.
When level-two checks use the DOWN ONLY option, the CheckTable results show only the subtables and regions (ranges of rows in subtables) that have been marked down.
Three
  • Data subtables
  • Large object subtables (same checking as for Level Two)
  • Unique secondary indexes
  • Nonunique secondary indexes
  • Reference indexes
  • System-defined join indexes (SJIs) for temporal tables with temporal unique constraints
Use level-three checking rarely and only for specific diagnostic purposes, such as when an AMP is down.
When level-three checks use the DOWN ONLY option, the CheckTable results show only the subtables and regions (ranges of rows in subtables) that have been marked down.
Databases and tables within databases are checked in alphabetical order, with the exception of CHECK AGAIN. For table recheck processing, the table order is determined by the order of the table appearance within the error file.