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

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

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 tablenames beginning with Sales

    CHECK ALL TABLES EXCLUDE database1.Sales% AT LEVEL ONE;

    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 11/06/26.
     
    ...
     
    "PROD"."CUSTOMERS" starting at 11:14:22 11/06/26.
      Table id 0000H 624BH, No fallback.
      Table check bypassed due to pending MultiLoad.
    "PROD"."SHIPMENT" starting at 11:14:24 11/06/26.
      Table id 0000H 04E1H, No fallback.
      Table check bypassed due to pending Table Rebuild.
    ...
     
     1435 table(s) checked.
      420 fallback table(s) checked.
     1015 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 11/06/26.

    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 “Wildcard Syntax” on page 58 and “Rules for Using Wildcard Syntax” on page 60. If a specified table includes a dbname, then CheckTable only checks the table in that referenced database.

    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 00/04/26.
    "MFGDB"."INVENTORY" starting at 13:08:06 11/04/26.
      Table id 0000H 0C31H, Fallback.
      No errors reported.
    "MFGDB"."PARTS" starting at 13:08:07 11/04/26.
      Table id 0000H 0C33H, No fallback.
          2753: Primary data row is on wrong AMP.
                AMP 00000
                Row id 0000H 79B6H 9E37H 0000H 0001H
                Expected primary AMP 00001
      1 error(s) reported.
    "MFGDB"."RETURNS_TEMP" starting at 13:08:08 11/04/26.
      Table id 0000H 0C32H, Fallback.
      No errors reported.
    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 11/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, internal ID, fallback status, and the date and time:

    “dbname”.“tablename” starting at YY/MM/DD HH:MM:SS
    [Table ID nnnnH nnnnH, Fallback] [Table ID nnnnH nnnnH, No fallback]

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

    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 11/12/30
    "FB3"."T1" skipped at 07:57:26 11/12/30 due to pending lock.
      Table id 0000H 3E9H.
    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.

    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;

    Serial Mode Output

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

    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 11/12/30.

    Note: The entire output is not shown.

    Parallel Mode Output

    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 11/12/30.
    "DB0"."T1" starting at 07:57:15 11/12/30.
      Table id 0000H 3F92H, Fallback.
    "DB0"."T10" starting at 07:57:15 11/12/30.
             Table id 0000H 0D14H, Fallback.
    "DB0"."T100" starting at 07:57:15 11/12/30.
      Table id 0000H 0D6EH, Fallback.
    "DB0"."T1000" starting at 07:57:15 11/12/30.
      Table id 0000H 12E6H, Fallback.
    "DB0"."T10" checking at 07:57:15 11/12/30.
      Table id 0000H 0D6EH, Fallback.
          2741: Table header not found.
                Table id 0000H 0D14H
                Header missing on 1 AMP:
                00000  
                Further checking skipped because of missing header(s).
    "DB0"."T10" ending at 07:57:15 11/12/30.
      Table id 0000H 0D6EH, Fallback.
    "DB0"."T1" checking at 07:57:15 11/12/30.
      Table id 0000H 3F92H, Fallback.
          2757: Primary data row is missing.
                Fallback AMP 00001, Fallback subtable 2048
                Row id 0000H 8C49H CDABH 0000H 0001H
                Expected primary AMP 00000
    "DB0"."T111" starting at 07:57:16 11/12/30.
      Table id 0000H 12E8H, Fallback.
    "DB0"."T1" checking at 07:57:17 11/12/30.
      Table id 0000H 3F92H, Fallback.
          2757: Primary data row is missing.
                Fallback AMP 00001, Fallback subtable 2048
                Row id 0000H BD81H 0459H 0000H 0001H
                Expected primary AMP 00000
    "DB0"."T100" checking at 07:57:17 11/12/30.
      Table id 0000H 0D6EH, Fallback.
          2757: Primary data row is missing.
                Fallback AMP 00003, Fallback subtable 2048
                Row id 0000H 1897H 9B57H 0000H 0001H
                Expected primary AMP 00002
    "DB0"."T1000" ending at 07:57:18 11/12/30.
      Table id 0000H 12E6H, Fallback.
          No errors reported.
    "DB0"."T1" checking at 07:57:18 11/12/30.
      Table id 0000H 3F92H, 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
    "DB0"."T1" checking at 07:57:18 11/12/30.
      Table id 0000H 3F92H, Fallback.
          2888: Invalid reference index row.
                AMP 00002, Primary subtable
                Reference index id 0
                Reference index row id 0000H 1897H 9B57H 0000H 0001H
    "DB0"."T100" checking at 07:57:18 11/12/30.
      Table id 0000H 0D6EH, Fallback.
          2757: Primary data row is missing.
                Fallback AMP 00003, Fallback subtable 2048
                Row id 0000H 3133H 36AEH 0000H 0001H
                Expected primary AMP 00002
    "DB0"."T100" ending at 07:57:19 11/12/30.
      Table id 0000H 0D6EH, Fallback.
      2 error(s) reported.
    "DB0"."T1" checking at 07:57:19 11/12/30.
      Table id 0000H 3F92H, Fallback.
      AMP 00002, Primary subtable
                 Reference index id 0
                 Reference index row id 0000H 3133H 36AEH 0000H 0001H

    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 11/01/06.
    Data dictionary check started at 10:55:23 11/01/06.
    F2
    >>>> Status: CheckTable running in PARALLEL mode.
          3 CheckTable tasks started.
          3 CheckTable tasks ACTIVE.
          0 CheckTable tasks IDLE.
    Task STATUS

    PRIORITY

    A priority level controls resource usage and improves performance.

    To run CheckTable in PARALLEL mode at the priority level corresponding to the performance group name $HMKTG, type the following:

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

    Assume the priority in the following example is invalid:

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

    CheckTable displays an error message indicating that the priority is invalid and waits for the next input.

    CONCURRENT MODE

    Example  

    To run CheckTable in CONCURRENT mode at level three on rfc66706.table_1, type the following:

    check rfc66706.table_1 at level three concurrent mode;

    The following appears:

    Check beginning at 15:38:01 11/03/17.

    "RFC66706"."TABLE_1" skipped at 15:38:16 11/03/17 due to pending lock.
      Table id 0000H 04D3H .

    "RFC66706"."TABLE_1" skipped again at 15:43:16 11/03/17 due to pending lock.
      Table id 0000H 04D3H .

     Remaining time to be retried is forever.

    "RFC66706"."TABLE_1" skipped again at 15:48:16 11/03/17 due to pending lock.
      Table id 0000H 04D3H .

     Remaining time to be retried is forever.

    Example  

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

    check dbc at level one skiplocks concurrent mode retry limit 1;

    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  

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

    check all tables at level one skiplocks concurrent mode retry limit 1;

    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.

    DOWN ONLY

    Example  

    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.

    "FIU"."ONEDR" checking.
      Table id 0000H 06A1H
          9131:Check was skipped due to detection of error <9130>.
               AMP 00000, Primary Data Subtable 1024 has down region marked.

        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  

    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.

    "FIU"."ONEDR" checking.
      Table id 0000H 06A1H
          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


        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  

    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.

    "FIU"."ONEDT" checking.
      Table id 0000H 06A2H
          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.

        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.