About Copying Temporal Tables - Teradata Data Mover

Teradata Data Mover User Guide

Product
Teradata Data Mover
Release Number
16.00
Published
December 2016
Language
English (United States)
Last Update
2018-03-29
dita:mapPath
rdo1467305237457.ditamap
dita:ditavalPath
ft:empty
dita:id
B035-4101
lifecycle
previous
Product Category
Analytical Ecosystem

In a conventional database system, data stored in tables is considered valid at the present time. Because conventional tables are limited to a current snapshot of reality, they do not retain data that was present in the past or that will be valid in the future. A temporal database allows you to store data related to points in time by providing temporal data types and storing information related to the past, present, and future. For example, a table in a temporal database system can store data about inventory levels at the end of each month of the year. By storing the temporal dimensions of data in tables, you can query historical and prospective data trends.

A temporal table contains one or both of the following:
Time Description

ValidTime

Data modeled on the real world using a time period during which a fact (a row) is true (valid) in the real world. A value for the DATE or TIMESTAMP column in the table defines the time that a fact is known to be valid.

TransactionTime

Data using a time period beginning at the moment a fact (a row) is recorded (committed) in the database and ending when the fact is superseded through an update, rollback, or deletion. A value for the TIMESTAMP column in the table defines the transaction time of the fact.

You can copy a temporal table, associated join and hash indexes, and triggers to the target system. To copy temporal tables from source to target, you must have NONTEMPORAL privileges on the target database. In the following XML example, the temporal table in Employee_BT_1 is copied to the target system:
<table selection = "included">
 <name>Employee_BT_1</name>
  <target_database>
  	<name>MyDatabase</name>
  </target_database>
</table>
If <force_utility> is not specified and Teradata ARC can be used, Data Mover automatically selects Teradata ARC when copying temporal tables. If Teradata ARC cannot be used, the Teradata PT API UPDATE operator is chosen by default as the copy utility. If you specify Teradata PT API as the value for <force_utility>, Teradata PT API UPDATE is chosen as the copy utility. When copying entire databases with Teradata ARC, all temporal tables on the source are copied to the target. When copying, you can rename or relocate temporal tables on the target system, or perform partial table copies of temporal tables.
When copying temporal tables, non-sequenced queries select the source rows that need to be copied to the target. For example:
<NONSEQUENCED VALIDTIME> <AND> <NONSEQUENCED TRANSACTIONTIME> SELECT * FROM Source_Table; 
Non-sequenced queries do not place any special semantics on temporal columns, treating them as any other regular columns. Therefore all current, history, and future rows that are open or closed are copied to the target.
The following scenarios result in an error when copying temporal tables:
  • Copying to or from a version of Teradata Database earlier than 13.10.
  • Copying using the Teradata PT API LOAD operator when the allowTPtLoadForMultiset attribute is not set to true.