CHECK Command Examples - Advanced SQL Engine - Teradata Database

Database Utilities

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
June 2020
Language
English (United States)
Last Update
2020-06-15
dita:mapPath
boh1556732696163.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1102
lifecycle
previous
Product Category
Teradata Vantage™

This section shows some examples using the options of the CHECK command.

Example: CHECK ALL TABLES EXCLUDE …

To exclude one or more databases or tables from the check, use CHECK ALL TABLES EXCLUDE. If a specified object does not exist in the system, then the object appears in a message at the end of the summary report.

CheckTable does the following:
  • Checks the dictionary and database DBC.

    (If database DBC is in the EXCLUDE list, it is not checked.)

  • Checks other non-excluded databases in database-name order

The following table shows different CHECK ALL TABLES EXCLUDE … examples based on database DBC at level-one checking.

IF you want to check … THEN type …
the Data Dictionary and all databases except DBC, SalesDB1, and PurchaseDB1 CHECK ALL TABLES EXCLUDE DBC, SalesDB1, PurchaseDB1 AT LEVEL ONE;
only the Data Dictionary one of the following:
  • CHECK ALL TABLES EXCLUDE % AT LEVEL ONE;
  • CHECK ALL TABLES EXCLUDE %.% AT LEVEL ONE;
all tables except those in db1 and t2 and t4 of db2 CHECK ALL TABLES EXCLUDE db1,db2.t2, db2.t4 AT LEVEL THREE;
all tables except those in database1 with table names beginning with Sales CHECK ALL TABLES EXCLUDE database1.Sales% AT LEVEL ONE;

Example: CHECK ALL TABLES AT LEVEL PENDINGOP

To check all tables to determine if they have any pending operations, type the following:

CHECK ALL TABLES AT LEVEL PENDINGOP;

The following output appears:

Check beginning at 11:13:31 11/06/26.

Data dictionary check started at 11:13:31 17/06/26.

...

Table: "PROD"."CUSTOMERS" starting at 11:14:22 17/06/26.
  Map No 1025, Table id 0000H 624BH, Database id 0000H 03FCH, No fallback.
  Table check bypassed due to pending MultiLoad.
  Table Result: Skipped
Table: "PROD"."SHIPMENT" starting at 11:14:24 17/06/26.
  Map No 1025, Table id 0000H 04E1H, No fallback.
  Table check bypassed due to pending Table Rebuild.
...

Summary: 
1,435 table(s) checked.
  420 fallback table(s) checked.
1,015 non-fallback table(s) checked.

    1 table(s) bypassed due to pending MultiLoad.
    1 table(s) bypassed due to pending Table Rebuild.
    0 table(s) failed the check.
    0 Dictionary error(s) were found.

Check completed at 11:14:58 17/06/26.

Example: CHECK dbname EXCLUDE …

To check all tables in a specified database, except for those listed after EXCLUDE, use

CHECK dbname EXCLUDE ...

You can use wildcards in the EXCLUDE list. For example:

check all tables exclude mydb% at level two

will exclude database having names starting with mydb.

The following table shows different CHECK dbname EXCLUDE examples at level-one checking.

IF you want to … THEN type …
check database dbname1 except for tables t1, t2, t3 CHECK dbname1 EXCLUDE t1, t2, t3 AT LEVEL ONE;
check database dbname2 except for tables beginning with the word table and followed by any single character as well as tables containing the string weekly CHECK dbname2 EXCLUDE table?, %weekly% AT LEVEL ONE;
exclude any tables whose names either begin with week1 and end with any character or begin with the word month in the SalesDB database CHECK SalesDB EXCLUDE week1?, month% AT LEVEL ONE;

For more information, see Using Wildcard Characters in Names. If a specified table includes a dbname, then CheckTable only checks the table in that referenced database.

Example: NO ERROR LIMIT

To perform level-two checking with no error limits on the MfgDb database, type the following:

