All Forums Database
Kishore_1 208 posts Joined 03/10
03 May 2014
Identifying Temporal Tables

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

Raja_KT 1246 posts Joined 07/09
06 May 2014

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,

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

ulrich 816 posts Joined 09/09
06 May 2014

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.
 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Raja_KT 1246 posts Joined 07/09
06 May 2014

 

 

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)

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

dnoeth 4628 posts Joined 11/04
07 May 2014

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'

 

Dieter

Kishore_1 208 posts Joined 03/10
08 May 2014

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?
 

Fred 1096 posts Joined 08/04
13 May 2014

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.

You must sign in to leave a comment.