Copy Temporal Tables | Teradata Data Mover - 17.10 - About Copying Temporal Tables - Teradata Data Mover

Teradata® Data Mover User Guide

Product
Teradata Data Mover
Release Number
17.10
Release Date
June 2021
Content Type
User Guide
Publication ID
B035-4101-061K
Language
English (United States)

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 a force_utility is not specified, Data Mover automatically selects Teradata DSA when copying temporal tables. If DSA is not available, the Teradata PT update operator is selected. If you force the use of Teradata PT, the Teradata PT update operator is chosen as the copy method. When copying entire databases with DSA, all temporal tables on the source are copied to the target. When copying a temporal table, you can rename or relocate temporal tables on the target system or perform partial copies of the 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 primary key constraint of a temporal table is maintained through a system-defined join index. This system-defined join index is automatically generated by the Teradata Database when the table is created. When copying a temporal table, you do not need to separately copy the system-defined join index. DSA copies the system-defined join index setting. For other copy methods, the system-defined join index already exists on the target if the table already exists or it is automatically generated when the table is created as part of the job.

The following scenarios result in an error when copying temporal tables:
  • Copying using the TPT API LOAD operator when the allowTPtLoadForMultiset attribute is not set to true.
  • Copying the temporal table and its system-defined join index as as a separate item rather than copying only the temporal table.