Informix

• I can unload quite some records from the tables and delete them but i forgot..
  1) will the space in the extents/new extents will be available again for the new records i try to insert? (so i won't have to add a new extents)
  • Yes, deleted row space is immediately available for use by new rows with a small caviat. The engine uses the bitmaps to determine what pages have space for new rows. So in a table with narrow rows pages with only a small number of deleted rows in them, may not trigger the flag, so it is possible that sparse deletes will waste a small amount of space. Not a major thing. But i'm gessing the « new extents » will allocated ?

• 2) or must i be prepared to truncate the table (which takes aaaaages to: turn off the db, unload the datas that i wont reload, unload datas that i will reload, load those latter datas (maybe though external table to quick-up this step), rebuild the stats (as auto update statitics doesnt work with complex index so i had to write custom update stats :'(), put the db online in solomode, check if it is working and put it in multiple mode)
  • TRUNCATE will optionally free the unused space for reuse by other tables, but if the REUSE STORAGE option is passed then the space freed up by deleted rows after the reload will still be there for the table itself to reuse with all of the free space at the end of the table rather than scattered as it would be under #1 above, so that is an advantage for efficiency as new rows will be contiguous!

• • 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?

Idea #1:

1. create a new table with all required indexes

2. rename the old table

3. create a VIEW that does a UNION between the new table and the old one and give it the name of the original table

4. create an INSTEAD OF trigger on the VIEW to direct all new inserts to the new table

5. 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.

6. 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:

1. 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.

• I can unload quite some records from the tables and delete them but i forgot..
  1) will the space in the extents/new extents will be available again for the new records i try to insert? (so i won't have to add a new extents)
  • Yes, deleted row space is immediately available for use by new rows with a small caviat. The engine uses the bitmaps to determine what pages have space for new rows. So in a table with narrow rows pages with only a small number of deleted rows in them, may not trigger the flag, so it is possible that sparse deletes will waste a small amount of space. Not a major thing. But i'm gessing the « new extents » will allocated ?

• 2) or must i be prepared to truncate the table (which takes aaaaages to: turn off the db, unload the datas that i wont reload, unload datas that i will reload, load those latter datas (maybe though external table to quick-up this step), rebuild the stats (as auto update statitics doesnt work with complex index so i had to write custom update stats :'(), put the db online in solomode, check if it is working and put it in multiple mode)
  • TRUNCATE will optionally free the unused space for reuse by other tables, but if the REUSE STORAGE option is passed then the space freed up by deleted rows after the reload will still be there for the table itself to reuse with all of the free space at the end of the table rather than scattered as it would be under #1 above, so that is an advantage for efficiency as new rows will be contiguous!

• • 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?

Idea #1:

1. create a new table with all required indexes

2. rename the old table

3. create a VIEW that does a UNION between the new table and the old one and give it the name of the original table

4. create an INSTEAD OF trigger on the VIEW to direct all new inserts to the new table

5. 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.

6. 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:

1. 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.