COPY Examples - TARA/ABU

Teradata Archive/Recovery Utility Reference

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

COPY Examples

The following examples illustrate data and journal table copies to all AMPs. Examples include copying:

  • A table to a different configuration
  • A data table to a new database
  • A table to a new table or database
  • A database to a new database
  • Two databases with fallback
  • A journal table from two archives
  • Copying a Table to a Different Configuration

    An archived data table named Personnel.Department is copied to a different Teradata Database in this example:

    COPY DATA TABLE (Personnel.Department)
       ,FILE = ARCHIVE;

    The table has the same name in the target database that it had in the archived database, and belongs to a database named Personnel on the target Teradata Database. In the example, if:

  • A database named Personnel does not exist on the target database, Teradata ARC rejects the copy operation.
  • A table named Personnel.Department does not exist in the target database, Teradata ARC creates one.
  • The data table being restored has permanent journaling on the source system, it has permanent journaling on the target system as well.
  • Teradata ARC creates a new table for the copy operation, the target journal table is the default journal for database Personnel. Otherwise, the target journal table is the journal table associated with the table that is being replaced on the target system.
  • Teradata ARC creates a new table for the copy operation and the Personnel database does not have a default journal, then Teradata ARC rejects the copy operation.
  • Copying a Data Table to a New Database

    In this example, an archived data table is copied under a new database name to a different Teradata Database:

    COPY DATA TABLE (Personnel.Department)
       (FROM (OldPersonnel), NO JOURNAL, NO FALLBACK)
       ,FILE = ARCHIVE;

    In this example:

  • The new database name is Personnel; the old database name is OldPersonnel. In both databases, the table name is Department.
  • If a database named Personnel does not exist in the target system, Teradata ARC rejects the copy operation.
  • If a data table named Department does not exist in the Personnel database on the target system, Teradata ARC creates one with that name.
  • The NO JOURNAL option indicates that this table should not have permanent journaling in the target database.
  • The NO FALLBACK option indicates that the new table should be nonfallback on the target system, even if it was a fallback table on the archived system.
  • Copying a Table to a New Table or Database

    An archived data table is copied under new database and table names to a different Teradata Database in this example:

    COPY DATA TABLE (Personnel.Department)
       (FROM (OldPersonnel.Dept)
       ,WITH JOURNAL TABLE = PersonnelJnlDB.PermanentJournal
       ,NO FALLBACK)
       ,FILE = ARCHIVE;

    In the example:

  • The new database name is Personnel; the old database name is OldPersonnel. The new data table name is Department; the old data table name is Dept.
  • If a database named Personnel does not exist in the target system, Teradata Archive/Recovery Utility rejects the copy operation.
  • If a data table named Department does not exist in the Personnel database on the target system, Teradata ARC creates one with that name.
  • The WITH JOURNAL option indicates to carry journaling over from the old Teradata Database to the new Teradata Database for this table. In the example, the user copies journal images into the permanent journal table named PersonnelJnlDB.PermanentJournal.
  • The NO FALLBACK option indicates that the new table is to be nonfallback on the target system, even if it was a fallback table on the archived system.
  • If the table was nonfallback on the archive, then it retains its journaling options on the target system (that is, the journal options do not change).
  • If the table was fallback on the archive, then its images are dual on the target system.
  • Copying a Database to a New Database

    In this example, an archived database is copied with a new name to a different Teradata Database:

    COPY DATA TABLE (Personnel)
       (FROM (OldPersonnel)
       ,WITH JOURNAL TABLE = PersonnelJnlDB.PermanentJournal
       ,NO FALLBACK)
       ,FILE = ARCHIVE;

    In the example:

  • The new database name is Personnel; the old database name is OldPersonnel.
  • If a database named Personnel does not exist in the target system, Teradata ARC rejects the copy operation.
  • The WITH JOURNAL option indicates to carry journaling over from the old Teradata Database to the new Teradata Database for any tables in the copied database that have journaling enabled on the archived system. In the example, the user copies journal images into the permanent journal table named PersonnelJnlDB.PermanentJournal. Although the tables are copied with the specified journal, the target database default does not change.
  • The NO FALLBACK option indicates that the new table is to be nonfallback on the target system, even if it was a fallback table on the archived system.
  • If the table was nonfallback on the archive, then it retains its journaling options on the target system (that is, the journaling options do not change).
  • If the table was fallback on the archive, its images are dual on the target system.
  • Although Teradata ARC copies the tables as nonfallback, the default for the target database does not change. For example, if the database named Personnel is defined with fallback, the database keeps that default after the copy operation is complete. The NO FALLBACK option applies only to the tables within the archive.

    Copying Two Databases With Fallback

    Here, two databases are copied that have different fallback attributes:

    COPY DATA TABLE (Personnel)
       (FROM (OldPersonnel), NO FALLBACK)
       ,(Finance) (NO JOURNAL)
       ,FILE = ARCHIVE;

    In the example:

  • The database named Personnel is restored from an archived database named OldPersonnel with all tables defined as nonfallback after the copy operation.
  • The database named Finance is restored from an archived database also named Finance with all journaling associated with its data tables disabled.
  • Copying a Journal Table From Two Archives

    This example illustrates how a journal table that has some nonfallback images spread across two separate archive files (because an AMP was down at the time of an all-AMPs archive operation) is restored:

    COPY JOURNAL TABLE (PersonnelJnlDB.PermanentJournal)
       (APPLY TO (Personnel.Employee, Personnel.Department))
       ,NO BUILD
       ,FILE = ARCHIVE1;
    COPY JOURNAL TABLE (PersonnelJnlDB.PermanentJournal)
       (APPLY TO (Personnel.Employee, Personnel.Department))
       ,FILE = ARCHIVE2;

    When restoring to a different configuration, Teradata ARC redistributes journal images among all the AMPs. Teradata ARC builds the journal only after it completes the all-AMPs data table archive operation.

    To perform this operation:

  • Copy the all-AMPs archive and specify the NO BUILD option.
  • Copy the specific-AMP archive, but do not specify the NO BUILD option.
  • This operation is only allowed if the applicable data tables are already copied into the target system. In the example, the archive of all AMPs (except the AMP that was offline) is called ARCHIVE1. The archive of the AMP that was offline at the time of the main archive is called ARCHIVE2.

    Teradata ARC copies only checkpoint journal images and images associated with the tables named Personnel.Employee and Personnel.Department to the target system. Teradata ARC discards all other journal images.