This happens because, by default,
does not return unused
space to the operating system. After a page has been allocated to a table or
index, does not
automatically return the page to the operating system until the table or index
is dropped, even if the space is no longer needed. However,
does provide a way to
reclaim unused space in tables and associated indexes.
If you determine that a table and its indexes have a significant amount
of unused space, use either the SYSCS_UTIL.SYSCS_COMPRESS_TABLE
or SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE procedure to reclaim
that space. SYSCS_UTIL.SYSCS_COMPRESS_TABLE is guaranteed to
recover the maximum amount of free space, at the cost of temporarily creating
new tables and indexes before the statement is committed.
SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE attempts to reclaim
space within the same table, but cannot guarantee it will recover all available
space. The difference between the two procedures is that unlike
SYSCS_UTIL.SYSCS_COMPRESS_TABLE, the
SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE procedure uses no
temporary files and moves rows around within the same conglomerate.
You can use the SYSCS_DIAG.SPACE_TABLE diagnostic table to
estimate the amount of unused space in a table or index by examining, in
particular, the values of the NUMFREEPAGES and ESTIMSPACESAVING columns. For
example:
SELECT * FROM TABLE(SYSCS_DIAG.SPACE_TABLE('APP', 'FLIGHTAVAILABILITY')) AS T
For more information about
SYSCS_DIAG.SPACE_TABLE see "SYSCS_DIAG diagnostic tables and
functions" in the .
As an example, after you have determined that the
FlightAvailability table and its related indexes have too much
unused space, you could reclaim that space with the following command:
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'FLIGHTAVAILABILITY', 0);
The third parameter in the
SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE procedure determines
whether the operation will run in sequential or non-sequential mode. If you
specify 0 for the third argument in the procedure, the
operation will run in non-sequential mode. In sequential mode,
compresses the table
and indexes sequentially, one at a time. Sequential compression uses less memory
and disk space but is slower. To force the operation to run in sequential mode,
substitute a non-zero SMALLINT value for the third argument. The following
example shows how to force the procedure to run in sequential mode:
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'FLIGHTAVAILABILITY', 1);
For more information about this command, see the
.