CURRENT_DATE/TIMESTAMP Partitioning in Teradata issue..

General

CURRENT_DATE/TIMESTAMP Partitioning in Teradata issue..

Hello All,

I need to create a PPI table in teradata 13 and keep 6 months of data and partition the table by each day.I am planing to use the CURRENT_DATE/TIMESTAMP Partitioning new feature of 13 and partition my table based on current_date.The problem i am facing is with dropping the oldest partiton and adding a new partition on a daily basis.

Suppose i create the table on July 1st, then my partition range needs to be between current_date and current_date - 6 months ('2011-01-01' and '2011-07-01' ).

My table defination:

CREATE TABLE Orders
( o_orderkey INTEGER NOT NULL,
o_custkey INTEGER,
o_orderstatus CHAR(1) CASESPECIFIC,
o_totalprice DECIMAL(13,2) NOT NULL,
o_orderdate DATE FORMAT 'yyyy-mm-dd' NOT NULL,
o_Run_Id INTEGER,
0_Run_Date DATE FORMAT 'YYYY-MM-DD'
)
PRIMARY INDEX ( o_Run_Id ,o_Run_Date )
PARTITION BY RANGE_N(o_run_date BETWEEN
CAST(((EXTRACT(YEAR FROM CURRENT_DATE) - 1900)*10000+0101) AS DATE) AND
CAST(((EXTRACT(YEAR FROM CURRENT_DATE) -1900)*10000+0701) AS DATE)
EACH INTERVAL '1' Day );

Going forward each day i need to drop the oldest partition and add a new partition for the current day.

When i use the following alter statement to drop the oldest partition, i get the following error:

alter table Orders modify primary index
drop range where partition = 1
with delete;

--error: 9249: The altering of RANGE_N definition with CURRENT_DATE/CURRENT_TIMESTAMP is not allowed.

When i use the following statement to automatically Roll Partitions, i get the following error:

ALTER TABLE Orders TO CURRENT WITH DELETE;

--error: 9247: One or more objects reconciled to an earlier or same date or timestamp.

Can someone please help me on how to implement the rolling partition concept using current_date ( drop oldest partition and create a new partition on a daily basis).

Thanks
Javed

2 REPLIES
Teradata Employee

Re: CURRENT_DATE/TIMESTAMP Partitioning in Teradata issue..

You are performing ALTER TO CURRENT on the same day you have created/altered the table(for date based expressions), Hence the error. Perform the operation on the day you really need to ALTER the partitions - In your case the next day.

Re: CURRENT_DATE/TIMESTAMP Partitioning in Teradata issue..

Prabhu, It makes sense.Let me try it out tomorrow and see if it works.

However if thats the case then i have the following question:

Let us suppose the table is created on july 1st with partition range between ( feb 1st and July 1st which will be based on current date logic) and on july 2nd i run my first alter statement,assuming it will drop the oldest partition for feb 1st and create a new partition for 2nd July.
Suppose i dont run the alter statement on 3,4,5th july and run it again on 6th july.
My question here is ,will it still drop the oldest partition ( 2nd feb) and create a new partition for 6th july only and not end up in creating partitions for 3,4,5th july as well.

Did you come across such scenario.

Thanks
Javed