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

 

ResolvedCurrent_date

ConstraintText

2018-07-25

CHECK ((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 )) BETWEEN 1 AND 00013)

 

 

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

ResolvedCurrent_date

ConstraintText

2018-07-26

CHECK ((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 )) BETWEEN 1 AND 00013)

 

 

Comments