Archiving Selected Partitions of PPI Tables - TARA/ABU

An all-AMPs archive on one or more partitions of a table can be performed rather than performing a full-table backup and restore. This feature is limited to all-AMP archives. Dictionary, cluster, and journal archives are not supported.

Use selected partitions to archive only a subset of data and avoid archiving data that has already been backed up. (Minimizing the size of the archive can improve performance.)

Before using this feature, be sure to understand “Potential Data Risks When Archiving/Restoring Selected Partitions” on page 41.

For procedures and script examples of selecting partitions, see “Archiving Selected Partitions of PPI Tables” on page 37.

Restrictions on Archiving Selected Partitions

These restrictions apply to archiving selected partitions of a PPI table:

Cluster, dictionary, and journal archives are not supported.

It is recommended that tables containing large objects (BLOB and CLOB columns) not be archived with selected partitions (using PARTITIONS WHERE) because a number of manual steps are needed to restore the data. Instead of using selected partitions, use a full-table archive and restore for tables that contain both PPIs and LOBs.

For additional information, see “Potential Data Risks When Archiving/Restoring Selected Partitions” on page 41 and “Considerations Before Restoring Data” on page 53.

Keywords for Archiving Selected Partitions

To perform an archive of selected partitions, specify a conditional expression in the PARTITIONS WHERE option in an ARCHIVE script. This conditional expression should only reference the column(s) that determine the partitioning for the table being archived.

Use the PARTITIONS WHERE option to specify the conditional expression, which contains a definition of the rows to be archived. To be effective, limit this expression to the columns that determine the partitioning for the table to be archived.

These restrictions apply to the use of PARTITIONS WHERE:

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

The source table has a PARTITIONS BY expression defined.

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

The INDEXES option is not used.

If the table belongs to a database that is specified in the ARCHIVE statement, 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 PARTITIONS WHERE condition be the partitioning columns or system-derived column PARTITION of the table. References to other columns do not contribute to partition elimination, and might accidently qualify more partitions than intended.

Examples of ARCHIVE Keywords

This example executes an archive of all the rows in the TransactionHistory table in the SYSDBA database for the month of July 2002:

   ARCHIVE DATA TABLES 
   (SYSDBA.TransactionHistory)
   (PARTITIONS WHERE

   (! TransactionDate BETWEEN DATE ‘2002-07-01’ AND DATE ‘2002-07-31’ !) )

   ,

   RELEASE LOCK,
   FILE=ARCHIVE;

Changes Allowed to a PPI Table

The following changes can be made to a PPI table without affecting or preventing the restoration of data from an archive of selected partitions:

Table-level options that are unrelated to semantic integrity, such as FALLBACK protection, journaling attributes, free space percentage, block size, and others.

Use of the MODIFY PRIMARY INDEX option, which changes the partitioning expression.

Changes to table-level CHECK CONSTRAINTS.

Changes that add, drop, or modify a CHECK CONSTRAINT on a column.

Secondary indexes may be added or dropped.

Other changes to a PPI table are more significant in that they affect the DBC.TVM.UtilVersion. Do not restore archives of selected partitions if the archive UtilVersion does not match the table UtilVersion. DBC.TVM.UtilVersion is initially set to 1. ALTER TABLE increases the value of DBC.TVM.UtilVersion to match the table DBC.TVM.Version whenever the following significant changes occur to the referential integrity of a child table:

Modification of the columns of a primary index

Addition or deletion of columns

Modification of the definition of an existing column

Addition, deletion, or modification of the referential integrity among tables

When DBC.TVM.UtilVersion is updated for a table, previous archives are invalid for future restores or copies of selected partitions to that table, but a full table restore or copy is still valid. For more information, see the Data Dictionary.