fragmentation and partition
I have fragmented table ds_archive. Originally, the "remainder" was stored
at dbdata02 dbspace( not defined as "partition").
After I altered the fragment( used "partition" and moved the "remainder"
from dbdata00 to dbdw01), somehow a piece of data is still left at
dbdata02.
schema:
create table "informix".ds_archive
(
dataset_name varchar(255,44) not null ,
archive_site char(10) not null ,
path_name varchar(250,50),
archive_dt datetime year to second
) with crcols
fragment by expression
partition pt1 (archive_dt < datetime(2003-06-01 00:00:00)
year to second ) in dbdw00 ,
partition pt2 ((archive_dt >= datetime(2003-06-01 00:00:00)
year to second ) AND (archive_dt < datetime(2003-08-10
00:00:00) year to second ) ) in dbdw00 ,
partition pt3 ((archive_dt >= datetime(2003-08-10 00:00:00)
year to second ) AND (archive_dt < datetime(2004-01-01
00:00:00) year to second ) ) in dbdw00 ,
partition pt4 ((archive_dt >= datetime(2004-01-01 00:00:00)
year to second ) AND (archive_dt < datetime(2005-08-10
00:00:00) year to second ) ) in dbdw00 ,
partition pt5 ((archive_dt >= datetime(2005-08-10 00:00:00)
year to second ) AND (archive_dt < datetime(2007-01-01
00:00:00) year to second ) ) in dbdw01 ,
partition pt6 ((archive_dt >= datetime(2007-01-01 00:00:00)
year to second ) AND (archive_dt < datetime(2008-01-01
00:00:00) year to second ) ) in dbdw01 ,
partition rmd remainder in dbdw01
extent size 4096 next size 4096 lock mode row;
revoke all on "informix".ds_archive from "public";
create index "informix".ds_arch_idx on "informix".ds_archive (archive_dt)
using btree ;
create unique index "informix".ds_arch_uidx on "informix".ds_archive
(dataset_name,archive_site,archive_dt) using btree ;
alter table "informix".ds_archive add constraint primary key
(dataset_name,archive_site,archive_dt) constraint "informix"
.ds_arch_pk ;
the data distributed:
dbdata02 9 9
ds_archive 1024
dbdw00 10 10
ds_archive 551936
dbdw00 10 10
ds_arch_uidx 250777
dbdw00 10 10
ds_arch_idx 50830
dbdw00 10 10
ds_arch 4096
dbdw01 11 11
ds_archive 77824
dbdw01 11 11
ds_arch_uidx 35919
dbdw01 11 11
ds_arch_idx 7132
dbdw01 11 11
ds_arch 3072
I am using IDS 10 UC4 and AIX 5..
Thanks,
Quman