All AMPs Online - TARA/ABU

An all-AMPs archive (or dictionary archive) of a database or table contains the Teradata Database dictionary rows that are needed to define the entity.

Table 3 alphabetically lists the dictionary rows or tables that are archived by an all-AMPs data archive (or a dictionary tables archive) of a user database or table.

Table 3: Dictionary Rows and Tables Archived for User Database 

Tables and Table Rows	Description
ConstraintNames	Constraints that have names
DBCAssociation	Retrieves information about tables that have been ported using the Dump/Restore facility
Dependency	Stores relationships among a UDT, its dependent routines, User- Defined Casts, User-Defined Transforms, User-Defined Orderings, and any dependency on any other database object
IdCol	Identity column
Indexes	Columns that are indexes
IndexNames	Indexes that have names
QueryStatsTbl	Saves information about collected statistics on the queries
ReferencedTbls	Referenced columns (i.e., Parent Key) for Parent tables
ReferencingTbls	Referencing columns (i.e., Foreign Key) for Child tables
StatsTbl	Saves information about collected statistics on the base tables, views, or queries
TableConstraints	Table level Check constraints
TextTbl	System table containing overflow DDL text
TriggersTbl	Definition of all triggers in the database
TVFields	All columns in data tables and views
TVM	All objects in the database
UDFInfo	Information on user-defined functions
UnresolvedReferences	Unresolved referential constraints

Example

The following example archives all databases when all AMPs are online:

   LOGON DBC,DBC;

   ARCHIVE DATA TABLES (DBC) ALL,

   RELEASE LOCK,

   FILE=ARCHIVE;

   LOGOFF;

When the archive operation is complete, check the output log to verify that all AMPs remained online during the archive.

If an AMP goes offline during an archive, the output log reports the processor number that is offline. If one or more AMPs go offline, see “Offline AMPs” on page 44 for more information.

Archiving Selected Partitions of PPI Tables

An all-AMPs archive on one or more partitions of a table is supported: it is not necessary to perform a full-table backup and restore. The ability to select partitions from PPI tables is limited to all-AMP archives. Dictionary, cluster, and journal archives are not supported.

Use partitioning to:

Archive only a subset of data and avoid archiving data that has already been backed up. (This can minimize the size of the archive and improve performance.)

Restore data in a table that is partially damaged.

Copy a limited set of data to a disaster recovery machine or to a test system.

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

For information about the keywords that are specific to archiving and restoring selected partitions of PPI tables, see “Using Keywords with ARCHIVE” on page 179 and “Archiving Selected Partitions of PPI Tables” on page 183.

PPI allows the division of data in a table into separate partitions based on a specific partitioning scheme. With Teradata ARC, individual partitions can be archived according to user-defined partitioning expressions. For more information about options for PPI archives/restores, see Chapter 6: “Archive/Recovery Control Language.”

Consider the following when archiving selected partitions in PPI tables:

A restore operation always deletes the selected partitions of the target table before restoring the rows that are stored in the archive.

Archiving selected partitions operates on complete partitions within tables, meaning that the selection of a partial partition implies the entire partition.

PPI and non-PPI tables are permissible in a single command. Both table types can be managed in a single database with the EXCLUDE TABLES option.

Partitioning is based on one or more columns specified in the table definition.

Partition elimination restricts a query to operating only in the set of partitions that are required for the query.

Incremental archives are possible by using a partition expression that is based on date fields, which indicate when a row is inserted or updated.

An archive or restore of selected partitions only places full-table HUT locks. HUT locks on individual partitions are not supported.

Re-collect table statistics after a restore of selected partitions. Statistics are part of the table dictionary rows, which are not restored during a partition-level restore.

If a table has a partitioning expression that is different from the partitioning expression used in the PPI archive, a PPI restore is possible as long as no other significant DDL changes are made to the table.

The archival of selected partitions has limitations. For more information, see “Potential Data Risks When Archiving/Restoring Selected Partitions” on page 41 and “Considerations Before Restoring Data” on page 53.

