I am in need of some general information about releasing extents from a table that I will be trimming and releasing those extents back to the database. The information I have at hand is not clear about this process.

Thanx, Jim Swallow GBAPS



sending to informix-list

On Mon, 26 Jan 2004 10:30:40 -0500, JAMES SWALLOW wrote:

To release unused space from a table back to the common pool of free extents,
you have to reorganize the table. There are several methods:

- Unload the data, drop the table(s), recreate the table(s) with appropriate
extent and next sizes, reload the table(s), recreate indexes and constraints.
This is and the dup-table copy (next) are the only viable options if the
initial extent size is so large that space will not be freed.

- Create a new table with appropriate extent and next sizes, copy the data
from the original table to the new one, drop the original table, rename the
new one, recreate indexes and constraints.

- Cluster an existing index, or create a new CLUSTERed index, after adjusting
the NEXT SIZE for the table. PDQ & PSORT_ are important to the performance of
this option. You can mark the index UNCLUSTERed after the reorg is complete
if you like.

- ALTER FRAGMENT FOR TABLE tablename INIT IN <single dbspace or fragmentation
expression>; after adjusting the NEXT SIZE. This last works for all tables
whether fragmented or not EVEN if you will be reorging the table into the same
dbspace it already lives in. This option tends to be fastest and is the only
one that will not change the table's tabid.

As noted above, if the table has an initial EXTENT SIZE that is much larger
than the current contents then that space will remain unused using the
CLUSTER and ALTER FRAGMENT...INIT methods. You can query sysmaster:sysptnhdr
to see the number of used pages or run oncheck -pT to for the same to
estimate new EXTENT SIZE and NEXT SIZE values. Also my dbschema replacement
utility, myschema, will automatically determine these minimal sizes for you
when run with -a & -m and will generate ALTER FRAGMENT...INIT IN statements
and ALTER TABLE...NEXT SIZE statements for you if you add the -r option.

Myschema is included in the package utils2_ak available for download from the
IIUG Software Repository. Also my package utils4_ak contains awk scripts to
post-process a dbschema or myschema output file to create various scripts
including reorg scripts.

Art S. Kagel

> I am in need of some general information about releasing extents from a
> table that I will be trimming and releasing those extents back to the
> database. The information I have at hand is not clear about this process.
>
> Thanx, Jim Swallow GBAPS
>
>
>
> sending to informix-list