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 )

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.
PURGE_ROWS: If PURGE_ROWS is set to a non-zero value, then a single pass is made through the table which will purge committed deleted rows from the table. This space is then available for future inserted rows, but remains allocated to the table. As this option scans every page of the table, its performance is linearly related to the size of the table.
DEFRAGMENT_ROWS: If DEFRAGMENT_ROWS is set to a non-zero value, then a single defragment pass is made which will move existing rows from the end of the table towards the front of the table. The goal of defragmentation is to empty a set of pages at the end of the table which can then be returned to the operating system by the TRUNCATE_END option. It is recommended to only run DEFRAGMENT_ROWS if also specifying the TRUNCATE_END option. The DEFRAGMENT_ROWS option scans the whole table and needs to update index entries for every base table row move, so the execution time is linearly related to the size of the table.
TRUNCATE_END: If TRUNCATE_END is set to a non-zero value, then all contiguous pages at the end of the table will be returned to the operating system. Running the PURGE_ROWS and/or DEFRAGMENT_ROWS options may increase the number of pages affected. This option by itself performs no scans of the table.

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 all available compress options:

call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('US', 'CUSTOMER', 1, 1, 1);

To return the empty free space at the end of the same table, the following call will run much quicker than running all options but will likely return much less space:

call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('US', 'CUSTOMER', 0, 0, 1);

Java example

To compress a table called CUSTOMER in a schema called US, using all available compress options:

CallableStatement cs = conn.prepareCall
("CALL SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE(?, ?, ?, ?, ?)");
cs.setString(1, "US");
cs.setString(2, "CUSTOMER");
cs.setShort(3, (short) 1);
cs.setShort(4, (short) 1);
cs.setShort(5, (short) 1);
cs.execute();

To return the empty free space at the end of the same table, the following call will run much quicker than running all options but will likely return much less space:

CallableStatement cs = conn.prepareCall
("CALL SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE(?, ?, ?, ?, ?)");
cs.setString(1, "US");
cs.setString(2, "CUSTOMER");
cs.setShort(3, (short) 0);
cs.setShort(4, (short) 0);
cs.setShort(5, (short) 1);
cs.execute();

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