Common Questions on Data Compression and Storage Optimization

Answer

• Do you need additional license to use the Storage Optimization feature?
• How do you enable Data Compression?
• How can you tell if a table will benefit from being compressed?
• What is the minimum number of rows required for compression?
• How do you compress a table?
• How can you tell if a table is compressed?
• What does the repack command do?
• What does the shrink command do?
• How do you monitor compression commands in progress?
• How do you monitor compression dictionaries for open tables with compression?
• Is a compression dictionary table a regular table?
• What is difference between normal uncompress, repack and uncompress_offline, repack_offline commands?
• What happens when the compression dictionary table is missing or corrupt?
• What database objects you cannot use with compression?
• How do you move compress data?
• What you need to consider for use Data Compression in HDR, MACH11 or ER environment?

Q. Do you need additional license to use the Storage Optimization feature?

A. Enabling compression requires additional license entitlements. Please contact your IBM representative to assess your needs and ensure that you have the appropriate proof of entitlements to use the Storage Optimization feature.
Back to top

Q. How do you enable Data Compression?

A. You need to use following Admin API command to enable Data Compression:

EXECUTE FUNCTION task("enable compression");

Back to top


Q. How can you tell if a table will benefit from being compressed?

A. Following Admin API command will estimate the benefits of compressing a table:
EXECUTE FUNCTION task("table estimate_compression",
       "<table name>", "<database name>", "<owner name>");

Sample output:
est   curr  change partnum    table
----- ----- ------ ---------- ------------------------------
89.9%  0.0%  +89.9 0x0010016b compdb:informix.comptable

Succeeded: table estimate_compression  compdb:informix.comptable
Back to top


Q. What is the minimum number of rows required for compression?

A. A table must have 2000 records to consider it for compression. If a table has less than 2000 records, the 'table estimate_compression' admin API commands generates error. Following is a sample output of compression estimate for table stores7:state with 52 records:
 
EXECUTE FUNCTION task("table estimate_compression",
        "stores7", "state", "informix");

 est   curr  change partnum    table
 ----- ----- ------ ---------- -------------------------------
  Partition 1049006 does not contain enough rows to build a
   compression dictionary.
  The partition must contain at least 2000 rows to perform
   this operation.
  FAILED: table estimate_compression  stores7:informix.state
Back to top


Q. How do you compress a table?

A. The following Admin API command will compress a table:
EXECUTE FUNCTION task("table compress",
   "<table name>", "<database name>", "<owner name>");
Back to top


Q. How can you tell if a table is compressed?

A. The 'oncheck -pT' command will show compression details of a table. Following is an excerpt of 'oncheck -pT' output:

Compressed Data Summary

   Number of compressed rows and percentage of compressed rows:
                           2046     100.00%
   Page 0 has maximum space 2047
   Minimum size of slot is 204
Back to top


Q. What does the repack command do?

A. The repack command reorganizes rows to be as contiguous as possible within the table specified. You can use repack on user tables only. The repack command does not free up unused extent space at the end of the table specified. After both repack and shrink operation, a table's total extent size(s) could be as small as number of total used data pages.

The following Admin API command will compress and repack a table:
EXECUTE FUNCTION task("table compress repack",
   "<table name>", "<database name>", "<owner name>");

The 'repack' command can be run in any of following combinations:

• compress repack
• compress repack shrink
• repack
• repack shrink

Back to top


Q. What does the shrink command do?

A. The shrink command frees up unused extent space at the end of a table specified. You can use shrink on user tables only. After both repack and shrink, a table's total extent size(s) could be as small as number of total used data pages.

The following Admin API command will compress and shrink a table:
EXECUTE FUNCTION task("table compress shrink",
   "<table name>", "<database name>", "<owner name>");

The 'shrink' command can be run in any of following combinations:

• compress shrink
• compress repack shrink
• shrink
• repack shrink

Back to top


Q. How do you monitor compression commands in progress?

A. The 'onstat -g dsk' command displays information regarding currently running compression operations.
Back to top


Q. How do you monitor compression dictionaries for open tables with compression?

A. The 'onstat -g ppd' command displays information on active compression dictionaries that exist for currently open compressed table(s). You can get similar information by querying the 'syscompdicts' view in the sysmaster database.
Back to top


Q. Is a compression dictionary table a regular table?

A. The compression dictionary table is a special table. A separate compression dictionary table exists for every individual compressed table or fragment(s). Each compression dictionary table is a library of frequently occurring patterns in the table or fragment(s) data and the symbol numbers that replace the patterns.

Just like any regular table the compression dictionary table has its own partition page and partition number. However, the table does not belong to a particular database. You can view it by querying sysmaster:syscompdicts_full but cannot modify it using SQL.
Back to top


Q. What is difference between the uncompress and uncompress_offline and the repack and repack_offline commands?

A. The offline commands perform the operation while holding an exclusive lock on the table or fragment. The exclusive lock prevents all other access to data until the respective compression operation is complete.
Back to top


Q. What happens when the compression dictionary table is missing or corrupt?

A. If the compression dictionary table is missing or corrupt, then you cannot access the compressed table. You will get these errors whenever trying to access the table:

-242 Could not open database table table-name
-135 ISAM error: tblspace does not exist

The message log also reports the error as: "rscLoadLatestDict: dictionary retrieval failed"
Back to top


Q. What database objects cannot be compressed?

A. You cannot compress these objects:

• System catalog tables in the sysmaster, sysutils, sysuser, syscdr, and syscdcv1 databases
• Temporary tables
• Virtual-tables
• Tblspace tblspace
• Dictionary tables
• Any kind of data that stored outside the row, or any other form of non-row data. For example, blobspace blob.
• A table or a table fragment that has less than 2000 records.
• Row not get compressed whose compressed image is not smaller than its uncompressed image
• In HDR environment compression cannot use only on HDR secondary, RS secondary, or SD secondary server. HDR primary and secondary servers must have exactly the same data and physical layout.

Back to top


Q. How do you move compress data?

A. You can use the High-Performance Loader (HPL) or any other Dynamic Server utilities, except the onunload and onload utilities, to move compressed data.

If you consider the dbimport and dbexport utilities to move compressed data, you must manually re-enable compression after import the data using "enable compression" Admin API command. Do this because importing does not preserve enable compression option.
Back to top


Q. What you need to consider for use Data Compression in HDR, MACH11 or ER environment?

A. You don't need to take any additional action for using Data Compression on HDR or MACH11 environment. You follow the same steps on primary server, just as a normal IDS server to use Data Compression. If you use HDR or MACH11, data that is compressed in the primary (source) server will be compressed in the secondary (target) server. However, you cannot perform compression operations on an HDR secondary, RS secondary, or SD secondary server, because the HDR target server must have exactly the same data and physical layout as the primary (source) server.

If you are using ER, compressing data on one replication server does not affect the data on any other replication server. For example, you can use Data Compression feature on the source server but not on the target server or visa versa.
Back to top