In this example, you learn how to run SQL administration
API commands to determine how much space you can save by compressing
a table, how to compress the table, and how to optimize storage on
demand. You also learn how to uncompress the table and remove the
compression dictionaries.
Assume that you have a table named rock in a database
named music that is owned by user mario. The rock table
is not fragmented. You can run the same operations on a table fragment
as you can on a whole table, but the syntax is slightly different.
Prerequisites:
- There must be at least 2,000 rows in each fragment of the table,
not just a total of 2,000 rows in the whole table.
- You must be able to connect to the sysadmin database
(by default only user informix), and you must be a DBSA.
- Logical and physical logs are large enough to handle normal processing
and compression operations. Compression, repacking, and uncompressing,
operations can use large amounts of logs.
To compress both row data and simple large objects in dbspaces:
- You run the following command to check how much space you
might save by compressing the table:
EXECUTE FUNCTION task("table estimate_compression", "rock", "music", "mario");
You
review the resulting report, which indicates you can save 75 percent
of the space that is used by the rock table. You decide to
compress the table.
- Before you compress data, you want to create a compression
dictionary, which contains information that IBM®
Informix® uses
to compress data in the rock table. You run the following command
EXECUTE FUNCTION task("table create_dictionary", "rock", "music", "mario");
Tip: If you do not create the compression dictionary as a separate
step, Informix creates
the dictionary automatically when you compress data.
- You decide that you want to compress data in the rock table and simple large objects in dbspaces,
consolidate the data, and then return the free space to the dbspace.
You run the following command:
EXECUTE FUNCTION task("table compress repack shrink", "rock", "music", "mario");
You can perform
the same operations faster by running them in parallel. You run the
following command:
EXECUTE FUNCTION task("table compress repack shrink parallel", "rock",
"music", "mario");
You can adjust the command by specifying
what you want to compress or shrink. For example:
- To compress only row data, specify:
EXECUTE FUNCTION task("table compress rows parallel","rock","music","mario");
- To compress only row data and then repack and shrink the data,
specify:
EXECUTE FUNCTION task("table compress repack shrink rows parallel",
"rock","music","mario");
- To compress only simple large objects in the dbspace, specify:
EXECUTE FUNCTION task("table compress blobs parallel","rock","music","mario");
After the existing rows and
simple large objects are compressed, Informix consolidates
the free space that is left at the end of the table, and then removes
the free space from the table, returning that space to the dbspace.
If the simple large objects or rows are not
smaller when compressed, the database server does not compress them.
- Now suppose that you want to uncompress the data. You run
the following command:
EXECUTE FUNCTION task("table uncompress parallel", "rock", "music", "mario");
- You want to remove the compression dictionary.
- Verify that Enterprise Replication does
not require the dictionary.
If you do require the dictionaries
for Enterprise Replication,
do not remove compression dictionaries for uncompressed or dropped
tables and fragments.
- Archive the dbspace that contains the table or fragment
with a compression dictionary.
- Run this command:
EXECUTE FUNCTION task("table purge_dictionary", "rock", "music", "mario");
To run compression
and other storage optimization commands on table fragments, include
the fragment argument instead of the table argument
and the fragment partition number instead of the table name.
EXECUTE FUNCTION task("fragment command_arguments", "partnum_list");