Join / Log in
You don't have to write your own custom update statistics, just use my dostats utility in my utils2_ak package. It implements the full recommended UPDATE STATISTICS protocols described in the Performance Guide manual with some enhancements suggested by the engine developers. You can download the latest from my web site free: (www.askdbmgt.com/my-utilities).
3) or must i be prepared to fragment the table (same issues with the unload/load/stats-index rebuild but i prefer it to the truncate honestly so i can still access all the datas)
After deleting the rows you no longer need, you could ALTER FRAGMENT ON TABLE INIT IN <dbspace>; which could be the same dbspace it already resides in or another. That, like #2, will make all new rows contiguous but only the space needed for the copied data or specified in the table's EXTENT SIZE and NEXT SIZE attributes will be allocates. So, you might want to set the EXTENT SIZE to include the amount of free space you want to be pre-allocated before issuing the ALTER command.
4) or do you have any other ideas?
create a new table with all required indexes
rename the old table
create a VIEW that does a UNION between the new table and the old one and give it the name of the original table
create an INSTEAD OF trigger on the VIEW to direct all new inserts to the new table
slowly over time copy data you need to retain from the old table to the new one, optionally deleting those rows from the old table afterwards.
once all of the needed data is in the new table run dostats against the new table then you can drop the VIEW, rename the new table to the original name, then drop the old table. Poof! Downtime should be on the order of seconds if you script #3 & #4 at the beginning of the process and #6 at the end. (You may have to recreate any foreign keys that reference the original table to point to the new table. Also some complex FK relationships may make this idea impractical.)
I like the idea (and tried something close before) but i can't change the source code unfortunatly and make the program look at the view instead of the table. Furthermore, there is a Java problem. If you first make a copy the table in the good dbspace and swiftly change/switch the table names while the program is not accessing the table, the IBM JDBC prepared statement don't work anymore. The statement need to be prepared again.I haven't tried with i4gl but i'm pretty sure it is a problem too.Idea #2:
If you are running very recent v12.10 releases or v14.10 you can use loopback replication to sync all of the rows you need to keep into a new table over time with zero downtime. Then once everything is synced, run dostats on the new table, drop replication, rename the old and new tables, and eventually drop the old table. Again, downtime only at the end lasting only a few seconds. (You may have to recreate any foreign keys that reference the original table to point to the new table.)
There are a couple of other similar ways to approach this depending on what version of Informix you are running including using triggers, change data capture, smart triggers, or post commit triggers to maintain the "new" table while or after initially populating it instead of using loopback replication for versions earlier than 12.10/14.10 that don't support it.