Example: Optimizing data storage on demand
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.
Before you begin
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.
About this task
To compress both row data and simple large objects in dbspaces:
Procedure
- 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 HCL 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.
- To compress only row data, specify:
- 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.
What to do next
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");