Keywords for Restoring Selected Partitions - TARA/ABU

Teradata Archive/Recovery Utility Reference

Product
TARA/ABU
Release Number
16.10
Published
May 2017
Language
English (United States)
Last Update
2018-05-01
dita:mapPath
utr1488824663491.ditamap
dita:ditavalPath
Audience_PDF_include.ditaval
dita:id
B035-2412
lifecycle
previous
Product Category
Teradata Tools and Utilities

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.

PARTITIONS WHERE Keyword

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 accidentally qualify more partitions than intended.

LOG WHERE Keyword

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.

ERRORDB/ERRORTABLES Keyword

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.

ALL PARTITIONS Keyword

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.

QUALIFIED PARTITIONS Keyword

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.

Examples of Keywords for Restoring Selected Partitions

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;