Restores of Selected Partitions - TARA/ABU

Teradata Archive/Recovery Utility Reference

Product
TARA/ABU
Release Number
15.10
Language
English (United States)
Last Update
2018-10-07
dita:id
B035-2412
lifecycle
previous
Product Category
Teradata Tools and Utilities

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 machine with a hash function that is different from the source machine, but a different configuration is allowed.
  • To restore or copy selected partitions, a table must already exist on the target system. For a copy, the existing table must have been created by a full-table copy from the source machine.
  • Restoring selected partitions is not allowed to a table that has undergone any of the following major DDL changes:
  • Adding, modifying, or dropping columns.
  • Certain changes during RESTORE and COPY operations. For more information, see “Restrictions on Copying Partitioned Data” on page 204.
  • Restoring selected partitions is not allowed to a table that has undergone any of the following major DDL changes:

  • Changing primary index columns
  • Changing from PPI to NPPI, or visa versa
  • Adding or changing referential integrity constraints
  • 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:

  • PARTITIONS WHERE
  • LOG WHERE
  • ERRORDB/ERRORTABLES
  • ALL PARTITIONS
  • QUALIFIED PARTITIONS
  • 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:

  • The object is an individual table (not a database).
  • The source and target tables have a PARTITIONS BY expression defined.
  • The restore is an all-AMP restore (not a dictionary, cluster, or journal restore).
  • If the table belongs to a database that is specified in RESTORE, the table is excluded from the database-level object (with EXCLUDE TABLES) and is individually specified.
  • Any name specified in the conditional expression is within the table being specified. (Using table aliases and references to databases, tables, or views that are not specified within the target table result in an error.) It is recommended that the only referenced columns in the conditional expression be the partitioning columns or system-derived column PARTITION of the table. References to other columns does not contribute to partition elimination, and might accidently qualify more partitions than intended.
  • 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.

  • If ERRORTABLES is specified without a database name, or if neither ERRORTABLES nor ERRORDB is specified, the error table is created in the same database as the base table.
  • If ERRORTABLES is not specified, by default the naming convention for the error table is the name of the base table plus the prefix “RS_”. For example, the error table for a table named “DataTable” is “RS_DataTable.” Names are truncated if they exceed 30 bytes.
  • Use the ALL PARTITIONS option to restore all of the archived partitions in a table. These restrictions apply:

  • The object being restored is an individual table, or the ALL FROM ARCHIVE option is specified.
  • The source and target tables contain a defined PARTITIONS BY expression.
  • The restore is an all-AMP restore rather than a dictionary, cluster, or journal restore.
  • PARTITIONS WHERE is not specified for the object.
  • The partition bounding condition must have been well-defined when the backup was performed. A bounding condition is well-defined if the PARTITION BY expression on the source table consists of a single RANGE_N function, and if the specified range does not include NO RANGE or UNKNOWN. (Use ANALYZE to determine whether a selected partition is well-defined.)
  • 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;