SYSCS_UTIL.SYSCS_COMPRESS_TABLE system procedure

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, 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_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.

Execute privileges

If authentication and SQL authorization are both enabled, all users have execute privileges on this procedure. However, in order for the procedure to run successfully on a given table, the user must be the owner of either the database or the schema in which the table resides. See "Enabling user authentication" and "Setting the SQL standard authorization mode" in the Derby Developer's Guide for more information.

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, 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.