Hi Guys,

Is it possible to reorganized a table that has been fragmented? How?
Our system is now reaching its chunks limit (ver 7.31) and most of the dbspaces were already reorg using sapdba and the only table left for us to reorganize are the fragmented tables.

Thanks.

Hi,

You can do it by:

- unload the table to a flat file
- drop and recreate the table using a large enough first extent size to hold all its current data (taking into account growth in time) and a proper next extent size (which will probably will never be used if you calculate the correct size for the first extent). Remember to create the table on a dbspace large enough to hold it.
- reload the table from the flat file and recreate indexes, triggers, permissions, etc.

Hope this helps

Thanks man,

I have been through this exercise this week with several tables and found the following to be much easier than unloading to files. The benefit here is you don't drop the original table until you are sure the new table has all the data but it does mean you need a fair bit of spare dbspace to have both tables existent at the same time for a period of time:

1. Create the new table with the correct extent size

2. Create a schema of the database as it stands
DBSCHEMA -d dbname -ss dbname.sql

3. Read dbname.sql and make a note of any view or trigger that refers to the table in question. You will need these to recreate the views or triggers as these are invalidated when you drop the table.

4. Copy the data from the old table:
INSERT INTO newtable SELECT * FROM table;

5. Drop any indexes on the old table:
drop index blah;

6. Check both tables have the same number of rows
select count(*) from table;
select count(*) from newtable;

7. Run oncheck -pe and look at new extents ensuring the new table is not fragmented

8. Rename old table
RENAME table TO oldtable;

9. Rename new table
RENAME newtable TO table;

10. Create index(es) on the new table

11. Grant permissions to the new tble e.g.
GRANT SELECT ON "informix".table TO "public" AS "informix";

12. Finally drop the old table if you are abosulutely sure we don't need it
DROP TABLE oldtable;

13. Recreate any views or triggers