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

Purpose  

Rebuilds only tables with fallback protection, including stored procedure tables. Because Table Rebuild deletes all tables selected for rebuild before the start of the rebuild process, this command prevents tables without fallback protection from being deleted.

This command performs most of the work which is done by normal down AMP recovery, making the actual restart to bring a down AMP back on line as fast as possible, while guaranteeing data integrity. The AMP to be rebuilt must be off line and all of the other AMPs in the cluster must be on line.

Syntax  

 

Syntax element …

Specifies …

nnnn

the number of the AMP vproc to be rebuilt.

FALLBACK TABLES

that only tables with fallback protection will be rebuilt. This prevents tables without fallback protection from being deleted. The first step that Table Rebuild usually performs is to delete the contents of the table that Table Rebuild is rebuilding.

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

WITH DATABASE LOCK

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

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.

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: For a partitioned primary index (PPI) table, a rowrange lock is not used. A table-level lock is used instead.

[n TABLES] IN PARALLEL

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

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.

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.LogTable ,FALLBACK ,NO BEFORE JOURNAL,
NO AFTER JOURNAL,CHECKSUM = DEFAULT
 ( MsgDate CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC,
   MsgTime CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC,
   MsgAMP  CHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC,
   MsgCode CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
   MsgText VARCHAR(600) CHARACTER SET UNICODE NOT CASESPECIFIC )
PRIMARY INDEX ( msgdate ,msgtime );

The table must have been defined previously as shown above, with the table characteristics matching the example. The names of the database, table, and columns are customizable.

The MsgAMP (third column) contains a four-digit AMP number.

The MsgCode (fourth column) has 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

You can use the log table to create reports.

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.