17.10 - Usage Notes - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Utilities

Advanced SQL Engine
Teradata Database
Release Number
July 2021
English (United States)
Last Update
  • 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.
  • Tables that use sparse maps (or contiguous maps that include only a subset of all AMPs in the system) are not necessarily distributed to all AMPs of the system. If a table specified for rebuilding does not exist on an AMP specified in the REBUILD command, Table Rebuild issues the following message:
    Unable to rebuild table tablename due to table not in rebuilt AMP
    For more information on contiguous and sparse maps, see Teradata Vantage™ - Database Design, B035-1094.
  • 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. In these cases, Vantage 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.

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 database.
    This step is not necessary if the rebuilding AMP state was FATAL before the last 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.

    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.

    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.
    Table Rebuild automatically sets the VprocState of this AMP from UTILITY to OFFLINE when complete.
  6. Restart the 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, Vantage 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:

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