Temporal capability and transaction-time ...

Database
Teradata Employee

Temporal capability and transaction-time ...

Dear Teradata fan community,

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.

Best regards,
Mehrdad
8 REPLIES
N/A

Re: Temporal capability and transaction-time ...

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
Teradata Employee

Re: Temporal capability and transaction-time ...

Many thanks for your feedback and your confirmation Dieter.

Mehrdad
Teradata Employee

Re: Temporal capability and transaction-time ...

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.

At least one way to fulfil this is:

1. dbscontol:
55. EnabNonTempoOp = TRUE
(See manual B035-1102-109A, pages 358ff)

2. Set the appropriate NONTEMPORAL right for the user. (see manual B035-1182-109A, pages 170ff.)

Run the respective queries as NONTEMPORAL.

We have been able to move data from a NON temporal to a temporal table, both had exact same DDL but the temporal definitions (period attributes for VT & TT existed but not defined as temporal).

Teradata suggests and defaults dbscontrol 55 to DISABLED. Accounts using temporal probably will have to set it to TRUE almost in all cases.

Rolf
N/A

Re: Temporal capability and transaction-time ...

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.

Dieter
Teradata Employee

Re: Temporal capability and transaction-time ...

Hello Dieter,

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.

Rolf
Teradata Employee

Re: Temporal capability and transaction-time ...

Hello

just to add Rolfs comment:

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".

Patrick

Re: Temporal capability and transaction-time ...

hi , everone , how to learn to TerraData, First,
basical presently working java, I have change to TerraDataportlets, how to do?

Re: Temporal capability and transaction-time ...

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?