Table and fragment compress and uncompress operations (SQL administration API)

You can compress and uncompress the data in a table or in table fragments with SQL administration API admin() or task() functions and arguments. Compression operations apply only to the contents of data rows and the images of those data rows that appear in logical log records.

The built-in SQL administration API admin() or task() functions are defined in the sysadmin database of each Informix® instance. By default, only user informix can invoke these functions. If Connect privilege on the sysadmin database is granted to user root or to DBSA group members, they too can invoke the SQL administration API admin() or task() functions when they are connected directly or remotely to the sysadmin database.

The SQL administration API admin() or task() command arguments that you can use for compress and uncompress operations in tables and table fragments are:

table compression parameters

Performs various compression operations to all fragments of a specified table. For more information, see table or fragment arguments: Compress data and optimize storage (SQL administration API).

fragment compression parameters

Performs various compression operations to a single fragment or a specified set of fragments that belong to a specific table. For more information, see table or fragment arguments: Compress data and optimize storage (SQL administration API).

compression purge_dictionary

Deletes all inactive compression dictionaries or all inactive compression dictionaries that were created before a date that you specify. For more information, see purge compression dictionary arguments: Remove compression dictionaries (SQL administration API).

Table and fragment compression operations include creating compression dictionaries, estimating compression ratios, compressing data in tables and table fragments, consolidating free space (repacking), returning free space to a dbspace (shrinking), uncompressing data, and deleting individual table and fragment compression dictionaries.

When you run SQL administration API compression and uncompression commands, you compress and uncompress both row data and simple large objects in dbspaces. You can also specify whether to compress or uncompress only row data or only simple large objects in dbspaces.

An admin() command returns an integer; a task() command returns a string.

For information on the types of data that you can compress, compression ratios, compression estimates, and compression dictionaries, as well as procedures for using compression command parameters, see Compression in the Informix® Administrator's Guide. For information on utilities and the sysmaster table and view that display compression information, see syscompdicts_full.

You can also compress, optimize storage, and estimate compression benefits for B-tree indexes. See index compress repack shrink arguments: Optimize the storage of B-tree indexes (SQL administration API) and index estimate_compression argument: Estimate index compression (SQL administration API).