All Forums Data Modeling
danimaltex 8 posts Joined 10/14
04 Dec 2014
Dropping a partition...

Hi All -
 
How would I drop a partition if i need to calculate against the current date?
So far I have 

ALTER TABLE XBITbls.FctBlgAcctSvcPrc_PARTITION

    MODIFY

      DROP RANGE WHERE XBITbls.FctBlgAcctSvcPrc_PARTITION.PartitionDt = CURRENT_DATE - 2

 

It errors out...

 

CREATE TABLE_1

...................

..... IsCurrFl BYTEINT,

      SnapDttm TIMESTAMP(0),

      PartitionDt DATE FORMAT 'yyyy-mm-dd')

PRIMARY INDEX ( DimAcctSk ,SvcCdDimBlgPrdSk ,PckgCdDimBlgPrdSk ,

MSOSvcCdDimBlgPrdSk ,SvcDimBlgOffrDiscCdSk ,SpclDimBlgOffrDiscCdSk ,

CustDimBlgOffrDiscCdSk ,BlgPrdSvcCdSeq )

PARTITION BY RANGE_N(PartitionDt  BETWEEN DATE '2014-12-02' AND DATE '2030-12-31' EACH INTERVAL '1' DAY ,

 NO RANGE, UNKNOWN);

 

Thanks,

Dan

 

Thanks, Dan
dnoeth 4628 posts Joined 11/04
04 Dec 2014

Hi Dan,
if you always want to drop the first partition (the one two days ago) you could simply use

WHERE PARTITION = 1;

every day. But never run it two times on a singe day :-)
 
Otherwise I would simply

DELETE FROM XBITbls.FctBlgAcctSvcPrc_PARTITION
WHERE PartitionDt = CURRENT_DATE - 2;

without dropping the partition. 
Empty partitions don't have any overhead.

Dieter

danimaltex 8 posts Joined 10/14
04 Dec 2014

Thanks, Dieter!!

Thanks,
Dan

danimaltex 8 posts Joined 10/14
08 Dec 2014

Follow up question, Dieter...
If I wanted to go the route of dropping the partition, instead of doing the delete, could i compare the PARTITION level against a date? Similar to 
WHERE PARTITION = 1 AND PartitionDt = CURRENT_DATE -1;
 
Would this work?
 
Dan

Thanks,
Dan

dnoeth 4628 posts Joined 11/04
08 Dec 2014

Hi Dan,
no it wouldn't. Simply try it.

Dieter

You must sign in to leave a comment.