CHECK MfgDb AT LEVEL TWO WITH NO ERROR LIMIT;

Output similar to the following appears.

Check beginning at 13:08:03 17/04/26.
Table: "MFGDB"."INVENTORY" starting at 13:08:06 17/04/26.
  Map No 1025, Table id 0000H 0C31H, Database id 0000H 03FCH, Fallback.
  Table result: No errors.
Table: "MFGDB"."PARTS" starting at 13:08:07 17/04/26.
  Map No 1025, Table id 0000H 0C33H, Database id 0000H 03FCH, No fallback.
      2753: Primary data row is on wrong AMP.
            AMP 00000
            Row id 0000H 79B6H 9E37H 0000H 0001H
            Expected primary AMP 00001
  Table result: Error(s) reported.
Table: "MFGDB"."RETURNS_TEMP" starting at 13:08:08 17/04/26.
  Map No 1025, Table id 0000H 0C32H, Fallback.
  No errors reported.
Summary:
    3 table(s) checked.
    2 fallback table(s) checked.
    1 non-fallback table(s) checked.
    1 table(s) failed the check.
    0 Dictionary error(s) were found.
Check completed at 13:08:09 17/04/26.

In the above output, the first line of the output is the header, which is displayed before the first table is checked. The header shows the exact time and date that you started the check in the following format:

Check beginning at HH:MM:SS YY/MM/DD

If one or more AMPs are unavailable, then the following message appears:

The following AMPs are not operational.  As a result, certain checks will not be complete:
 nnnnn   nnnnn... ...

Unavailable AMPs are indicated by the format nnnnn.

The next set of lines displayed indicates the database name, table name, map number, internal ID, database ID, fallback status, and the date and time:

Table: “dbname”.“tablename” starting at YY/MM/DD HH:MM:SS
Map No nnnn, Table ID  nnnnH  nnnnH, Database id nnnnH  nnnnH, {Fallback | No fallback}
When the checking process begins and CheckTable detects inconsistencies, CheckTable displays specific messages that pertain to those inconsistencies. CheckTable checks tables in alphabetical order by database name and table name. If you specify to check DBC, then CheckTable always checks DBC before the named database and table. CHECK AGAIN IS AN exception to alphabetical table ordering. In table recheck processing, the table order is determined by the order of the table appearance within the error file.

Example: SKIPLOCKS

CheckTable automatically skips any tables when it tries to lock them and finds that they are locked already. For example, suppose you type the following:

CHECK fb3.t1 at level two SKIPLOCKS IN PARALLEL;

If fb3.t1 is locked when you submit the command, then the following output appears:

Check Beginning at 07:57:25 17/12/30
Table: "FB3"."T1" skipped at 07:57:26 17/12/30 due to pending lock.
  Map No 1025, Table id 0000H 3E9H, Database id 0000H 041BH.
Summary:
    0 table(s) checked.
    0 fallback table(s) checked.
    0 non-fallback table(s) checked.
    1 table(s) skipped due to pending lock.
    0 table(s) failed the check.
    0 Dictionary error(s) were found.

Skiplocks applies only to non-concurrent mode lock handling.

CheckTable enforces CONCURRENT MODE with RETRY LIMIT=1. If CheckTable cannot get a lock on a table, it attempts one retry after the rest of the tables are checked.

Example: SERIAL/PARALLEL

Serial mode allows CheckTable to check a single table at a time. Parallel mode allows CheckTable to check multiple tables simultaneously. The following tables shows examples of both modes.

IF you want to check database DB0 with tables t1, t10, t100, t1000, and t11 at level TWO in … THEN type …
SERIAL mode CHECK db0.t1% at level two IN SERIAL;
PARALLEL mode CHECK db0.t1% at level two IN PARALLEL;

Example: Serial Mode Output

Tables are checked one at a time and all table messages are grouped together under one table message.

