9247 One or more objects reconciled to an earlier or same date or timestamp

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';