15.00 - REBUILD AMP - 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

REBUILD AMP

Purpose  

Rebuilds data on a specified AMP. The rebuild operation can include:

  • All tables on the AMP
  • For a specified database: Primary, fallback, or both types of data in a specified database
  • For a specified table: Primary, fallback, or both types of data, down regions, table header, or a specific range of rows
  • Syntax

     

    Syntax element …

    Specifies …

    nnnn

    the number of the AMP that is to be rebuilt.

    ALL TABLES

    that all table data stored on the AMP, whether primary or fallback data, is to be rebuilt.

    Tables with fallback protection are fully recovered. Tables without fallback protection are left empty on the AMP. All the data in the permanent journals will be recovered, except for journal rows for non-fallback tables without the dual journaling option.

    The AMP to be rebuilt must be in the UTILITY vproc state and running DBS partitions. All the other AMPs in the cluster must be on-line.

    For more information, see “Before Rebuilding All Tables” on page 859.

    dbase

    the name of a database. All tables (including stored procedures) in this database will be rebuilt on the specified AMP. Tables with fallback protection are fully recovered. Tables without fallback protection are left empty on the AMP. If the database contains a permanent journal, the journal is left unchanged.

    If the DBC database is specified, the specified AMP must be off line. For other databases, the AMP may be on line or off line during the rebuild.

    Note: Join indexes and hash indexes are skipped and not rebuilt. This is true whether or not the join index has fallback.

    dbase.tbl

    the name of a table, stored procedure, UDF, or UDM that is to be rebuilt. The AMP on which the table or stored procedure will be rebuilt can be either online or offline.

    If the specified table is fallback protected, the appropriate data is recovered. If the table is not fallback protected, the table is left empty if ALL or PRIMARY DATA rebuild was selected. You cannot rebuild fallback data for a table that has no fallback protection.

    ALL DATA

    that both primary data and fallback data tables stored on the AMP should be rebuilt. recommended, unless you are certain that only primary or only fallback data on the AMP has been corrupted.

    PRIMARY DATA

    that only the primary data tables stored on the AMP should be rebuilt.

    Use the ALL DATA option unless you are certain that only primary data on the AMP has been corrupted.

    FALLBACK DATA

    that only the fallback data tables stored on the AMP should be rebuilt.

    Use the ALL DATA option unless you are certain that only fallback data on the AMP has been corrupted.

    DOWN REGION

    to rebuild only the down regions of the specified table.

    Down regions are ranges of rows for which Teradata Database has detected file system errors. The rows are marked as down, and their data can be rebuilt from fallback copies.

    TABLE HEADER

    to rebuild the table header on the specified AMP. At least one AMP in the system must be online. Information in fields 4, 6, and 10 of the table header row may be lost as a result of the rebuild. For more information on these fields, see Database Design.

    SUBTABLE subtable_id

    to rebuild a specific subtable.

    subtable_id is a non-zero 16-bit number in hexadecimal format that identifies the subtable to be rebuilt.

    For example, 400 represents the primary data subtable of the specified table, and 800 represents the fallback data subtable for the primary data.

    ROWRANGE
    start_rowid end_rowid

    Rebuilds the specified range of rows.

    The format for a row ID comprises five hexadecimal values:

  • partition number: a 64-bit number in hexadecimal format, or 0 for a non-partitioned table
  • hash0: 16-bit hexadecimal number
  • hash1: 16-bit hexadecimal number
  • unique0: a 16-bit hexadecimal number
  • unique1: a 16-bit hexadecimal number
  • Each hexadecimal value can optionally include an H suffix.

    Examples of valid row IDs:

    0H B334 4BFA 00 01
    0 B334H 4BFA 00H 01
    0H B334H 4BFAH 0H 1H
    0 B334 4BFA 0 1

     

    AUTOADJUSTBLOCKS

    specifies that Table Rebuild should automatically adjust the specified row range to include complete blocks if the specified row range starts or ends in the middle of a bad block.

    If this option is not specified and a row range starts or ends in the middle of a bad data block, REBUILD returns an error.

    lock_wait_minutes

    the amount of time in minutes that Table Rebuild should wait to obtain a table lock. The default is zero, which means REBUILD waits indefinitely.

    LOG INTO logdbase.logtbl

    that Table Rebuild is to run in the quiet mode or background mode. All messages will be written to the system console and to a user‑defined table. The table is specified by database name and table name. For more information, see “Running Table Rebuild in the Background” on page 860.

    NO LOCK [ON NO FALLBACK TABLES]

    that no lock should be applied to any non-fallback tables being rebuilt. (non-fallback tables are tables that were created without fallback protection.)

    By default, tables that do not have fallback protection are flagged in their table headers as being in the process of being rebuilt. (Field 4 of the table header row contains ‘rebuild in progress’.) This causes locks to be applied which limit the operations that are allowed on these tables. As long as these tables are flagged, they cannot be dropped or restored, and the rebuild cannot be rerun on them.

    The only ways to remove the flag is by one of the following:

  • rebuild the table with the NO LOCK option
  • drop the table
  • restore the table
  • The NO LOCK option prevents the flagging and locking of these tables,. It should be used when access to the tables is no longer important. Rebuilding non-fallback tables causes their contents to be deleted.

    Note: ON NO FALLBACK TABLES has no effect on this option, but optionally may be entered for additional console clarity.

    WITH DATABASE LOCK

    that a database‑level read lock will be placed on the source AMP database data used to rebuild each corrupted table. This is the default lock setting.

    Note: This option is valid only with the ALL TABLES option.

    WITH TABLE LOCK

    that a table‑level read lock will be placed on the source AMP table to be used to rebuild the corrupted table.

    Note: This option is valid only with the ALL TABLES option.

    WITH ROWRANGE LOCK

    that a rolling-read lock (selected groups of row‑only locks) will be placed on the source AMP table used to rebuild the corrupted table. This lock allows concurrent updates of the tables being used on the source AMP for the rebuild.

    Note: This option is valid only with the ALL TABLES option.

    For a partitioned table, a rowrange lock is not used. A table-level lock is used instead.

    [n TABLES] IN PARALLEL

    that during all-table rebuilds (rebuilds using ALL TABLES), or during fallback table rebuilds (using FALLBACK TABLES), or during database rebuilds (using the dbase options described above) multiple tables per database should be rebuilt in parallel. This can make the rebuild operations complete more quickly. From two to six tables can be rebuilt simultaneously.

    n is an integer from 2 to 6 that specifies how many tables will be rebuilt in parallel. If n TABLES is not specified, the default number of tables that will be rebuilt in parallel is six.

    If Teradata Database is reset during an ALL TABLES ALL DATA rebuild, when the rebuild process is restarted (see “RESTART REBUILD” on page 865), the rebuild preserves the IN PARALLEL setting, and continues rebuilding tables in parallel.

    Note: If the IN PARALLEL option is used together with the ALL TABLES, FALLBACK TABLES, or dbase options, the database will be locked during the entire duration of the parallel rebuild.

    Usage Notes

    Regardless of whether the AMP to be rebuilt is offline or online during Table Rebuild, all other AMPs in the same cluster must be online. AMPs in other clusters may be offline.

    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.

    The normal rebuild process removes down-region information from the table header. For more information on down regions, see CheckTable and DBS Control.

    Before Rebuilding All Tables

    Before rebuilding all tables, do the following:

    1 Use the Vproc Manager utility to set the VprocState of the AMP that will be rebuilt to FATAL.

    2 Restart the Teradata Database.

    Note: This step is not necessary if the rebuilding AMP state was FATAL before the last Teradata Database restart.

    3 Use the Vproc Manager utility to boot the AMP that will be rebuilt.

    Messages will be displayed on the system console to indicate the status of the boot. If the boot is successful, this AMP is ready for an ALL TABLES rebuild.

    Note: The BOOT command will re-initialize the disk of the AMP in anticipation of all-tables table rebuild and start the DBS partitions on the specified AMP. This applies only to vprocs with a VprocState of FATAL and a ConfigStatus of Down. A confirmation input is necessary to process the initialization.

    Valid VprocIds are decimal numbers in the range of 0 through either 30719 or 16383, depending on the system.

    Note: Hex numbers can also be specified by appending a trailing “x” (for example, 0x, 3FFx).

    4 Start Table Rebuild and run an ALL TABLES rebuild on this AMP.

    5 When the rebuild is done, use the Vproc Manager utility to set the VprocState of this AMP to ONLINE.

    Note: Table Rebuild automatically sets the VprocState of this AMP from UTILITY to OFFLINE when complete.

    6 Restart the Teradata Database.

    Rebuilding One or All Tables

    When all data on an AMP has been lost and needs to be rebuilt or the database DBC on that AMP needs to be rebuilt, Table Rebuild can recover this information. While database DBC is being rebuilt, the AMP whose data needs to be rebuilt must be offline. By contrast, when any other database on the AMP or a single table is being rebuilt, the AMP can be either online or offline.

    After all databases are rebuilt, Teradata Database must be restarted to update the rebuilt tables and to return the AMP whose data has been rebuilt to online operation.

    Running Table Rebuild in the Background

    When you specify the LOG INTO logdbase.logtbl option, Table Rebuild runs as a background task. You can run multiple Table Rebuild operations both in the background and foreground (interactive mode) at the same time. Completion messages for background rebuilds are sent to the system console and to the user‑defined table specified in the LOG INTO option.

    The table specified in the LOG INTO option must have been created previously as follows:

    CREATE SET TABLE logDB.LogTbl, FALLBACK
      ( MsgDate CHAR(8),     /* format:  'yy/mm/dd' */ 
        MsgTime CHAR(8),     /* format:  'hh:mm:ss' */ 
        MsgAMP  CHAR(6),     /* format:  'nnnn' */ 
        MsgCode CHAR(1),     /* see below */
        MsgText VARCHAR(600) CHARACTER SET UNICODE) /* message text */ 
      PRIMARY INDEX (MsgDate, MsgTime);
     

    Column

    Contents

    MsgDate and MsgTime

    The system date and time when the message was generated. Together these columns comprise a non-unique primary index for the log table.

    MsgAMP

    The four-digit vproc number of the rebuilding AMP.

    MsgCode

    A single character code indicating the type of rebuild message. See below.

    MsgText

    The text of the rebuild message.

    MsgCode is one of the following values:

     

    Value

    Meaning

    ‘ ’

    A normal message

    ‘D’

    Rebuilding database message

    ‘E’

    Error message

    ‘J’

    Rebuilding table message for a journal

    ‘N’

    Rebuilding table message for a no‑fallback table

    ‘R’

    Rebuilding table message for tables used by recovery

    ‘S’

    Start/Restart rebuild operation

    Note: The table-level messages do not include the database names. The reports should include all the ‘D’ class messages and be ordered by date and time for proper identification.