Table: "DBO1"."T1" starting at 17:08:06 11/04/26.
  Map No 1025, Table id 0000H 0C4EH, Database id 0000H 041BH,
  Header missing on 1 AMP:
  00000
  Further checking skipped because of missing table header(s).
  Table result: Error(s) reported.

When processing completes, the following termination message or summary of the check results appears:

Summary:
    5 table(s) checked.
    3 fallback table(s) checked.
    2 non-fallback table(s) checked.
    3 tables failed check.
    0 Dictionary error(s) were found.
Check completed at 7:57:24 17/12/30.
The entire output is not shown.

Example: Parallel Mode Output

Tables are concurrently checked by multiple worker tasks. Table messages display separately with no message grouping and may appear in three types of table messages: starting, checking, and ending. Consequently, these messages may display with other messages for other tables due to concurrent table checking.

The database name and the table name precede the report for each message to help you identify the table to which the output message belongs:

Check beginning at 07:57:14 17/12/30.
Table: "DB0"."T1" starting at 07:57:15 17/12/30.
  Map No 1025, Table id 0000H 3F92H, Database id 0000H 041BH, Fallback.
Table:"DB0"."T10" starting at 07:57:15 17/12/30.
  Map No 1025, Table id 0000H 0D14H, Database id 0000H 041BH, Fallback.
Table:"DB0"."T100" starting at 07:57:15 17/12/30.
  Map No 1025, Table id 0000H 0D6EH, Database id 0000H 041BH, Fallback.
Table:"DB0"."T1000" starting at 07:57:15 17/12/30.
  Map No 1025, Table id 0000H 12E6H, Database id 0000H 041BH, Fallback.
Table: "DB0"."T10" checking at 07:57:15 17/12/30.
  Map No 1025, Table id 0000H 0D6EH, Database id 0000H 041BH, Fallback.
      2741: Table header not found.
            Table id 0000H 0D14H
            Header missing on 1 AMP:
            00000
            Further checking skipped because of missing header(s).
Table: "DB0"."T10" ending at 07:57:15 17/12/30.
  Map No 1025, Table id 0000H 0D6EH, Database id 0000H 041BH, Fallback.
  Table Result:  No errors
Table: "DB0"."T1" checking at 07:57:15 17/12/30.
  Map No 1025, Table id 0000H 3F92H, Database id 0000H 041BH, Fallback.
      2757: Primary data row is missing.
            Fallback AMP 00001, Fallback subtable 2048
            Row id 0000H 8C49H CDABH 0000H 0001H
            Expected primary AMP 00000
Table: "DB0"."T111" starting at 07:57:16 17/12/30.
  Map No 1025, Table id 0000H 12E8H, Database id 0000H 041BH, Fallback.
Table: "DB0"."T1" checking at 07:57:17 17/12/30.
  Map No 1025, Table id 0000H 3F92H, Database id 0000H 041BH, Fallback.
      2757: Primary data row is missing.
            Fallback AMP 00001, Fallback subtable 2048
            Row id 0000H BD81H 0459H 0000H 0001H
            Expected primary AMP 00000
Table: "DB0"."T100" checking at 07:57:17 17/12/30.
  Map No 1025, Table id 0000H 0D6EH, Database id 0000H 041BH, Fallback.
      2757: Primary data row is missing.
            Fallback AMP 00003, Fallback subtable 2048
            Row id 0000H 1897H 9B57H 0000H 0001H
            Expected primary AMP 00002
Table: "DB0"."T1000" ending at 07:57:18 17/12/30.
  Map No 1025, Table id 0000H 12E6H, Database id 0000H 041BH, Fallback.
  Table Result:  No errors
Table: "DB0"."T1" checking at 07:57:18 17/12/30.
  Map No 1025, Table id 0000H 3F92H, Database id 0000H 041BH, Fallback.
      2880: Reference index row indexes non existent data row.
            AMP 00000, Primary subtable
            Reference index id 0
            Reference index row id 0000H 8C49H CDABH 0000H 0001H
            Reference index row count exceeds data row count by 1
