Use the SYSCS_UTIL.SYSCS_COMPRESS_TABLE system
procedure to reclaim unused, allocated space in a table and its indexes. Typically,
unused allocated space exists when a large amount of data is deleted from
a table, or indexes are updated. By default, does
not return unused space to the operating system. For example, once a page
has been allocated to a table or index, it is not automatically returned to
the operating system until the table or index is destroyed. SYSCS_UTIL.SYSCS_COMPRESS_TABLE allows
you to return unused space to the operating system.
The SYSCS_UTIL.SYSCS_COMPRESS_TABLE system
procedure updates statistics on all indexes as part of the index rebuilding
process.
Syntax SYSCS_UTIL.SYSCS_COMPRESS_TABLE (IN SCHEMANAME VARCHAR(128),
IN TABLENAME VARCHAR(128), IN SEQUENTIAL SMALLINT)
- SCHEMANAME
- An input argument of type VARCHAR(128) that specifies the schema of the
table. Passing a null will result in an error.
- TABLENAME
- An input argument of type VARCHAR(128) that specifies the table name of
the table. The string must exactly match the case of the table name, and the
argument of "Fred" will be passed to SQL as the delimited identifier 'Fred'.
Passing a null will result in an error.
- SEQUENTIAL
- A non-zero input argument of type SMALLINT will force the operation to
run in sequential mode, while an argument of 0 will force the operation not
to run in sequential mode. Passing a null will result in an error.
SQL example To compress a table called CUSTOMER
in a schema called US, using the SEQUENTIAL option:
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('US', 'CUSTOMER', 1)
Java example To compress a table called CUSTOMER
in a schema called US, using the SEQUENTIAL option:
CallableStatement cs = conn.prepareCall
("CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE(?, ?, ?)");
cs.setString(1, "US");
cs.setString(2, "CUSTOMER");
cs.setShort(3, (short) 1);
cs.execute();
If the SEQUENTIAL parameter is not specified, rebuilds
all indexes concurrently with the base table. If you do not specify the SEQUENTIAL
argument, this procedure can be memory-intensive and use a lot of temporary
disk space (an amount equal to approximately two times the used space plus
the unused, allocated space). This is because compresses
the table by copying active rows to newly allocated space (as opposed to shuffling
and truncating the existing space). The extra space used is returned to the
operating system on COMMIT.
When SEQUENTIAL is specified, compresses
the base table and then compresses each index sequentially. Using SEQUENTIAL
uses less memory and disk space, but is more time-intensive. Use the SEQUENTIAL
argument to reduce memory and disk space usage.
SYSCS_UTIL.SYSCS_COMPRESS_TABLE cannot release
any permanent disk space back to the operating system until a COMMIT is issued.
This means that the space occupied by both the base table and its indexes
cannot be released. Only the disk space that is temporarily claimed by an
external sort can be returned to the operating system prior to a COMMIT. We recommend that you issue the SYSCS_UTIL.SYSCS_COMPRESS_TABLE system
procedure in the auto-commit mode.
This procedure acquires
an exclusive table lock on the table being compressed. All statement plans
dependent on the table or its indexes are invalidated. For information on
identifying unused space, see the .