Explanation: When “ALTER TABLE TO CURRENT” command issued then the newly resolved date and/or timestamp value evaluated earlier than the last resolved date and/or timestamp value for a table or join index whose PPI is defined with CURRENT_DATE or CURRENT_TIMESTAMP. . This may be possible either due to a clock drift, a clock reset, or time zone differences for data.
Remedy: Resubmit the ALTER TABLE TO CURRENT statement later. If problem persists check if there are issues with the system clock.
Example: In this example I have created a table and trying to issue “ALTER TABLE TO CURRENT” command to show it failed because the command issued where last ResolvedCurrent_date was same when the command was issued. Afterword I have change the session date and issued the “ALTER TABLE TO CURRENT” command and that was completed successfully.
select current_date; Current Date -------------------- 2018-07-25
CREATE TABLE item ( l_orderkey INTEGER NOT NULL, l_shipdate DATE FORMAT 'yyyy-mm-dd' ) PRIMARY INDEX (l_orderkey) PARTITION BY RANGE_N(l_shipdate BETWEEN ADD_MONTHS(((DATE + 1 )- (EXTRACT(DAY FROM (DATE )))),(-12)) AND ADD_MONTHS(((DATE)- (EXTRACT(DAY FROM (DATE )))),(+1)) EACH INTERVAL '1' MONTH);
ALTER TABLE item TO CURRENT; *** Failure 9247 One or more objects reconciled to an earlier or same date or timestamp.
select ResolvedCurrent_date,ConstraintText from DBC.PartitioningConstraintsV p where p.databasename||'.'||p.tablename='DB.ITEM';
Let’s change the session date to 2018-07-26
DIAGNOSTIC "setcurrentdate=1180726" ON FOR SESSION; Where the date used in the DIAGNOSTIC statement = required date - 19000000.e.g. July 26th 2018 = 20180726 - 19000000 = 1180726.
select current_date; Current Date ------------ 2018-07-26
ALTER TABLE item TO CURRENT; *** Table has been modified. *** Total elapsed time was 1 second.
select ResolvedCurrent_date,ConstraintText from DBC.PartitioningConstraintsV p where p.databasename||'.'||p.tablename='DB.ITEM';
|