Identifying Temporal Tables

Database
Enthusiast

Identifying Temporal Tables

Is there any way to identify temporal tables using data dictionary (DBC) tables in TD 13.10 and above?

-Thanks

Tags (1)
6 REPLIES
Enthusiast

Re: Identifying Temporal Tables

Hi Kishore_1,

I am glad that your period data type problem is resolved. There are many functions  available in teradata temporal features.

I am not able to see any indicator in any dictionary table.

My suggestion, is that , if we have many temporal tables, better place them in a separate database for structured maintenance, ease of management etc. Also we can  trace temporal from  dbc.tvm, requesttext.

I see that implementation of temporal features is few and far between.

From my standpoint, I see that having a separate indicator for this in dictionary table, may or may not be good. It is just my opinion.

Cheers,

Senior Supporter

Re: Identifying Temporal Tables

In 14.10 the dbc documentation states that tables views should have the column

TemporalProperty

Further down you find the following description:

TemporalProperty Column

The TemoralProperty column indicates that the table, view, or join index is a nontemporal, ValidTime, TransactionTime, or bi-temporal table.

 

Enthusiast

Re: Identifying Temporal Tables

 

 

Plz doubke check. I am on 14.10.

 

create set table retail.temp1(id integer, dob period(date) format 'YYYY-MM-DD');

 

SELECT * FROM DBC.COLUMNS WHERE DATABASENAME='retail' and tablename='temp1',

then you will see columntype='PD' ------PD PERIOD(DATE)

 

Similarly, you can get dob period(timestamp)

columntype=PS     PERIOD(TIMESTAMP (n))

 

However, you may need to join dbc.columns with dbc.tables to filter only for tables only.

 

period data type

PD---PERIOD(DATE)

PM---PERIOD(TIMESTAMP(n) WITH TIMEZONE)

PS---PERIOD(TIMESTAMP (n))

PT---PERIOD(TIME(n))

PZ---PERIOD (TIME(n) WITH TIME ZONE)

Junior Contributor

Re: Identifying Temporal Tables

The column Ulrich mentioned exists since TD13.10, so to get all Temporal tables:

SELECT * FROM dbc.TablesV WHERE TemporalProperty <> 'N'

Similar to find the Temporal columns:

SELECT * FROM dbc.ColumnsV WHERE TimeDimension <> 'N'
Enthusiast

Re: Identifying Temporal Tables

Thanks Ulrich,Raja and  Dieter for the valuable information on identifying temporal tables in Teradata.

Could you please help me in answering: Which DBS control parameter(s) should be enabled to enable this feature on TD13.10 and above?

Does it require a system restart?

Teradata Employee

Re: Identifying Temporal Tables

On a real Teradata system, this requires a change control and verification of entitlement. (Yes, it requires a restart.) Talk to your local Teradata sales and Customer Services team for assistance.

For Teradata Express VMs, the feature should be enabled.