Syntax - Analytics Database - Teradata Vantage

CHECK check_spec
  [ BUT { ONLY | NOT } check_object [,...] ] AT LEVEL
  { PENDINGOP | ONE | TWO | THREE }
  [ WITH ERROR LIMIT = [ nnn ] | WITH NO ERROR LIMIT ]
  [ SKIPLOCKS ]
  [ IN SERIAL | IN PARALLEL [ TABLES = n ] [ MAXSESSIONS ] ]
  [ PRIORITY = { L | M | H | R | performance_group_name } ]
  [ CONCURRENT MODE [ RETRY LIMIT n ] ]
  [ ERROR DOWN | DOWN ONLY ]
  [ COMPRESSCHECK ]
  [ CHECKINVALIDS ]
  [ SCOPE { DICTIONARY | USER | ALL } ] ;

check_spec

{ ALL TABLES [ EXCLUDE [dbname.]table_name [,...] ] |
  dbname EXCLUDE [dbname.]table_name [,...] |
  [dbname.]table_name [,...] |  
  AGAIN [ Error-filename ]
}

check_object

{ INDEX ID = nnn |
  UNIQUE INDEXES |
  NONUNIQUE INDEXES |
  REFERENCE ID = nnn |
  REFERENCE INDEXES |
  DATA |
  LARGE OBJECT ID = nnn |
  LOB ID = nnn |
  LARGE OBJECTS |
  LOBS |
  SJI ID = nnn |
  SJIS
}

Syntax Elements

ALL TABLES

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

CheckTable considers join and hash indexes as tables. References to tables in this discussion 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.

AGAIN

Re-checks tables that failed the previous check.

Error-filename

Error-filename is a Linux filename.

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 or 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.

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 reference 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

Can be one of PENDINGOP, ONE, TWO, or THREE:

• 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 Check Levels.

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 in non-concurrent mode, CheckTable may wait indefinitely on a table lock, depending on the value of the ChecktableTableLockRetryLimit field in DBS Control. The ChecktableTableLockRetryLimit field 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.

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.

MAXSESSIONS

Runs user table check with the maximum number of parallel sessions.

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 the priority at which CheckTable should run. This option can be used to control resource usage and improve performance. The available priority levels are:

• L - Low
• M - Medium

  This is the default, if the PRIORITY= option is not specified.

• H - High
• R - Rush

These values are mapped to specific workloads. Default mappings exist for L, M, H, and R, but can be changed in the Workload Designer Viewpoint portlet. For more information on the Workload Designer portlet, see Teradata® Viewpoint User Guide, B035-2206.

• workload name - a specific workload in which CheckTable should run. If the specified workload does not exist, CheckTable runs in the workload mapped to CheckTable in Workload Designer.

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.

In a non-quiescent system with logons enabled or disabled, or on a quiescent system with logons enabled, CheckTable overrides a user-specified command and enforces CONCURRENT MODE with RETRY LIMIT=1. In a normal running environment, CONCURRENT MODE is used by default.

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 in concurrent mode, use CHECK DBC CONCURRENT MODE.

For more information on CONCURRENT MODE, see Determining the Status of a Table Check.

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

Vantage 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, it 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 failure that requires 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 MaxDownRegions field in the General fields group 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 Teradata Vantage™ - Database Design, B035-1094 and Teradata Vantage™ - Data Types and Literals, B035-1143.

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.

SCOPE

Use with one of the following:

• DICTIONARY: Runs only a dictionary check with batch mode error logging for the given CheckTable command.
• USER: Runs only a user table check for the given CheckTable command.
• ALL: Runs a dictionary check in batch mode with error logging and user table check for the given CheckTable command.