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, 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.
SyntaxSYSCS_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.
SQL exampleTo 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 exampleTo 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();
We recommend that you issue the SYSCS_UTIL.SYSCS_INPLACE_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 .