Creating an Informix Database Server

I used the following steps to recreate Informix Database server and the databases within it in linux/ubuntu. Since I had a dbexport, I could use dbimport with -D option to reimport the tables into the new database

Login as informix or root
1) Modifiy onconfig file
rootdbs, path to rootdbs, DBSERVERNAME, log files and paths to log files

2) Update environment variable to new server
export INFORMIXSERVER=

3) Update sqlhosts file for the new server
If on linux, possibly the /etc/services file may need to change depending on sqlhosts file settings

4) create a path to the new rootdbs
$touch /path_to_rootdbs/rootdbs
$chmod 660 /path_to_rootdbs/rootdbs

5) Create any dbspace you may need if you wish to store databases outside of rootdbs
$onspaces -c -d dbspace -p path_to_dbname -o 0 -s sizeKB

6) if you don't already have a logon,
a) go thru the usual user, group creation and add the user to the appropriate groups with a profile so they can get to dbaccess
b) i don't create the database since I plan to use dbimport from another server

7)Navigate to where database.exp folder is
$dbimport -c newDB -d dbspace
If the appropriate .sql file is in the folder this should run and reimport all the tables

8)You should be able to use dbaccess to access the new db
$dbaccess newDB

Partitioning Informix table data into quarterly and historic info

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