we know, that since release 13.10 there exists temporal capability within the database. My understanding specially about the transaction time is, that this data will be handled automatically within the DBMS as described as follow:
"A transaction-time column records and maintains the time period for which Teradata Database was aware of the information in the row. Teradata Database automatically enters and maintains the transaction-time column data, and consequently automatically tracks the history of such information."
So there exists no way to manipulate the data of a transaction-time column within a table is my conclusion.
And what i have to do, if the transaction time within my DWH has to reflect the transaction time of the front system? I will be very happy for any feedback.
Just read carefully: "A transaction-time column records and maintains the time period for which **Teradata Database** was aware of the information in the row"
It's "Teradata Database", not "source system".
If you want to use the source systems transaction time, then simply don't use AS TRANSACTIONTIME in the column's definition.
And if you read on: "You cannot normally set or modify the value of a transaction-time column. Teradata Database maintains these values automatically. (However, for database maintenance and troubleshooting, closed rows can be modified or deleted by administrators who have been granted the NONTEMPORAL privilege.)"
Dieter - basically agreed. As the standard implementation in Teradata this is it. There are two things to add:
- If it is true that TRANSACTIONTIME is strictly system maintained by all means this would be a killer criteria to use this function ever. In every life of a DWH you have the need of logical reorganisations (table recreate), initial load etc. An information loss on TRANSACTIONTIME would then be the result.
- Let's go one step back and look at the temporal feature as a whole. There are two separate tasks that want to be supported / simplified: The loading task and the end user query task. It is reasonable to ask for the support of the end user task only whereas in the loading task Teradata's support is not wanted. This is the case when e.g. a legacy system’s transaction time should be stored and still we want to simplify the end users queries with the temporal feature on TRANSACTIONTIME. Obvious, that we are responsible for correctness of the data in this case ourselves.
Hi Rolf, i agree, too :-) I didn't think about the select part, of course you would loose that easy way to query the table if you omit AS TRANSACTIONTIME.
But if you want to keep the original transaction timestamp from the source system using NONTEMPORAL, don't you have to do the historization manually again? I guess i only started digging into all the details of temporal queries.
Yes, this is what I meant with „Obvious, that we are responsible for correctness of the data in this case ourselves.”
As a starter to dig into the subject, the document “A Case Study of Temporal Data” explains nicely the logical fundamentals. What I severely missed is that it lacks the definition of the primary key (NOT index!) in the DDL - this is a key point in this area. Note, by defining a primary key, a system created Join Index will appear that will avoid duplicates that can be created in the examples of the study. For the time being I do not see a logical reason why a PK is not mandatory here, too easy to corrupt the table without:
CREATE MULTISET TABLE Prop_Owner_pk ( customer_number INTEGER , property_number INTEGER NOT NULL , property_VT PERIOD(DATE) NOT NULL AS VALIDTIME , property_TT PERIOD (TIMESTAMP(6) WITH TIME ZONE) NOT NULL AS TRANSACTIONTIME /**/, CURRENT TRANSACTIONTIME AND CURRENT VALIDTIME PRIMARY KEY (property_number) ) PRIMARY INDEX(property_number);
Try to create the table with and without PK and apply this insert twice:
INSERT INTO Prop_Owner_pk (1,1);
Then select the table content. Uups - Two records w/o PK, of course wrong.
As a side effect - Temporal tables with PK must have unique names within the first 23 chars.
This seems to be a pretty interesting area which we'll have to get light into.
By creating a temporal table with a primary key the Teradata database creates a system-defined join index on that column not really a primary key, which is in fact a really sophisticated way to solve that issue. But this inhibits our ability to create a copy of this table by a "Create Table ... As Select ..." statement. It will work but only with the "No Data" Option. This fact doesn't really come through in the "Temporal table support" document.
I think there is still a little more to dig, to ensure we get things right "the temporal way".
Hi All, Can you pls help me understand how to add new columns to Bitemporal tables without NONTEMPORAL Priviledge. i tried alter table but its throwing error 9328. if i use NONTEMPORAL alter command, will it cause any changes to the data?