A Derby table or index (sometimes called a conglomerate) can contain unused space after large amounts of data have been deleted or updated.
This happens because, by default, Derby does not return unused space to the operating system. After a page has been allocated to a table or index, Derby does not automatically return the page to the operating system until the table or index is dropped, even if the space is no longer needed. However, Derby does provide a way to reclaim unused space in tables and associated indexes.
If you determine that a table and its indexes have a significant amount of unused space, use either the SYSCS_UTIL.SYSCS_COMPRESS_TABLE or SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE procedure to reclaim that space. SYSCS_UTIL.SYSCS_COMPRESS_TABLE is guaranteed to recover the maximum amount of free space, at the cost of temporarily creating new tables and indexes before the statement is committed. SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE attempts to reclaim space within the same table, but cannot guarantee it will recover all available space. The difference between the two procedures is that unlike SYSCS_UTIL.SYSCS_COMPRESS_TABLE, the SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE procedure uses no temporary files and moves rows around within the same conglomerate.
You can use the SYSCS_DIAG.SPACE_TABLE diagnostic table to estimate the amount of unused space in a table or index by examining, in particular, the values of the NUMFREEPAGES and ESTIMSPACESAVING columns. For example:
SELECT * FROM TABLE(SYSCS_DIAG.SPACE_TABLE('APP', 'FLIGHTAVAILABILITY')) AS T
For more information about SYSCS_DIAG.SPACE_TABLE see "SYSCS_DIAG diagnostic tables and functions" in the Derby Reference Manual.
As an example, after you have determined that the FlightAvailability table and its related indexes have too much unused space, you could reclaim that space with the following command:
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'FLIGHTAVAILABILITY', 0);
The third parameter in the SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE procedure determines whether the operation will run in sequential or non-sequential mode. If you specify 0 for the third argument in the procedure, the operation will run in non-sequential mode. In sequential mode, Derby compresses the table and indexes sequentially, one at a time. Sequential compression uses less memory and disk space but is slower. To force the operation to run in sequential mode, substitute a non-zero SMALLINT value for the third argument. The following example shows how to force the procedure to run in sequential mode:
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'FLIGHTAVAILABILITY', 1);