Recently, the project quickly ending the need to clean up the database (Derby): clean up unused data, reducing the database size.
Finished cleaning up and found that the physical database file size is not reduced. Derby did not know what kind of mechanism, the physical file weight? Less than the entry-level English level I do not want to see the official documents in English, direct google about and find this link: http://mxh85154.blog.hexun.com/33808923_d.html , provided the original stored procedure to thin Derby.
Described in the article was not very detailed look Derby had their own documents, as seen from the document text:
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.
SYSCS_UTIL.SYSCS_COMPRESS_TABLE (IN SCHEMANAME VARCHAR (128),
IN TABLENAME VARCHAR (128), IN SEQUENTIAL SMALLINT)
An input argument of type VARCHAR (128) that specifies the schema of the table. Passing a null will result in an error.
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.
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.
To compress a table called CUSTOMER in a schema called US, using the SEQUENTIAL option:
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE ('US', 'CUSTOMER', 1)
As can be seen from the document, called this process, need to pass in three parameters: the first is a database of SCHEME, the second for the table name, and the third is a short non-zero integer, and the three parameters can not be empty. According to documents introduced to try, really worked, the file size reduced by the 850M to 40M. But too much trouble, you need a table for each stored procedure execution time. Later, I thought I could use stored procedures, one to clean up all the tables: in the storage process, through the statement "SELECT * FROM SYS.SYSTABLES T WHERE T. TABLETYPE = 'T';" check out all of the users table, and then clean up .
Finally, there is a doubt, stored procedure, the interpretation of the third parameter is not very clear: the different incoming short integers, the results will be any different?