Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2004
    Posts
    3

    Unanswered: Reorganization of fragmented table?

    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.

  2. #2
    Join Date
    Aug 2004
    Posts
    99
    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

  3. #3
    Join Date
    Aug 2004
    Posts
    3
    Thanks man,

  4. #4
    Join Date
    Nov 2004
    Posts
    60

    Don't drop the table

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •