IBM Support

Reclaim dbspace using repack or shrink

Question & Answer


Question

Is it possible to reclaim dbspace from a table or fragment?

Cause

A dba wants to reduce the extent size, or change a fragment strategy for a table or fragment, or a need arises to compress a table or fragment.

Answer

You can return free space to a dbspace (shrink the space) when you compress, repack, or compress and repack tables or fragments; or you can return free space separately without compressing or repacking. Returning free space reduces the total size of the fragment or table.

When you initiate a shrink operation, Informix shortens extents as follows:

  • It shortens all extents except the first extent to as small a size as possible.
  • If the table is entirely in the first extent (for example, because the table is an empty table), Informix does not shrink the first extent to a size that was smaller than the extent size that was specified when the table was created with the CREATE TABLE statement.

You can use the MODIFY EXTENT SIZE clause of the ALTER TABLE statement to reduce the current extent size. After you do this, you can rerun the shrink operation to shrink the first extent to the new extent size.

To return free space to the dbspace:

  1. Connect to the sysadmin database (as user informix).
  2. Run the admin() or task() function with the table shrink or fragment shrink arguments.
    For a table, the syntax is:
    EXECUTE FUNCTION admin(“table shrink”, “table_name”, “database_name”, “owner_name”);

      The table name is mandatory. The database and owner names are optional. If you do not specify a database or owner name, Informix uses the current database and owner name.

    For a fragment, the syntax is: EXECUTE FUNCTION task(“fragment shrink”, “partnum_list”);

      The partnum_list is a space-separated list of partition numbers that belong to the same table.

    Optionally, expand the arguments to include compress and repack using one of the following operations:
    • compress repack shrink
    • compress shrink
    • repack shrink
    This example shows how to repack and shrink a fragment with a partition number of 14680071:
      EXECUTE FUNCTION task("fragment repack shrink," "14680071");

[{"Product":{"code":"SSGU8G","label":"Informix Servers"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Not Applicable","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF022","label":"OS X"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"11.5;11.7;12.1","Edition":"Enterprise;Growth;Hypervisor;Ultimate;Workgroup","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
03 June 2021

UID

swg21648987