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:
- Connect to the sysadmin database (as user informix).
- Run the admin() or task() function with the table shrink or fragment shrink arguments.
- For a table, the syntax is:
- compress repack shrink
- compress shrink
- repack shrink
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.
- EXECUTE FUNCTION task("fragment repack shrink," "14680071");
Related Information
[{"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":""}}]
Was this topic helpful?
Document Information
Modified date:
03 June 2021
UID
swg21648987