REBUILD AMP - Advanced SQL Engine - Teradata Database

Database Utilities

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Published
January 2021
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
ynh1604715438919.ditamap
dita:ditavalPath
hoy1596145193032.ditaval
dita:id
B035-1102
lifecycle
previous
Product Category
Teradata Vantage™

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

REBUILD AMP nnnn {
  { ALL TABLES ALL DATA |

    { dbase | dbase.tbl } { ALL DATA | PRIMARY DATA | FALLBACK DATA }
  }
  [, rebuild_amp_spec [,...] ] |

  dbase.tbl { DOWN REGION | TABLE HEADER | row_range } [ lock_wait_minutes ]

} ;
rebuild_amp_spec
{ LOG INTO logdbase.logtbl |
  NO LOCK [ ON NO FALLBACK TABLES ] |
  WITH { DATABASE | TABLE | ROWRANGE } LOCK |
  [ n TABLES ] IN PARALLEL
}
row_range
SUBTABLE subtable_id ROWRANGE start_rowid end_rowid [ AUTOADJUSTBLOCKS ]
nnnn
The number of the AMP that is to be rebuilt.
ALL TABLES
Specifies 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 Usage Notes.
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.
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
Specifies 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
Specifies 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
Specifies 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
Specifies 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 Usage Notes.
NO LOCK [ON NO FALLBACK TABLES]
Specifies 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.
ON NO FALLBACK TABLES has no effect on this option, but optionally may be entered for additional console clarity.
WITH DATABASE LOCK
Specifies 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.
This option is valid only with the ALL TABLES option.
WITH TABLE LOCK
Specifies that a table-level read lock will be placed on the source AMP table to be used to rebuild the corrupted table.
This option is valid only with the ALL TABLES option.
WITH ROWRANGE LOCK
Specifies 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.
For a column-partitioned table, a full table lock is used.
This option is valid only with the ALL TABLES option.
[n TABLES] IN PARALLEL
Specifies 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 nTABLES 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), the rebuild preserves the IN PARALLEL setting, and continues rebuilding tables in parallel.
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.