The next example shows a partitioning expression that follows the PARTITION BY keyword. Data is partitioned for the TransactionHistory table, based on the month when the transaction occurred:

   CREATE TABLE TransactionHistory

   (TransactionID         INTEGER,

   TransactionDate       DATE FORMAT ‘yyyy-mm’dd’,

   TransactionParam1     INTEGER,

   …

   )

   PRIMARY INDEX (TransactionID)

   PARTITION BY RANGE_N

   (TransactionDate BETWEEN DATE ‘2000-01-01’ AND DATE ‘2004-12-31’

   EACH INTERVAL ‘1’ MONTH

   );

The procedure in this section is a generic example of how to set up archive and restore scripts for selected partitions. This example is based on the TransactionHistory table previously described.

Assume that this backup schedule is desired for the TransactionHistory table:

An incremental backup of the currently active partition will be done nightly.

At the beginning of each month, the final incremental backup for the previous month will be done; this backup will be saved until the next differential or full-table backup is done.

Every three months, a differential backup will be done, containing the data for the last three months.

Every year, a full-table backup will be done.

1 Perform a full-table archive:

ARCHIVE DATA TABLES

    (SYSDBA.TransactionHistory),

RELEASE LOCK,

FILE=ARCHIVE;

2 Set up incremental archives:

ARCHIVE DATA TABLES

    (SYSDBA.TransactionHistory)

      ( PARTITIONS WHERE

        (! TransactionDate BETWEEN CURRENT_DATE – 3 AND CURRENT_DATE

          !) ),

RELEASE LOCK,

FILE=ARCHIVE;

Note: In this example, ‘CURRENT_DATE – 3’ archives a partition even after it becomes non-active, in case the final archive of the partition fails or the value of CURRENT_DATE changes during the final backup.

3 Set up differential backups:

ARCHIVE DATA TABLES

    (SYSDBA.TransactionHistory)

      ( PARTITIONS WHERE

        (! TransactionDate BETWEEN DATE ‘2004-01-01’ AND DATE ‘2004-03-31’

           !) ),

RELEASE LOCK,

FILE=ARCHIVE;

4 (Optional) Perform a separate partition backup if updating a partition that is not archived by the incremental backup step (step 2):

ARCHIVE DATA TABLES

    (SYSDBA.TransactionHistory)

      ( PARTITIONS WHERE

        (! TransactionDate = DATE ‘2004-03-15’

          !) ),

RELEASE LOCK,

FILE=ARCHIVE;

1 Perform a complete restoration of the full-table archive:

RESTORE DATA TABLES

    (SYSDBA.TransactionHistory),

RELEASE LOCK,

FILE=ARCHIVE;

2 Perform a full restoration of the differential and incremental archives (in order):

RESTORE DATA TABLES(SYSDBA.TransactionHistory) (ALL PARTITIONS),

RELEASE LOCK,

FILE=ARCHIVE;

3 (Optional) If a separate partition backup is performed due to an update of a non-active partition, restore the separate partition backup after (or instead of) the differential or incremental backup that contains the updated partition:

RESTORE DATA TABLES

    (SYSDBA.TransactionHistory)

      ( PARTITIONS WHERE

        (! TransactionDate = DATE ‘2004-03-15’

          !) ),

RELEASE LOCK,

FILE=ARCHIVE;

Individual partitions can also be restored from a full-table or selected-partition archive. To restore individual partitions, specify the partitions to be restored in a PARTITIONS WHERE expression:

   RESTORE DATA TABLES

    (SYSDBA.TransactionHistory)

      ( PARTITIONS WHERE

        (! TransactionDate BETWEEN DATE ‘2004-05-01’ AND DATE ‘2004-05-31’!) 

        ),

   RELEASE LOCK,

   FILE=ARCHIVE;

Be careful when archiving partitioned tables: a number of undesirable conditions can occur. For additional issues that might occur during restore operations, see “Considerations Before Restoring Data” on page 53.

Note: The following cases generally do not display an error or give any indication that a problem has occurred. In most instances, the only indication is that data is incorrect or is missing from a table.

Use Correct Specifications –The incorrect use of specifications causes the following problems:

An incorrect PARTITIONS WHERE specification during backup can result in an incomplete archive or difficulties during a restore operation.

An incorrect PARTITIONS WHERE or ALL PARTITIONS specification during restore can result in data lost from a table or the restoration of stale data to a table if the archive being restored contains partial, incomplete, or stale versions of an already existing partition.

