15.00 - CHECK Command - 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

CHECK Command

After the CheckTable utility is started, use the CHECK command to:

  • designate the level of checking
  • specify tables and databases to be included in or excluded from the check
  • specify other CHECK options discussed below
  • Syntax

     

    Syntax element

    Description

    ALL TABLES

    CheckTable checks all tables, join indexes, and hash indexes in all databases in the system.

    Note: CheckTable considers join and hash indexes as tables. References to tables in this chapter should be interpreted as encompassing tables, join indexes, and hash indexes.

    dbname

    CheckTable checks all tables, join indexes, and hash indexes in the specified database.

    You can use wildcard characters or wildcard syntax in specifying database names. For more information, see “Using Wildcard Characters in Names” on page 58 and “Rules for Using Wildcard Syntax” on page 60.

    EXCLUDE

    CheckTable excludes the specified tables or databases from the check.

  • CHECK ALL TABLES EXCLUDE excludes one or more databases or tables.
  • CHECK dbname EXCLUDE excludes one or more tables in a particular database.
  • If a specified object does not exist in the system, the summary report lists the object in a message.

    If you do not specify this option, CheckTable checks all data objects in the system.

    tablename

    dbname.tablename

    CheckTable checks a specific table, join index, or hash index, including global temporary tables.

    You can use wildcard characters or wildcard syntax in specifying database names. For more information, see “Using Wildcard Characters in Names” on page 58 and “Rules for Using Wildcard Syntax” on page 60.

    BUT ONLY

    BUT NOT

    Places constraints on what CheckTable checks:

  • BUT ONLY causes CheckTable to check only the subsequently specified objects.
  • BUT NOT causes CheckTable to skip checking of the subsequently specified objects.
  • If you do not specify any selection constraints, CheckTable checks all data objects in the system.

    These options are ignored for level-pendingop checks.

    Example: The following command checks all tables limiting the check to only INDEX ID=nnn at level three:

    CHECK ALL TABLES BUT ONLY INDEX ID=nnn AT LEVEL THREE;

    INDEX ID = nnn

    Specifies a specific secondary index (specified by its index ID) when using a constraint with CheckTable. In general, specify this option only when you want to check a single table.

    UNIQUE INDEXES

    Specifies all unique secondary indexes when using a constraint with CheckTable.

    NONUNIQUE INDEXES

    Specifies all Nonunique Secondary indexes when using a constraint with CheckTable.

    REFERENCE ID = nnn

    Specifies a specific index (as specified by its index ID) when using a constraint with CheckTable.

    REFERENCE INDEXES

    Specifies all reference indexes when using a constraint with CheckTable.

    DATA

    Specifies the data subtable when using a constraint with CheckTable.

    LARGE OBJECT ID = nnn

    Specifies a specific large object (as specified by its ID) when using a constraint with CheckTable.

    LOB ID = nnn

    Specifies a specific large object (as specified by its ID) when using a constraint with CheckTable.

    LARGE OBJECTS

    Specifies all large objects when using a constraint with CheckTable.

    LOBS

    Specifies all large objects when using a constraint with CheckTable.

    SJI ID =nnn

    Specifies a specific system defined join index (as specified by its index ID) when using a constraint with CheckTable.

    SJIS

    Specifies all system defined join indexes when using a constraint with CheckTable.

    AT LEVEL

  • PENDINGOP
  • ONE
  • TWO
  • THREE
  •  

    Specifies the level checking CheckTable performs. Higher levels of checking yield more thorough information, but generally require more system resources and more time. You should choose the minimum level that will provide the information you require:

  • PENDINGOP Provides a list of tables for which pending operations exist.
  • ONE isolates specific tables with errors.
  • TWO provides a detailed check of:
  • Consistency of row IDs
  • Checksum of primary and fallback rows
  • Hash codes
  • THREE provides the most diagnostic information, but uses more system resources and requires more time. Use this level of check only when necessary.
  • For more information, see “CheckTable Check Levels” on page 34.

    WITH ERROR LIMIT= nnn

    CheckTable stops checking a table if it finds nnn or more errors. If CheckTable was checking more than one table, it continues on to the next table.

    The default is 20 errors for each table checked.

    WITH NO ERROR LIMIT

    CheckTable reports all errors for each table.

    SKIPLOCKS

    CheckTable skips all locked tables automatically.

    If a table is locked, and if CheckTable cannot obtain a lock, then CheckTable indicates the table check is skipped. The summary at the end of CheckTable processing includes the total number of tables skipped.

    Without this option, CheckTable waits indefinitely on the table to be checked until it is unlocked. The ChecktableTableLockRetryLimit field in DBS Control specifies the duration, in minutes, that CheckTable, in non-concurrent mode, will retry a table check when the table is locked by another application.

    The default is 0, which indicates that in non-concurrent mode, CheckTable will retry a table check until CheckTable can access the table.

    If the ChecktableTableLockRetryLimit setting is greater than 0, then CheckTable will retry a table check within the specified limit.

    For more information, see “ChecktableTableLockRetryLimit” on page 245.

    IN SERIAL

    IN PARALLEL

    Specifies the mode of checking CheckTable uses:

  • IN SERIAL means CheckTable checks a single table at a time.
    This is the default.
  • IN PARALLEL means CheckTable checks multiple tables simultaneously. Using PARALLEL mode saves time but is resource intensive. The number of tables that CheckTable can check simultaneously in parallel depends on resource availability.
  • You can check the status of the number of parallel checks CheckTable performs at any time. For information on how to check the status, see “Determining the Status of a Table Check” on page 29.

    TABLES=n

    Optionally used with IN PARALLEL to specify the upper limit on the number of tables that will be checked simultaneously. The value of n can be any integer from two through eight.

    At level-one checking, the actual maximum number of tables that can be checked simultaneously is based on the maximum number of AMP work tasks (AWT) defined for the system. At all other levels, the maximum is based on the maximum number of AWT and on the available spool space. The TABLES=n option is used to decrease the number of tables checked in parallel to something less than the maximum.

    If there is insufficient spool space to check n tables in parallel, the number checked will be less than the number specified.

    PRIORITY =

    Specifies a priority level at which CheckTable should run. This option can be used to control resource usage and improve performance. The available priority levels are:

  • L - Performance Group Low
  • M - Performance Group Medium
    This is the default, if the PRIORITY= option is not specified.
  • H - Performance Group High
  • R - Performance Group Rush
  • Performance Group Name - any other Performance Group that has been defined. If the Performance Group name contains any special characters, enclose the name in double quotation marks. CheckTable reports and error if the specified Performance Group has not been defined.
  • CONCURRENT MODE

    Use when running on a non-quiescent system. CONCURRENT MODE reduces lock contention by optimizing the locking protocol and by automatically skipping the locked tables to retry them later. After CheckTable checks all tables, CheckTable automatically retries all tables that were skipped due to lock contention.

    When you specify the CONCURRENT MODE option, you cannot specify the IN PARALLEL option. CheckTable always checks tables serially in concurrent mode to reduce lock contention.

    CHECK ALL TABLES … CONCURRENT MODE does not check data dictionaries and the DBC database in concurrent mode. To check the DBC database and data dictionaries in concurrent mode, use CHECK DBC … CONCURRENT MODE.

    For more information on CONCURRENT MODE, see “CheckTable and System Activity” on page 31.

    RETRY LIMIT n

    The duration in minutes that CheckTable waits before attempting to re-check a table that was skipped during CONCURRENT MODE operation.

    If n is zero, CheckTable will not attempt to check tables that were skipped.

    If RETRY LIMIT is not specified, CheckTable retries the locked tables indefinitely, or until all tables have been 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. If the RETRY LIMIT has not been met, CheckTable will return to the locked table to try again.

    ERROR ONLY

    CheckTable displays only bypassed tables and tables that have errors or warnings. This option allows you to quickly identify and address problems that CheckTable finds. 

    This option is ignored during level-pendingop checks.

    DOWN ONLY

    Teradata Database can isolate some file system errors to a specific data or index subtable, or to a contiguous range of rows (“region”) in a data or index subtable. In these cases, Teradata Database marks only the affected subtable or region down. This improves system performance and availability by allowing transactions that do not require access to the down subtable or rows to proceed, without causing a database crash that would require a system restart.

    However, if several regions in a subtable are marked down, it could indicate a fundamental problem with the subtable itself. Therefore, when a threshold number of down regions is exceeded per AMP, the entire subtable is marked down on all AMPs, making it unavailable to most SQL queries. This threshold can be adjusted by means of the DBS Control utility.

    The DOWN ONLY option causes CheckTable to display status information for only those data and index subtables that have been marked down. Down status information for subtables is displayed at check levels one, two, and three. The specific rows included in down regions are listed only for check levels two and three. Down status information is not displayed for level-pendingop checks.

    COMPRESSCHECK

    CheckTable compares the compression information (compress multi-value and name of compression algorithm UDF if specified in table definition) from the table header to the corresponding information stored in the CompressValueList column of the DBC.TVFields table.

    At Level Three, compressed data subtables are checked. For example, for multi-value compressed columns, CheckTable compares the compress list in the table header to the column values in each row to ensure the appropriate values were compressed.

    This option is ignored during level-pendingop checks.

    For more information on compression, see Database Design and SQL Data Types and Literals.

    CHECKINVALIDSI

    Causes CheckTable to continue index checks, even if it encounters an invalid secondary index. If this option is not specified, CheckTable bypasses further index checks and logs a warning message when it encounters an invalid SI. By allowing CheckTable to continue index checking, this option may result in a more detailed error reported that can help determine why the index is invalid.

    CHECKTABLEB

    The CHECKTABLEB command runs the non-interactive batch mode of CheckTable. CHECKTABLEB cannot be aborted. It runs until the check process has completed.

    To run CHECKTABLEB in batch mode, use the cnsrun utility. For information on cnsrun, see Chapter 6: “CNS Run (cnsrun).”

    The syntax of CHECKTABLEB is identical to the syntax of the CHECK command.