Restoring selected partitions of a PPI table allows archiving and restoring only a subset of data in a PPI table.
Restrictions on Restoring Selected Partitions
Before attempting to restore selected partitions, read “Potential Data Risks When Archiving/Restoring Selected Partitions” on page 41 and “Changes Allowed to a PPI Table” on page 184.
The following restrictions apply to restoring selected partitions:
Restoring selected partitions is not allowed to a table that has undergone any of the following major DDL changes:
Other restrictions exist for archiving selected partitions of PPI tables. For more information, see “Restrictions on Archiving Selected Partitions” on page 183.
Keywords for Restoring Selected Partitions
These options are available for restoring selected partition archives:
The next sections describe how to use the options for selecting partitions of PPI tables.
Use the PARTITIONS WHERE option to specify the conditional expression, which contains the definition of the partitions that to be restored. The following restrictions apply to PARTITIONS WHERE:
If the PARTITIONS WHERE option does not capture all the rows that need to be restored, use the LOG WHERE option. This option inserts into a Teradata-generated error table archived rows that both fall outside the partitions specified by the PARTITIONS WHERE conditional expression and match the LOG WHERE conditional expression.
Use the option only if PARTITIONS WHERE is also specified for the object. If LOG WHERE is omitted, the default is to log to the error table only the rows in the partitions being restored that have errors.
The ERRORDB and ERRORTABLES options are mutually exclusive; specify only one option for an object. Also, specify either the PARTITIONS WHERE or ALL PARTITIONS option when using either ERRORDB or ERRORTABLES.
Use the ALL PARTITIONS option to restore all of the archived partitions in a table. These restrictions apply:
If a conditional expression is not well-defined, Teradata ARC issues an error. Use PARTITIONS WHERE for the restore operation rather than ALL PARTITIONS.
Use this option only to restore a specific-AMP archive after restoring selected partitions from an all-AMP archive done while an AMP is down.
All of the rows of the TransactionHistory table for the month of July 2002 are restored in this example:
RESTORE DATA TABLES
(SYSDBA.TransactionHistory)
(PARTITIONS WHERE
(! TransactionDate BETWEEN DATE ‘2002-07-01’ AND DATE ‘2002-07-31’ !)
),
RELEASE LOCK,
FILE=ARCHIVE;
All of the rows for the TransactionHistory table for the month of July 2001 are restored and all rows for the month of August 2001 are logged to an error table called TransError in this example:
RESTORE DATA TABLES
(SYSDBA.TransactionHistory)
(PARTITIONS WHERE
(! TransactionDate BETWEEN DATE ‘2001-07-01’ AND DATE ‘2001-07-31’ !),
LOG WHERE
(! TransactionDate BETWEEN DATE ‘2001-08-01’ AND DATE ‘2001-08-31’ !),
ERRORTABLES SYSDBA.TransError
),
RELEASE LOCK,
FILE=ARCHIVE;
The following example restores all data for all tables in database SYSDBA, including all partitions archived for table TransactionHistory:
RESTORE DATA TABLES
(SYSDBA)
(EXCLUDE TABLES (TransactionHistory)),
(SYSDBA.TransactionHistory)
(ALL PARTITIONS),
RELEASE LOCK,
FILE=ARCHIVE;