Using Keywords with RESTORE
RESTORE is affected by the following keywords. For information about the keywords that apply to restoring partitioned data, see “Restores of Selected Partitions” on page 229.
RESTORE DICTIONARY TABLE Keywords
Use the all-AMPs journal archive to restore journal dictionary rows to a Teradata Database that has been reconfigured since the journal archive was created. If the dictionary is restored for an entire database, Teradata ARC restores the definitions for all the objects belonging to that database. If specific tables are restored, Teradata ARC restores only the definitions for those tables.
AMP Keyword
Do not specify this option for:
With Teradata Database, use the AMP=n option to specify up to five AMPs.
This option applies only with the NO FALLBACK TABLE or JOURNAL TABLE options. It is useful following all-AMPs restores when all AMPs are not available at the time of the operation and non-fallback or journal receiving tables are involved.
When restoring journal tables to specific processors, specify the processor to recover with the journal.
If the journal to restore contains after images on a backup processor, the journal rows restored by Teradata ARC are sent to the backup processor for the AMP that is specified in the statement.
CLUSTER Keyword
The following rules apply to this option:
Teradata ARC builds all indexes except unique indexes. Do not update a fallback table with unique indexes using Teradata SQL after a cluster restore until a build is performed.
If any AMPs in the list of clusters are offline, restore any non-fallback table to those AMPs when they are brought back online. A specific AMP restore can be from a specific AMP or cluster archive.
Teradata ARC releases HUT locks at the cluster level.
RESTORE FALLBACK Keywords
If this option is not specified, Teradata ARC restores only a single copy and then builds the fallback copy.
If an AMP is down during either the archive or the restore, Teradata ARC does not restore non-unique indexes.
If the RESTORE FALLBACK and NO BUILD options are both specified, one of the following occurs:
If the RESTORE FALLBACK and NO BUILD options are both specified to a cluster level archive and the restore is to all AMPs, Teradata ARC does not build indexes or revalidate the primary subtables. In this case, the table is not fully restored. To access the table using Teradata SQL, do one of the following first:
When restoring a set of cluster archives to a reconfigured Teradata Database, restore each cluster archive to all‑AMPs. Use the RESTORE FALLBACK option on all of the cluster restore operations or on none of the cluster restore operations. Otherwise, if the archive contains fallback tables, Teradata ARC reports an error and terminates the restore operation.
Use NO BUILD for all but the last all-AMPs restore operation. NO BUILD is optional for the last all-AMPs restore operation.
When restoring a set of cluster archives to the same configuration, always perform cluster level restore operations.
If the RESTORE FALLBACK option is specified without the NO BUILD option, the restore operation continues processing on a reconfigured system instead of restarting.
Note: If a fallback table is being processed, this option disables the HALT and PAUSE runtime parameters until the restore completes.
NO BUILD Keywords
These keywords prevent fallback data and secondary indexes from being built on fallback tables when restoring cluster archives to all AMPs. Use this option if restoring cluster archives to a Teradata Database that was reconfigured after the cluster archives were created.
When restoring journal tables to a Teradata Database that has been reconfigured since the journal archive was made, specify NO BUILD to prevent sorting of the restored journal and distribution of fallback rows. This procedure is useful when restoring more than one journal archive (that is, restoring an all-AMPs journal archive and one or more specific AMP journal archives).
If the NO BUILD keywords are used with RESTORE, run a separate BUILD statement for all databases and/or tables that were restored. The tables will not be accessible until a BUILD statement is run.
If the NO BUILD keywords are used while restoring a database or table that contains Join or Hash indexes, the database/table will be in a 'restoring' state when Teradata ARC attempts to restore 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 restore of the Join/Hash index to fail.
In order to restore 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 Restore the Join/Hash index by rerunning the restore job and just specifying the Join/Hash indexes to be restored as individual objects in the restore list.
If the NO BUILD keywords are used while restoring a database or table that contains Stat Collections, use the same procedure above for Join/Hash indexes in order to restore 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 restored when using ALL FROM ARCHIVE. All databases and tables in the given archive file will be restored, 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 restore database DBC, and DBC must be excluded by the user if it is present in the archive being restored.
The EXCLUDE TABLES object option is not allowed when using ALL FROM ARCHIVE.
The ALL FROM ARCHIVE command cannot be used to restore 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 51 for more details.
EXCLUDE TABLES Keyword
During a database-level RESTORE, the EXCLUDE TABLES option can be used to skip the restore of one or more objects from the archive file to the target database. If a object is excluded, Teradata ARC will not restore any of the dictionary information or any of the object data for the specified object(s). If any of the specified object(s) already exist in the target database, they are unaffected by the RESTORE operation. By not restoring any information/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 restore.
Use the EXCLUDE TABLES option to skip the restore of a object from the archive file. If the object already exists in the target database, it will be unaffected by the RESTORE operation. If the RESTORE 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 restore of an excluded object from the archive file. When a 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 restored, the target object will be 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 will be restored, which will produce 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 restore of that object. By using the EXCLUDE TABLES option, that object will be unaffected by the RESTORE 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 RESTORE of a database-level object, all objects in the target database are dropped before the objects from the archive file are restored to it. If a new object has been added to the target database since the archive was taken, you will have a situation where the archive file does not have any archived data for that new object. If you were to run a restore without the EXCLUDE TABLES option, Teradata ARC will drop that new object from the target database even though there is no data in the archive file to restore for that object. To prevent dropping the new object during the restore, use the EXCLUDE TABLES option to specify the new object in the target database. This will tell Teradata ARC to not drop that object from the target database during the restore. By using the EXCLUDE TABLES option, the new object in the target database will be unaffected by the RESTORE operation.
If the ALL keyword is not specified after the object name, 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 database that it is associated with in the RESTORE statement.
If the ALL keyword is specified after the object name, then only fully qualified object names in the form of databasename.tablename are accepted in the list of EXCLUDE TABLES.
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 restore of the database will be skipped.
For a list of individual object types that can be specified in the EXCLUDE TABLES clause for Restore, 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.