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 and Using Wildcard Characters in Names.
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.
CHECK AGAIN Command Restrictions
Each CHECK command generates an error file containing a list of the significant errors. Use the CHECK AGAIN command to check only the (failed) tables listed in an error file. You can also specify an input error file or let CheckTable default the input error file to the output error file generated by the previous CHECK command.
If Error-filename is not specified, the last error file generated by the previous CHECK command in the current CheckTable instance is used as input.
Error-filename may be a fully qualified filename (such as /var/tmp/CheckTableErrors20130503164640) or a relative path filename (for example, CheckTableErrors20130503164640).
For a relative path name, the root directory is the error file directory, which is managed with the ERRORFILEDIR command.
Determining the Status of a Table Check
To determine the status of the current table check type status at the CheckTable command prompt. For more information, see STATUS.
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 1.4 minute(s).
These results indicate that CheckTable started five parallel checks:
|
| 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. For more information, see ABORT TABLE.
To stop the check of all tables, type abort at the CheckTable command prompt. For more information, see ABORT.
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. For more information, see HELP.
CheckTable and System Activity
- 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.
- 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.
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 Ferret Utility (ferret).
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 more information on level-one checking, see Level One Checks.
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 canceled.
For foreign tables (tables created to analyze data in external object storage, such as AWS S3 data), CheckTable checks that table headers exist and that the Data Dictionary information for such tables is consistent. The COMPRESSCHECK and CHECKINVALIDSI options do not apply to foreign tables. For foreign tables, the check level is irrelevant; all levels perform the same checks.
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 Teradata Vantage™ - SQL Fundamentals, B035-1141 and Teradata Vantage™ - Database Design, B035-1094.