All Forums Database
anishgoyal007 7 posts Joined 02/14
16 Dec 2015
Dynamic partition not working

Hi,
I am trying to insert data into table. Both source and target have same DDL structure. Source table has Dynamic partitions. Source DDL:
CREATE MULTISET TABLE db1.tb1 ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      CUSTID CHAR(3),
      SEGMENTG CHAR(9),
      VPERIOD DATE FORMAT 'YYYY-MM-DD' NOT NULL,
      LOADED_TS TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP(0))
PRIMARY INDEX PI_CUSTICPR ( CUSTID )
PARTITION BY RANGE_N(VPERIOD  BETWEEN ADD_MONTHS(((DATE + 1 )- (EXTRACT(DAY FROM (DATE )))),(-36 )) AND DATE - (EXTRACT(DAY FROM (DATE ))) EACH INTERVAL '1' MONTH );
target DDL is exactly same. However i get partition voilation error when I do simple INSERT INTO db1.target SEL * from db1.tb1;
Can anyone help me if there is something wrong with dynamic partition.

Anish Goyal

kirthi 65 posts Joined 02/12
17 Dec 2015

Were both the tables created on the same date?

anishgoyal007 7 posts Joined 02/14
21 Dec 2015

No, both tables are not on same date.

Anish Goyal

VandeBergB 182 posts Joined 09/06
21 Dec 2015

add a no range and unknown partition to partitioning phrase and retry your insert.  
 

Some drink from the fountain of knowledge, others just gargle.

Fred 1096 posts Joined 08/04
21 Dec 2015

Sending a bunch of rows to NO RANGE partition would eliminate the error, but is unlikely to be the desired result.
Note that the definition of partitions based on CURRENT_DATE (what you are calling "dynamic partitioning") is established at CREATE time and is in fact static except at the points where you explicitly change via ALTER TABLE TO CURRENT.

You must sign in to leave a comment.