Copying Partitioned Data - TARA/ABU

Copying Partitioned Data

Selected partitions of PPI tables can be copied. This means that one or more partitions of a table can be backed up so that only a subset of data in a table can be archived, restored, and copied.

Before attempting to copy selected partitions, read “Potential Data Risks When Archiving/Restoring Selected Partitions” on page 38.

Restrictions on Copying Partitioned Data

Normally, the table to be copied does not need to exist in the target database. The table must exist, however, in the target database to copy selected partitions to the table. Additionally, the target table must be a full table, not just selected partitions.

Copying 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. See “Restrictions on Archiving Selected Partitions” on page 179.

Keywords for Copying Selected Partitions

The options available for copying selected partitions include:

PARTITIONS WHERE

LOG WHERE

ERRORDB/ERRORTABLES

ALL PARTITIONS

QUALIFIED PARTITIONS

PARTITIONS WHERE Keyword

Use the PARTITIONS WHERE option to specify the conditional expression for selected partitions. The expression must contain the definition of the partitions that will be copied. The following restrictions apply to the use of PARTITIONS WHERE:

The object is an individual table (not a database).

The source and target tables have a PARTITIONS BY expression defined.

The copy is an all-AMP copy (not a dictionary, cluster, or journal copy).

If the table belongs to a database that is specified in COPY, 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.

LOG WHERE Keyword

If the PARTITIONS WHERE option does not capture all the rows that need to be copied, 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 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 to 30 bytes.

ALL PARTITIONS Keyword

Use the ALL PARTITIONS option to copy all of the archived partitions in a table. These restrictions apply:

The object being copied is an individual table, or the ALL FROM ARCHIVE option is specified.

The source and target tables have a PARTITIONS BY expression defined.

The copy is an all-AMP copy rather than a dictionary, cluster, or journal copy.

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 copy a specific-AMP archive after copying selected partitions from an all-AMP archive that was done while an AMP is down.