EXCLUDE TABLES Keyword - Teradata Tools and Utilities

Teradata® Archive/Recovery Utility Reference

Product
Teradata Tools and Utilities
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-06-05
dita:mapPath
fac1527114221922.ditamap
dita:ditavalPath
Audience_PDF_include.ditaval
dita:id
B035-2412
Product Category
Teradata Tools and Utilities

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 Excluding Tables.

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.