Table: "DB0"."T1" checking at 07:57:18 17/12/30.
  Map No 1025, Table id 0000H 3F92H, Database id 0000H 041BH, Fallback.
      2888: Invalid reference index row.
            AMP 00002, Primary subtable
            Reference index id 0
            Reference index row id 0000H 1897H 9B57H 0000H 0001H
Table: "DB0"."T100" checking at 07:57:18 17/12/30.
  Map No 1025, Table id 0000H 0D6EH, Database id 0000H 041BH, Fallback.
      2757: Primary data row is missing.
            Fallback AMP 00003, Fallback subtable 2048
            Row id 0000H 3133H 36AEH 0000H 0001H
            Expected primary AMP 00002
Table: "DB0"."T100" ending at 07:57:19 17/12/30.
  Map No 1025, Table id 0000H 0D6EH, Database id 0000H 041BH, Fallback.
  Table Result:  Error(s) reported.
Table: "DB0"."T1" checking at 07:57:19 17/12/30.
  Map No 1025, Table id 0000H 3F92H, Database id 0000H 041BH, Fallback.
  AMP 00002, Primary subtable
             Reference index id 0
             Reference index row id 0000H 3133H 36AEH 0000H 0001H

Example: TABLES=n Clause

The following example uses the TABLES=n clause option to specify the number of tables to check simultaneously in parallel mode.

check all tables at level one in parallel tables=3;
Check beginning at 10:55:23 17/01/06.
Data dictionary check started at 10:55:23 17/01/06.
F2
>>>> Status: CheckTable running in PARALLEL mode.
      3 CheckTable tasks started.
      3 CheckTable tasks ACTIVE.
      0 CheckTable tasks IDLE.
Task  STATUS 

Example: PRIORITY

A priority level controls resource usage and improves performance. To run CheckTable in the High timeshare workload group (H-WD), type the following:

CHECK db3 at level two priority=H;

For the PRIORITY option, the behavior depends on whether TASM is enabled.

To run CheckTable in PARALLEL mode in a workload named WDAdminM, type the following:

CHECK eb3, db3, fb3.t1 at level two IN PARALLEL PRIORITY='WDAdminM';

Assume the workload name specified for PRIORITY in the following example is invalid:

CHECK eb3, db3, fb3.t1 at level two IN PARALLEL PRIORITY='WDSdminM';

If TASM is not enabled, CheckTable displays an error message indicating that the priority setting is invalid, and waits for the next command.

If TASM is enabled and you specify an invalid workload name, CheckTable uses the default workload mapping, specified in the Workload Designer portlet, for example, workload WD-Default.

Example: Concurrent mode and retry limit

Because CheckTable overrides CONCURRENT mode with Retry Limit=1, the first try and one re-try are shown for a locked table. To run CheckTable in CONCURRENT mode with Retry Limit=1:

check rfc66706.table_1 at level three;

The following appears:

Table: "RFC66706"."TABLE_1" starting at 16:15:32 17/02/09.
  Map No 1025, Table id 0000H 04D3H, Database id 0000H 041BH, Fallback.
  Table check skipped at 16:15:32 17/02/09 due to pending lock.

Table: "RFC66706"."TABLE_1" starting at 16:16:32 17/02/09.
  Map No 1025, Table id 0000H 04D3H, Database id 0000H 041BH, Fallback.
  Table check skipped again at 16:16:32 17/02/09 due to pending lock.
  Table Result: Skipped

Example: Checking table headers

To check all table headers and all tables in DBC, type the following:

check dbc at level one;

Checking table headers requires a table read lock on DBC.TVM. For each table in DBC, CheckTable will obtain a table read lock, check the table, and release the table lock. These locks will probably block DDL operations. However, the duration of DBC check should be short.

Example: Excluding a database from being checked

To check all tables in all databases excluding DBC, type the following:

