We are stuck with a long running ALTER table command. From previous forum articles/blogs and posts, I have gathered that we cannot cancel an ALTER Table once submitted. Since the TJ size is changing with this statement, I presume that this does not fall into a classical "Alter Table" definition and can be cancelled. I just don't want to go-ahead and abort it without knowing whether it will go into recovery or will not work at all.
This is what I am running for more than 30 hours.
ALTER TABLE <DB_NAME>.<TB_NAME>TO CURRENT WITH DELETE;
We migrated on 15.10 and moved to intelliflex recently - obviously everything is FB protected. The table is around ~5-6TB (non-BLC'd) and 500GB with BLC. Unfortunately it has NUSI as well (which I beleive is again FB protected).
Any help here will be much appreciated.
You're right, this is not a normal ALTER TABLE.
I don't know if it can be aborted (but then you might wait another 30 hours to finish the rollback).
Seems like you defined partitioning in a (dumb) way that leads to reassigning each row to a new partition, didn't you test it before?
Can you show the PARTITION BY definition?
SyntaxEditor Code Snippet
DATE_COLUMN BETWEEN ADD_MONTHS(((DATE + 1 )- (EXTRACT(DAY FROM (DATE ))( TITLE 'Day'))),(-36 )) , DATE - (EXTRACT(DAY FROM (DATE ))( TITLE 'Day')) EACH INTERVAL '1' MONTH
This covers the previous 36 months not including the current month, the ALTER should not change the internal partition number.
Assuming there's an ALTER TABLE TO CURRENT WITH DELETE this will delete the oldest month, but as far as I know it's not a fastpath delete, thus using the Transient Journal.
I don't have expertise in IF, maybe it's actually slower due to Fallback & BLC, but I know that I would never apply WITH DELETE, I would submit a SQL DELETE before.
In your case this would be a DELETE FROM myTable WHERE PARTITION = 1
Btw, why do you use those rolling partitions?
I would define a fixed range far into the future, DATE_COLUMN BETWEEN DATE '2015-01-01' AND DATE '2040-12-31' EACH INTERVAL '1' MONTH, thus you will never have to modify the partitioning again.
Then you simply delete the oldest month using a SQL DELETE, an empty partition has zero overhead.
No need for ALTER TABLE, exclusive locks, Transient Journal.