Drop a Temporal Table

Database
Enthusiast

Drop a Temporal Table

How can I drop the below table ?? Thanks in advance .. 

 

SyntaxEditor Code Snippet

CREATE MULTISET TABLE mydb.employee1(empid INT NOT NULL,ename VARCHAR(12),deptno INT NOT NULL,jobduration PERIOD(DATE) NOT NULL AS VALIDTIME,db_trans_time PERIOD(TIMESTAMP (6) WITH TIME ZONE) NOT NULL AS TRANSACTIONTIME)PRIMARY INDEX (empid)PARTITION BY CASE_N(
   ( 
     (END(jobduration) IS NULL)
  OR((END(jobduration)) >= (CURRENT_DATE - INTERVAL '2' DAY) )
 AND(( END(db_trans_time)) >= (CURRENT_TIMESTAMP(6)))
   ),

(( END(jobduration)) < (CURRENT_DATE - INTERVAL '2' DAY) )AND(( END(db_trans_time)) >= (CURRENT_TIMESTAMP(6)) ),END(db_trans_time) < CURRENT_TIMESTAMP(6)
)

 

10 REPLIES
Junior Contributor

Re: Drop a Temporal Table

DROP TABLE mydb.employee1;?

Senior Supporter

Re: Drop a Temporal Table

what is the issue with drop table?

 

CREATE MULTISET TABLE employee1 (empid INT NOT NULL,
ename VARCHAR(12),
deptno INT NOT NULL,
jobduration PERIOD (DATE) NOT NULL AS VALIDTIME,
db_trans_time PERIOD (TIMESTAMP (6) WITH TIME ZONE) NOT NULL AS TRANSACTIONTIME
) PRIMARY INDEX (empid) PARTITION BY CASE_N (((END (jobduration) IS NULL) OR ((END (jobduration)) >= (CURRENT_DATE-INTERVAL '2' DAY)) AND ((END (db_trans_time)) >= (CURRENT_TIMESTAMP(6)))),((END (jobduration)) <(CURRENT_DATE-INTERVAL '2' DAY)) AND ((END (db_trans_time)) >= (CURRENT_TIMESTAMP(6))),END (db_trans_time) < CURRENT_TIMESTAMP(6))



insert into employee1 (empid,ename, deptno,jobduration)  values (1, 'asd',3,period('2017-01-01' (date), until_changed))

select * 
from employee1

drop table employee1;
Junior Apprentice

Re: Drop a Temporal Table

Have you tried: "drop TABLE mydb.employee1"?

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Drop a Temporal Table

I should have explained more .. This table was created by a user who is bo longer with the company. Since this person have this table created in his DB, we are not able to drop his ID from the system. So I am trying to drop the table using DBC and getting the below error .. 

 

SyntaxEditor Code Snippet

DROP TABLE Failed 3706: Syntax ERROR: Temporal operations are NOT supported ON this SYSTEM.

 

Junior Apprentice

Re: Drop a Temporal Table

So it looks like the Temporal feature used to be enabled and now is disabled. I've come across this before.

 

Have you tried:

ALTER TABLE to drop the temporal columns? Then just drop the table.

 

If you can't do anything then I think:

Re-enable Temporal using DBSControl (you might have to speak to TD about this if it involves 'licenseable options')

Drop the table.

Disable Temporal.

 

Cheers,

dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Junior Contributor

Re: Drop a Temporal Table

How was the Temporal feature disabled, it's a one-way switch and can't be set to false after enabling it?

Was the table restored from a backup?

 

Does DELETE USER work? (probably not)

Enthusiast

Re: Drop a Temporal Table

Thanks Dave .. We are attempting this option of turning on the temporal and to drop the table and user. 

Enthusiast

Re: Drop a Temporal Table

Thanks Dnoeth. It was a Teradata consultant who created this table. Not restored from backup.

 

I attempted the delete user id; .. it supposedly ran successfully, but his account still exist in dbc.databases and dbc.users. So I guess it didn't really work .. 

Highlighted
Junior Contributor

Re: Drop a Temporal Table

DELETE USER only cleans the database, i.e. drops all objects.

Check if the table still exists.