check all tables exclude dbc at level one;

A table access lock on DBC.DBASE is obtained for a short time to get a list of databases. This access lock should cause minimal contention. For each database, a table access lock on DBC.TVM is obtained for a short time to get a list of tables in the current database. This access lock should also cause minimal contention.

For each table in the current database, CheckTable will obtain a table read lock, check the table, and release the table lock. Any operation that requires either a write lock or exclusive lock on the table being checked will be blocked. The locking duration may be longer for a large table. An EXCLUDE clause can be used to skip large tables that are actively modified to avoid blocking.

Example: Reporting down subtables and regions (DOWN ONLY)

Level-one checks report down subtables and down regions, but do not define the region that is down.

check fiu.onedr at level one down only;
Check beginning.
Table: "FIU"."ONEDR" checking.
  Map No 1025, Table id 0000H 06A1H, Database id 0000H 041BH. Fallback.
      9131:Check was skipped due to detection of error <9130>.
           AMP 00000, Primary Data Subtable 1024 has down region marked.
  Table Result: Error(s) reported
Summary:
    1 table(s) checked.
    1 fallback table(s) checked.
    0 non-fallback table(s) checked.
    1 table(s) skipped due to presence of down regions and/or down subtables.

Example: Reporting start and end of down region

Level-two and level-three checks specify the starting and ending rows that define the down region.

check fiu.onedr at level three down only;
Check beginning.
Table: "FIU"."ONEDR" checking.
  Map No 1025, Table id 0000H 06A1H, Database id 0000H 041BH, Fallback.
      9131:Check was skipped due to detection of error <9130>.
           AMP 00000, Primary Data Subtable 1024 has down region marked.
           Region #1
             Start Row Id 0000000000000000 CDC4H F1BBH 0000H 0001H
             End Row Id   0000000000000000 DA53H B54CH 0000H 0000H
  Table Result: Error(s) reported
Summary:
    1 table(s) checked.
    1 fallback table(s) checked.
    0 non-fallback table(s) checked.
    1 table(s) skipped due to presence of down regions and/or down subtables.

Example: Marking AMPs as down due to excessive down regions

If the number of down regions in a subtable exceeds the threshold defined by the DBS Control setting MaxDownRegions, the subtable is marked down on all AMPs, and CheckTable reports the subtable down. For the following example, assume the subtable on AMP 0 had a number of down regions exceeding the limit set by MaxDownRegions.

check fiu.onedt at level two down only;
Check beginning.
Table: "FIU"."ONEDT" checking.
  Map No 1025, Table id 0000H 06A2H, Database id 0000H 041BH, Fallback.
      9131:Check was skipped due to detection of error <9129>.
           AMP 00001, Primary Data Subtable 1024 is marked down.
           AMP 00000, Primary Data Subtable 1024 is marked down.
  Table Result: Error(s) reported
Summary:
    1 table(s) checked.
    1 fallback table(s) checked.
    0 non-fallback table(s) checked.
    1 table(s) skipped due to presence of down regions and/or down subtables.

Example: Rechecking failed tables from previous CHECK (CHECK AGAIN)

To recheck the failed tables from the previous CHECK command:

CHECK AGAIN AT LEVEL TWO;

Before re-running a Level 2 check, you must specify Level 2 again.

You must specify the check level and CHECK options to use the table recheck process on the CHECK AGAIN command line. For example, you could run the table recheck at a higher level.

Example: Rechecking failed tables from a specific error file

To recheck the failed tables in a specific error file:

CHECK AGAIN CheckTableErrors20130503164640 AT LEVEL TWO;
CHECK AGAIN /var/tmp/CheckTableErrors20130503164640 AT LEVEL TWO;
CHECK AGAIN '/var/tmp/CheckTableErrors20130503164640' AT LEVEL TWO;
CHECK AGAIN "/var/tmp/CheckTableErrors20130503164640" AT LEVEL TWO;