Using Keywords with COPY - TARA/ABU

Teradata Archive/Recovery Utility Reference

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

COPY is affected by the following keywords. For information about the keywords that apply to copying partitioned data, see “Restores of Selected Partitions” on page 233.

NO FALLBACK Keywords

This option applies only during a copy of a dictionary archive or an all-AMPs archive.

If a fallback table has permanent journaling on the archive, the table has dual journaling of its non-fallback rows after the copy when Teradata ARC applies the NO FALLBACK option (unless NO JOURNAL is specified).

FROM Keyword

The object specified in the FROM keyword identifies the archive object. This option applies only during a copy of a dictionary archive or an all-AMPs archive.

Journal enabled tables in the original archive carry their journaling forward to the copy unless NO JOURNAL keywords are specified.

The NO JOURNAL keywords apply to all tables in a database when a database is copied. This option has no effect on a target database’s journaling.

If the object specified in the FROM option is a table, Teradata ARC copies only that table.

If the object specified in the FROM option is a database, one of the following occurs:

  • If the receiving object is a table, then Teradata ARC searches through the archived database for the named table and copies the table.
  • If the receiving object is a database, then Teradata ARC copies the entire database into the receiving database.
  • Copying into database DBC is not valid, however specifying database DBC as the object of the FROM option is valid. In other words, copy database DBC into another database.

    Similarly, do not copy into database SYSUDTLIB. Specify database SYSUDTLIB as the object of the FROM option. That is, copy database SYSUDTLIB into another database.

    When specifying the FROM and EXCLUDE TABLES options in the same COPY statement, specify the FROM clause first. Then Teradata ARC can generate a default source database name for the excluded tables from the FROM clause if a database name is not specified in the EXCLUDE TABLES clause.

    WITH JOURNAL TABLE Keywords

    This option only applies during a copy of a dictionary archive or an all-AMPs archive. To use this option, INSERT access privileges to the referenced journal table are required.The source table must have a journal or this option has no effect.

    When copying a database, the journaling specified with this option applies only to those tables that had journaling enabled in the original database. This option has no effect on a receiving database’s default journaling.

    If the database has default journaling specified, Teradata ARC uses those options. This option only overrides the journal table in the receiving database, and is only valid if the originating table had journaling enabled.

    To carry journaling from the original table forward without specifying the WITH JOURNAL TABLE option, Teradata ARC uses one of the following:

  • The default journal of the receiving database if the table is to be created. If there is no default journal, Teradata ARC rejects the copy.
  • The journal specified in the WITH JOURNAL option when the table was created.
  • The journal of the table being replaced if the table already exists.
  • APPLY TO Keywords

    This option is required when copying journal images. Restore access privileges to each table that is necessary. Applying journal images to as many as 4096 tables is valid, however, all of the table names in the APPLY TO option cannot exceed 8 KB.

    When a journal table is copied, Teradata ARC restores only checkpoint rows and those rows that apply to the receiving environment. The tables to which those images apply and their journaling options must have already been copied. Thus, to perform a COPY JOURNAL with an APPLY TO option, the appropriate COPY DATA TABLE statement must have been issued at least once prior to using COPY JOURNAL to establish the source/target tableid mapping in database DBC.DBCAssociation table. Teradata ARC copies only those images that have the correct table structure version (as defined by the originating table).

    If a journal image for the source table is applied to more than one target table, Teradata ARC writes two occurrences of the journal image into the restored journal.

    CLUSTER Keyword

    This option is valid only if the source archive is a cluster archive and the copy is to the identical Teradata Database. The option is useful in restoring or copying a dropped table to a different database on the same Teradata Database.

    AMP Keyword

    With Teradata Database, use the AMP=n option to specify up to five AMPs.

    Only the NO FALLBACK TABLE or JOURNAL TABLE options are applicable. It is useful following all-AMPs copies where all AMPs were not available at the time of the copy operation and non-fallback or journal receiving tables were involved.

    NO BUILD Keywords

    Use the NO BUILD keywords for all archives except the last one. Omit the NO BUILD option for the last archive, so that Teradata ARC can:

  • Build indexes for tables.
  • Sort change images for journal tables.
  • If the NO BUILD keywords are used during a RESTORE or COPY statement, run a separate BUILD statement for all databases and/or tables that were restored. The tables are not accessible until a BUILD statement is run.

    If the NO BUILD keywords are used while copying a database or table that contains Join or Hash indexes, the database/table will be in a 'restoring' state when Teradata ARC attempts to copy the Join/Hash indexes. When in the 'restoring' state, the Teradata Database will not allow any access to the database/table. This will cause the copy of the Join/Hash index to fail.

    In order to copy the Join/Hash indexes, follow this procedure:

    1 Run a separate BUILD job to build the fallback rows and secondary indexes for the database/table. This will clear the 'restoring' state and allow user access to the database/table.

    2 Copy the Join/Hash index by rerunning the copy job and just specifying the Join/Hash indexes to be copied as individual objects in the copy list.

    If the NO BUILD keywords are used while copying a database or table that contains Stat Collections, use the same procedure above for Join/Hash indexes in order to copy them.

    ALL FROM ARCHIVE Keywords

    The ALL FROM ARCHIVE keywords take the place of the database and/or table names that are normally specified after the DATA, DICTIONARY, JOURNAL, or NO FALLBACK TABLES keywords.

    Do not specify any other database or table names to be copied when using ALL FROM ARCHIVE. Exercise caution when using this option, as all databases and tables in the given archive file will be copied, and any existing databases or tables will be overwritten.

    CATALOG and Fastpath are not supported while using ALL FROM ARCHIVE. If CATALOG (or Fastpath) is enabled when ALL FROM ARCHIVE is specified, it will be disabled and a warning message will be given.

    ALL FROM ARCHIVE cannot be used to copy database DBC, and database DBC must be excluded by the user if it is present in the archive being copied.

    COPY supports the EXCLUDE option; the syntax and function of the option is identical to the RESTORE version of EXCLUDE. Unlike RESTORE, COPY only supports the EXCLUDE option when using ALL FROM ARCHIVE.

    The FROM, WITH JOURNAL TABLE, APPLY TO, and EXCLUDE TABLES object options are not allowed when using ALL FROM ARCHIVE.

    The ALL FROM ARCHIVE command cannot be used to copy a table that has a join or hash index defined or a database that contains a table that has such indexes defined. See “Join and Hash Indexes in Restoring” on page 53 for more details.

    EXCLUDE TABLES Keyword

    During a database-level COPY, the EXCLUDE TABLES option can be used to skip the copy of one or more objects from the archive file to the target database.

    If an object is excluded, Teradata ARC will not copy any of the dictionary information or any of the object data for the specified objects. If any of the specified objects already exist in the target database, they are unaffected by the COPY operation. By not copying any information or data for the specified object, the EXCLUDE TABLES option can also be used to prevent the deletion of existing objects from the target database during a copy.

    Use the EXCLUDE TABLES option to skip the copy of an object from the archive file. If the object already exists in the target database, it will be unaffected by the COPY operation. If the COPY is run without the EXCLUDE TABLES option, the target object is dropped and replaced by the dictionary information and data stored in the archive file for that object.

    Use the EXCLUDE TABLES option to skip the copy of an excluded object from the archive file. When an object is excluded during the archive, the only things archived for that object are the dictionary information and the table header, but no object data. If the excluded object is copied, the target object is dropped and replaced by the dictionary information for that object. But since there is no data in the archive file for that object, no data is copied, which produces an empty object. To prevent replacing an existing object with an empty object due to that object being excluded during the archive, use the EXCLUDE TABLES option to skip the copy of that object. By using the EXCLUDE TABLES option, that object is unaffected by the COPY operation if it currently exists in the target database.

    Use the EXCLUDE TABLES option to prevent the deletion of new objects from the target database. During a normal COPY of a database-level object, all objects in the target database are dropped before the objects from the archive file are copied to it. If a new object has been added to the target database since the archive was taken, a situation exists where the archive file does not have any archived data for that new object. If a copy is run without the EXCLUDE TABLES option, Teradata ARC drops that new object from the target database even though there is no data in the archive file to copy for that object. To prevent dropping the new object during the copy, use the EXCLUDE TABLES option to specify the new object in the target database. This communicates to Teradata ARC to not drop that object from the target database during the copy. By using the EXCLUDE TABLES option, the new object in the target database is unaffected by the COPY operation.

    Either non-qualified object names (without a database name prefix) or fully qualified object names (databasename.tablename) can be specified in the list of EXCLUDE TABLES. If a non-qualified object name is specified, it is presumed to belong to the source database that it is associated with in the COPY statement.

    The EXCLUDE TABLES option can only be used with database-level objects.

    The EXCLUDE TABLES option can not be used when ALL FROM ARCHIVE is specified.

    If a specified object is in neither the archive file nor the target database, the copy of the database will be skipped.

    If the FROM and EXCLUDE TABLES options are specified in the same COPY statement, specify the FROM clause first. Then Teradata ARC can generate a default source database name for the excluded objects from the FROM clause if a database name is not specified in the EXCLUDE TABLES clause.

    If the FROM and EXCLUDE TABLES options are specified in the same COPY statement, the excluded objects in the EXCLUDE TABLES clause can be qualified with either the source database name or the target database name. When the FROM clause is not used, the excluded objects in the EXCLUDE TABLES clause can only be qualified by the source database name.

    For a list of individual object types that can be specified in the EXCLUDE TABLES clause for Copy, see Appendix C.

    Notice:

    There is no explicit maximum length for a EXCLUDE TABLES clause, but when the EXCLUDE TABLES clause specified is too long to fit in the SQL buffer, the SQL request can not be sent to the database. The buffer that is used to send the EXCLUDE TABLES clause to the database has a maximum length and the EXCLUDE TABLES must fit completely within that buffer. Since other data may exist in the buffer and may be variable in length, a max size for a EXCLUDE TABLES clause cannot be given. If the above situation occurs, ARC will display following error message:
    ARC0127: “EXCLUDE TABLES list is too long to fit in the SQL buffer.”
    Modify the EXCLUDE TABLES list to reduce its size and resubmit the job.