REBUILD AMP - Analytics Database - Teradata Vantage

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
  LOGRATE n
}

row_range

SUBTABLE subtable_id ROWRANGE start_rowid end_rowid [ AUTOADJUSTBLOCKS ]

Syntax Elements

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 Vantage 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 Teradata Vantage™ - Database Design, B035-1094.

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) multiple tables can be rebuilt in parallel. This can make the rebuild operations complete more quickly. From 2 to 255 tables can be rebuilt simultaneously.

n is an integer from 2 to 255 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 Vantage 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.

LOGRATE n

Provides progress information while rebuilding. Rebuild progress information is reported every n minutes. n is an integer from 1 to 10.

The output is similar to this:

P 05:04:32 Progress info status: 1.20% 667 MB/55522 MB 11.1 MB/s 00:01:11 ETA 01:22:09

The letter P indicates the message code for progress information. It is followed by the current system time, percentage of rebuild done, data size done so far/total size, rate at which rebuild is progressing, elapsed time, and estimated turnaround time.

Progress information is not shown for these cases specific to a single table:

REBUILD AMP nnnn dbname.tname SUBTABLE subtableid ROWRANGE startrowid endrowid;

REBUILD AMP nnnn dbname.tname DOWN REGION [,lockwaittimeinminutes];

REBUILD AMP nnnn dbname.tname TABLE HEADER [,lockwaittimeinminutes];