<< SYSCS_UTIL.SYSCS_COMPRESS_TABLE system procedure | DEFRAGMENT_ROWS >>

SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE system procedure

<< SYSCS_UTIL.SYSCS_COMPRESS_TABLE system procedure | DEFRAGMENT_ROWS >>

Derby Reference Manual

159

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, Derby 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 Derby 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, Derby 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.
Tip: We recommend that you issue the

SYSCS_UTIL.SYSCS_COMPRESS_TABLE

system procedure in the auto-commit mode.

Note: 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 Derby Server and Administration Guide.

SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE system procedure

Use the

SYSCS_UTIL.SYSCS_INPLACE_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 and there has not been
any subsequent inserts to use the space created by the deletes. By default, Derby 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_INPLACE_COMPRESS_TABLE

allows you to return unused space to the operating system.

This system procedure can be used to force three levels of in-place compression
of a SQL table:

PURGE_ROWS

DEFRAGMENT_ROWS

, and

TRUNCATE_END

. Unlike

SYSCS_UTIL.SYSCS_COMPRESS_TABLE()

, all work is done in place in the existing

table/index.

Syntax

SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE(
IN SCHEMANAME VARCHAR(128),
IN TABLENAME VARCHAR(128),
IN PURGE_ROWS SMALLINT,
IN DEFRAGMENT_ROWS SMALLINT,
IN TRUNCATE_END SMALLINT )