1) Setup a date column to use for partitioning
The easiest way is to have an update_ts column with the Informix Date format. I use java to update the informix database and since I want to use MM/dd/YYYY as my timestamp, I first code the java to eliminate unnecessary seconds/milliseconds from the current timestamp generated by System.currentTimeMillis()
long ts = System.currentTimeMillis() ;
java.text.DateFormat sdf= new java.text.SimpleDateFormat("MM/dd/yyyy ") ;
String newDateStr = sdf.format(new java.sql.Date(ts));
System.out.println("updated timestamp:"+sdf.format(new java.sql.Date(ts))) ;
2) Fragmenting an existing Informix table with the init clause
alter fragment on table events_tbl init
fragment by expression
partition ptn_qtr1 (update_ts between "01/01/2010" AND "03/31/2010") in dbsp1,
partition ptn_qtr2 (update_ts between "04/01/2010" AND "06/30/2010") in dbsp1,
partition ptn_qtr3 (update_ts between "07/31/2010" AND "09/30/2010") in dbsp1,
partition ptn_qtr4 (update_ts between "10/01/2010" AND "12/31/2010") in dbsp1,
partition ptn_historic(update_ts < "01/01/2010") ;
create index tbl_idx on events_tbl (update_dt) ;
index tbl_idx created
3) A script runs on the last day of every quarter that drops the partition holding the nextQuarterly data
(in the first year this partition will be empty)
Ex: End of Qtr2 and start of Qtr3
alter fragment on table events_tbl
detach partition ptn_qtr3 tbl_ptn_qtr3_tmp
4) Create and attach a new partition to the events_tbl
(The new partition should be a table with the same structure as events_tbl and same index for partitioning)
5a) To re-attach the dropped partition so that it is now in the history partion
The following adds the dropped partition into a new partition
alter fragment on table events_tbl
attach tbl_ptn_qtr3_tmp
as partition ptn_tmp
Note: this adds an extra partition to the table
5b)After re-attaching the old fragment, refragment to bring our fragment down to the original 5 partitions
dates will need to be adjusted to the new quarter/year
drop index tbl_idx ;
alter fragment on table tbl init
fragment by expression
partition ptn_qtr1 (update_ts between "01/01/2010" AND "03/31/2010") in dbsp1,
partition ptn_qtr2 (update_ts between "04/01/2010" AND "06/30/2010") in dbsp1,
partition ptn_qtr3 (update_ts between "07/31/2010" AND "09/30/2010") in dbsp1,
partition ptn_qtr4 (update_ts between "10/01/2010" AND "12/31/2010") in dbsp1,
partition ptn_historic(update_ts < "01/01/2010") ; create index tbl_idx on tbl (update_dt) ;
This effectively reorganizes the data into the 5 partitions and drops the old fragments.
6)Once the historic data is stored, it can be used to feed reports or segragate data for a datacube or datamart
