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.