17.10 - CheckTable and System Activity - 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 can be run on both quiescent and non-quiescent systems. Before running CheckTable, consider the following:
  • Table integrity validation requires that tables do not change during the CheckTable diagnostics. Consequently, CheckTable places a READ lock on each table as that table is being checked. You cannot perform an INSERT while CheckTable verifies consistency between the primary and fallback rows of a table.
  • CheckTable places a READ lock on DBC.TVM momentarily to check for the existence of the table being checked. This lock might cause a problem when you create or modify tables, views, or macros, which require a WRITE lock on DBC.TVM.
  • In some cases, CheckTable cannot perform a complete validation of table integrity if one or more AMPs are down.
To run CheckTable when users are logged on, and the system is non-quiescent, use the following CHECK options:
  • PRIORITY

    Sometimes CheckTable places a substantial resource demand (for example, CPU cycles, disk access, and spool space) on the system, degrading performance significantly for users accessing the system. By default, CheckTable performs table checking at MEDIUM priority.

    To control the job scheduling based on the expected system workload and to improve performance, use the PRIORITY option.

  • CONCURRENT MODE

    This option reduces lock contention by optimizing the locking protocol and checking tables serially.

    CONCURRENT MODE allows CheckTable to run to completion, and helps prevent deadlocks on non-quiescent systems. The locking protocol used by concurrent mode is optimized to minimize the number of locks required, and to use less restrictive lock types as much as possible. However, some locks are still required to avoid reporting false errors due to in-progress transactions.

    While lock contention is minimized, some blocking is still expected on an active system. For example, a read lock is placed on a table while it is being checked. Therefore, update operations to the table will be blocked until the table check is complete.

    The CONCURRENT MODE RETRY LIMIT option skips all locked tables and retries these locked tables after CheckTable finishes checking all specified tables not locked by other sessions.

    IF RETRY LIMIT is … THEN CheckTable …
    not specified keeps retrying the locked tables forever or until all tables are checked successfully.

    When trying to access a locked table, CheckTable waits a maximum of five minutes for the lock to be released. If the lock is not released in that time, CheckTable moves on to the next table. CheckTable will return to the locked table to try again.

    greater than 0 will continue to retry until the RETRY LIMIT is reached.
    equal to 0 will not retry skipped tables.
    negative displays an error message.
On non-quiescent systems and on quiescent systems with logons enabled, CheckTable defaults to CONCURRENT MODE with RETRY LIMIT set to one. Under these conditions, Data Dictionary checks are skipped.