Add Fragment Partition Resets Serial

Hi, We are running 11.70.FC7W2 on Solaris 11. We have a table fragmented by partition with 7 fragments, 1 for each day of the week. The thought was to keep a rolling 7 days and always drop the last partition. We noticed in Test, that when we drop and add the partition back using the "BEFORE" keyword, it resets the serial value back to 1. Since we have a unique index on the serial field we are getting errors that it is trying to insert a duplicate record. If I remove the unique index, I can see the engine starts inserting records starting from 1. If I don't use the BEFORE keyword, then it doesn't reset the value. I read through the documentation and didn't see any mention of this behavior. The reason I wanted to use the before keyword was to keep the current days partition at the top of the list. This way when it evaluates the fragmentation it will find the correct partition right away. Thoughts? Is this expected? Here is a sample: ----------------------------------------------------------------------------- create table mytable ( rec_key serial not null , cust_code char(8) not null , ins_dtime datetime year to second default current year to second not null ) fragment by expression partition pt_4 (WEEKDAY (ins_dtime ) = 4 ) in dbs_02, partition pt_3 (WEEKDAY (ins_dtime ) = 3 ) in dbs_02, partition pt_2 (WEEKDAY (ins_dtime ) = 2 ) in dbs_02, partition pt_1 (WEEKDAY (ins_dtime ) = 1 ) in dbs_02, partition pt_0 (WEEKDAY (ins_dtime ) = 0 ) in dbs_02, partition pt_6 (WEEKDAY (ins_dtime ) = 6 ) in dbs_02, partition pt_5 (WEEKDAY (ins_dtime ) = 5 ) in dbs_02 extent size 8192 next size 8192 lock mode row; create unique index ui_mytable on mytable (rec_key) in idx_dbs_01; INSERT INTO mytable( rec_key, cust_code, ins_dtime) values ( 0, "123456", CURRENT YEAR TO second); select * from mytable; alter fragment on table mytable detach pt_4 pt_4_mytable; drop table pt_4_mytable; alter fragment on table mytable add partition pt_4 ( WEEKDAY ( ins_dtime ) = 4 ) IN dbs_02 BEFORE pt_3; -------------------------------------------------------------------------------- Thank You, --Dave --047d7bb03db25172e8051a63662e

Hmm, don''t know if that's expected or not, but the workaround would be to immediately: ALTER TABLE mytable modify rec_key SERIAL( <next value> ); Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Jul 8, 2015 at 4:58 PM, Informix DBA <in4mixdba@gmail.com> wrote: > Hi, > > We are running 11.70.FC7W2 on Solaris 11. We have a table fragmented by > partition with 7 fragments, 1 for each day of the week. The thought was to > keep a rolling 7 days and always drop the last partition. We noticed in > Test, that when we drop and add the partition back using the "BEFORE" > keyword, it resets the serial value back to 1. Since we have a unique > index on the serial field we are getting errors that it is trying to insert > a duplicate record. If I remove the unique index, I can see the engine > starts inserting records starting from 1. If I don't use the BEFORE > keyword, then it doesn't reset the value. I read through the documentation > and didn't see any mention of this behavior. The reason I wanted to use > the before keyword was to keep the current days partition at the top of the > list. This way when it evaluates the fragmentation it will find the > correct partition right away. > > Thoughts? Is this expected? > > Here is a sample: > > > ----------------------------------------------------------------------------- > create table mytable > (> > rec_key serial not null , > > cust_code char(8) not null , > > ins_dtime datetime year to second > > default current year to second not null > ) > fragment by expression > > partition pt_4 (WEEKDAY (ins_dtime ) = 4 ) in dbs_02, > > partition pt_3 (WEEKDAY (ins_dtime ) = 3 ) in dbs_02, > > partition pt_2 (WEEKDAY (ins_dtime ) = 2 ) in dbs_02, > > partition pt_1 (WEEKDAY (ins_dtime ) = 1 ) in dbs_02, > > partition pt_0 (WEEKDAY (ins_dtime ) = 0 ) in dbs_02, > > partition pt_6 (WEEKDAY (ins_dtime ) = 6 ) in dbs_02, > > partition pt_5 (WEEKDAY (ins_dtime ) = 5 ) in dbs_02 > extent size 8192 next size 8192 lock mode row; > > create unique index ui_mytable on mytable (rec_key) in idx_dbs_01;> > INSERT INTO mytable( > rec_key, > cust_code, > ins_dtime)> > values ( > 0, > "123456", > CURRENT YEAR TO second); > > select * from mytable;> > alter fragment on table mytable detach pt_4 pt_4_mytable;> > drop table pt_4_mytable;> > alter fragment on table mytable > add partition pt_4 ( WEEKDAY ( ins_dtime ) = 4 ) > IN dbs_02 BEFORE pt_3;> > > > -------------------------------------------------------------------------------- > > Thank You, > > --Dave > > --047d7bb03db25172e8051a63662e > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1134b964448cdc051a6561ad