How can I test the alter table to current command

Database
Highlighted
Enthusiast

How can I test the alter table to current command

I am writing code to manage archiving and purging of data.  We have many tables partitioned with clauses like:

 

PARTITION BY RANGE_N(EFF_DT  BETWEEN DATE - 397  AND DATE + 45  EACH INTERVAL '1' DAY )

 

I'd like to be able to test my code for doing an 'alter table to current' with either 'insert into' or delete.  But how can I test my code without having to wait for days for the date to change?  I'd like to be able run tests, reset and run again.

 

Can anyone give me some advice?

 

Thanks,


Accepted Solutions
Junior Contributor

Re: How can I test the alter table to current command

You can use DIAGNOSTIC "setcurrentdate=1180511" ON FOR SESSION; using the internal date format, 1180511 = 2018-05-11.

 

But why don't you need to partition based on CURRENT_DATE? I prefer defining the range far into the future (so far, that I never have do modify it) and instead of Alter Table I simply delete the old rows (empty partitions don't have any overhead).

1 ACCEPTED SOLUTION
3 REPLIES
Teradata Employee

Re: How can I test the alter table to current command

I believe if you change the OS date on your test system, e.g. [sudo] date +%Y%m%d -s "20180512", and then do a TPAReset to restart Teradata, it will pick up the new date.  Do a "Select current_date" to check it, and if the date has changed, try an insert.

Junior Contributor

Re: How can I test the alter table to current command

You can use DIAGNOSTIC "setcurrentdate=1180511" ON FOR SESSION; using the internal date format, 1180511 = 2018-05-11.

 

But why don't you need to partition based on CURRENT_DATE? I prefer defining the range far into the future (so far, that I never have do modify it) and instead of Alter Table I simply delete the old rows (empty partitions don't have any overhead).

Enthusiast

Re: How can I test the alter table to current command

Thanks for the responses.  I'm intrigued by the diagnostic setcurrent date command.  I'll give it a try.

 

I've already programmed the archive/purge process to handle insert into ... select from .... and delete....

 

I was creating the process to handle the situations where alter table to current would be needed when I got stumped on how I could test it in a timely manner.