Restrict Updates to Active Partitions – It is not possible to determine which partitions have been modified since the last backup. If changed partitions are not re-archived, the changes are lost when restored.

For example, if the following scenarios exist for a table:

The backup strategy is to back up only the active (latest) partition of the table.

A change is made to a non-active partition (to fix an incorrect update).

the only way to archive the change is to archive the changed partitions separately.

The remedy for this situation is to restrict updates to the active partitions only (by using views to control which rows/partitions are updated) or to re-archive all modified partitions.

Do Not Change Values of Functions or Variables – I f a built-in SQL function or variable is used in the PARTITIONS WHERE condition, and the value of the function or variable changes during the job, a different set of partitions might be archived (or restored) for some objects in that single archive.

For example, if an archive job uses the CURRENT_DATE built-in function to determine which is the active partition, and the backup runs past midnight, the date change causes a different partition to be selected. This means that objects archived after midnight will archive the new (and probably empty) partition.

The remedy for this situation is to do one of the following:

Avoid using a changing function or variable in the PARTITIONS WHERE condition.

Run the backup at a time when the value will not change.

Modify the PARTITIONS WHERE condition to take the value change into account when selecting partitions. For example, define a range, such as ‘BETWEEN CURRENT_DATE – n AND_CURRENT_DATE’ to archive the active partition even if the date changes.

Always Specify PARTITIONS WHERE or ALL PARTITIONS – If PARTITIONS WHERE or ALL PARTITIONS are not specified for a RESTORE or COPY operation, the default action is to overwrite the entire table with the archived table definition and data. Essentially, this is the same as a full-table restore.

For example, if PARTITIONS WHERE is omitted when restoring a single-partition backup, data is dropped from the table and the single partition stored on the archive is restored.

To solve this problem, always specify PARTITIONS WHERE or ALL PARTITIONS when restoring partitions into an existing table. Otherwise, the existing table will be overwritten.

Know What Partitions are Being Deleted – In a RESTORE or COPY operation, all partitions that match the PARTITIONS WHERE condition are deleted, even if they are not stored on the archive.

For example, if an archive is restored that:

Contains the data for April 2007

Has a PARTITIONS WHERE condition that matches both March and April 2007

the data for March and April 2007 are deleted, and only April 2007 is restored.

Therefore, be careful when using PARTITONS WHERE. If there is any doubt about which partitions are affected, COPY the selected partition backup to a staging table, and manually copy the desired partition(s) into the target table using INSERT ... SELECT and/or DELETE.

Avoid Restoring From a Previous Partitioning Scheme – When changing the partitioning expression for a table, changing the boundaries of existing partitions is feasible. If these partitions are restored, Teradata might drop more data than expected or restore less data than expected, if the archive does not include data for all of the selected partitions.

For example, if an archive is done on a table partitioned by month with the archive data corresponding to March 2004, and the table is re-partitioned by week, then a PPI restore of the March backup (using ALL PARTITIONS) overwrites the data for all weeks that contain at least one day in March. As a result, the last few days of February and the first few days of April might be deleted and not restored.

Therefore, avoid restoring partition backups from a previous partitioning scheme to an updated table. Or, use LOG WHERE for the weeks that contain days in both March and February/April, and manually copy the rows into the table.

Track the Partitions in Each Archive – Manual steps are required to determine which partitions are archived by a given backup job, or to determine which backup job has the latest version of a given partition. ANALYZE displays the Teradata-generated bounding condition that defines the archived partitions. (This differs from a user-entered condition that might only qualify partial partitions.) In this case, inconsistent or old data might be restored to the table if the wrong archive is restored for a partition, or if partition-level archives are restored out-of-order and the archives contain an overlapping set of partitions.

For example, updated data is lost in the following situation. Assume that a final backup for a March 2007 partition is performed on April 1, 2007. On April 5, a mistake is found in a row dated March 16, so the row is updated, and a new backup of the March partition is done. If, for instance, the table is accidentally deleted a month later, and an attempt is made to restore the April 1 backup instead of the April 5 backup, the updated data is lost.

To determine the partitions in each archive, keep track of the partition contents of each archived table, retain the output listing associated with a tape, or run ANALYZE jobs on archives.