15.00 - CheckTable Usage Notes - Teradata Database

Teradata Database Utilities

Product
Teradata Database
Release Number
15.00
Content Type
Configuration
Publication ID
B035-1102-015K
Language
English (United States)
Last Update
2018-09-25

CheckTable Usage Notes

CHECK Command Restrictions

The CHECK command, including database or table names, cannot exceed 1300 characters. Wildcard characters are counted as single characters. For more information see “Valid Characters in Object Names” on page 55 and “Using Wildcard Characters in Names” on page 58.

The CHECK command is not case sensitive. You can specify the syntax elements in uppercase or lowercase or a mixture of both. This also applies to names specified in wildcard syntax.

CheckTable can check an unlimited number of databases and tables, however the CHECK command accepts a maximum of 30 names on the command line. Names of the form dbname, tablename, and dbname.tablename are each considered to be a single name. However, wildcard expressions are also considered to be single names, and can resolve to any number of databases and tables. For more information on using wildcard expressions, see “Using Wildcard Characters in Names” on page 58.

Determining the Status of a Table Check

To determine the status of the current table check type status at the CheckTable command prompt.

For example, assume that the table check in progress specifies database DB0 with tables t1, t10, t100, t1000, and t11. The following table shows the results for Parallel and Serial modes.

 

Mode

Result Example

PARALLEL


     >>> STATUS: CheckTable running in PARALLEL mode.
                 5 CheckTable tasks started.
                 4 CheckTable tasks ACTIVE.
                 1 CheckTable tasks IDLE.
                 1000005  bytes spool space in use.
                     120  bytes spool space available.
   Task   Status
   ****   **********************************
    1     Checking data subtable ("DB0"."T1").
    2     Checking data subtable ("DB0"."T10").
    3     Checking data subtable ("DB0"."T100").
    4     Waiting for a read lock on table
          ("DB0"."T1000"). 0 lock retry(s), total
           wait time of 0.0e+00 minute(s).

These results indicate that CheckTable started five parallel checks:

  • Three CheckTable tasks are checking the data subtable of tables DB0.T1, DB0.T10, and DB0.T100.
  • One CheckTable task is waiting for a read lock on table DB0.T1000.
  • One CheckTable task is paused because of insufficient resources.
  • SERIAL

    >>>> STATUS: Checking data subtable ("DB0"."T10").

    This indicates that CheckTable is checking the data subtable of the table DB0.T10.

    Stopping Table Checks

    To stop the check of the current single table (in SERIAL mode) or current group of tables being checked (in PARALLEL mode) type abort table at the CheckTable command prompt.

    To stop the check of all tables Type abort at the CheckTable command prompt.

    Logging CheckTable Runs

    CheckTable logs the start and finish times for CHECK runs to the DBC. SW_Event_Log table and to the operating system event log. The logged start and finish times allows calculation of duration of CheckTable run, and prediction of duration of future CheckTable runs.

    Log entries can be viewed by querying Teradata Database, or viewing the operating system event logs. The event log is the messages file located in the /var/log directory.

    CheckTable Help

    To access the CheckTable menu-driven, hierarchical help system, press the F7 key. Use the function keys to navigate within the help system.

    To display the complete text of the help system, type help; at the CheckTable command prompt.

    CheckTable and System Activity

    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 pseudo-table locks and 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.

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

    The Data Dictionary tables are checked only if the check specifies ALL TABLES (non-concurrent mode) or explicitly specifies to check database DBC. In this case, Teradata Database must be quiescent with logons disabled.

    Checking Data Dictionary Tables

    The Data Dictionary tables are checked only if the check specifies ALL TABLES (non-concurrent mode) or explicitly specifies to check database DBC. In this case, Teradata Database must be quiescent with logons disabled.

    Data Dictionary processing defaults to batch scan mode for performance reasons. If a dictionary error occurs, processing automatically switches to serial scan mode for its more comprehensive error handling functionality. However, serial scan mode is slower, and on large systems can take significant time.

    Use the STATUS command to check on the progress of the Data Dictionary scan.

    Use the ABORT command to abort the Data Dictionary scan along with the CHECK command itself. To skip the Data Dictionary scan, issue the CHECK command while logons are enabled.

    File Corruption and CheckTable

    CheckTable ensures that file system data structures are consistent. If CheckTable experiences any file corruption problem while checking the tables and databases, the table check in progress is aborted, and CheckTable displays an error message.

    To find the error, run the SCANDISK command after CheckTable finishes checking the remaining tables. For information on SCANDISK, see Chapter 13: “Ferret Utility (ferret)” and Filer in Support Utilities.

    Out-Of-Order Rows

    CheckTable treats rows that are out-of-order as if they do not exist, and continues looking for the next row in the sequence. This can cause CheckTable to report out-of-order errors for subsequent rows, even if only a single row was out of order.

    For example, given the sequence: 1, 2, 4, 6, 8, 20, 9, 10, 11, 12, 17, 18, 21, 25, CheckTable would see 9 as being out of ascending sequence. CheckTable would therefore ignore the 9, then continue looking for a row to follow 20. In this case, although 20 is likely the only row that is out of order, CheckTable would report errors for rows 9 through 18.

    Special Tables and Exceptions

    CheckTable only checks the table headers of base global temporary tables and their instances; data rows of instances are not checked during level-one checking. For detailed information on level-one checking, see “Level One Checks” on page 36.

    Stored procedures, UDFs, and UDMs, store their text and object code internally as special tables that are not normally accessible to users. Join index rows, and hash index rows are also stored internally as tables. CheckTable checks all of these tables, in addition to checking ordinary data tables.

    Some checks are not done for unhashed tables, and certain join indexes and hash indexes.

    CheckTable does not include the DBC.ALL table when accounting for tables checked because it is not considered a conventional table.

    CheckTable does not check tables whose rollback has been cancelled.

    Referential Integrity Terminology

    The following table defines the Referential Integrity terminology that CheckTable uses.

     

    Term

    Definition

    Primary Key

    One or more columns that uniquely identify a table row.

    Foreign Key

    One or more columns in a table (the child table) that references the Primary Key in one or more tables (the parent tables).

    Parent Table

    Table having a Primary Key that is referenced by another table, the child table.

    Child Table

    Table having a Foreign Key that references the Primary Key in another table, the parent table.

    For more information on referential integrity, see SQL Fundamentals and Database Design.