<< SYSCS_UTIL.SYSCS_EMPTY_STATEMENT_CACHE system procedure | SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE system procedure >>

SYSCS_UTIL.SYSCS_COMPRESS_TABLE system procedure

<< SYSCS_UTIL.SYSCS_EMPTY_STATEMENT_CACHE system procedure | SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE system procedure >>

Derby Reference Manual

158

If authentication and SQL authorization are both enabled, only the

database owner

has

execute privileges on this procedure by default. See "Enabling user authentication" and
"Setting the SQL standard authorization mode" in the Derby Developer's Guide for more
information. The database owner can grant access to other users.

JDBC example

CallableStatement cs = conn.prepareCall
("CALL SYSCS_UTIL.SYSCS_CHECKPOINT_DATABASE()");
cs.execute();
cs.close();

SQL Example

CALL SYSCS_UTIL.SYSCS_CHECKPOINT_DATABASE();

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)