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:

To compress and uncompress row data:

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 OneDB™ 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, HCL OneDB 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, HCL OneDB 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", "rock", "music", "mario");

EXECUTE FUNCTION task("table uncompress parallel", "rock", "music", "mario");

You want to remove the compression dictionary.
1. 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.
2. Archive the dbspace that contains the table or fragment with a compression dictionary.
3. Run this command:
```
EXECUTE FUNCTION task("table purge_dictionary", "rock", "music", "mario");
```

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");