Estimate Disk Space Savings with Data Compression

Answer

Data compression provides several advantages to a business such as saved disk storage, avoiding bottlenecks in high throughput OLTP systems, reduced I/O, increased performance, and lower archiving time.

When reduction of disk space usage is critical to the business, it helps to determine the potential space savings available by using data compression. The data compression feature allows you to estimate the amount of space that can be saved before compressing data in a table. This document will explain how to estimate the disk space saving.

The maximum possible compression ratio is 90 percent. However, the amount of compression to be achieved depends on the nature of the data being compressed. Since this utility is an estimate, there can be differences between estimate and actual disk space after compression.

You can use SQL Admin API commands for estimate disk space savings for a table or table fragment(s). Either “task” or “admin” API functions can be used to execute SQL Admin API command.

As user informix or a user with connect permission to the sysadmin database, perform the following steps to estimate storage savings:

1. Connect to sysadmin database

DATABASE sysadmin;

Table compression syntax:
EXECUTE FUNCTION task("table estimate_compression",
  "TableName", "DatabaseName", "OwnerName");

TableName
The name of the table that needs to compress

DatabaseName
The name of the database that contains the table

OwnerName
The name of the database owner

Table partition(s) syntax:
EXECUTE FUNCTION task("fragment estimate_compression",
  "PartnumList");

PartnumList
The list of partition numbers that belong to the same table. Use space as a delimiter.

• A windows based tool is available, called the 'compression estimator' which uses a csdk connection and active statistics. For more information check this developerWorks article and download the tool listed in the resources section.

• Here is a query to list the tables potentially available for compression:
SELECT * FROM sysmaster:systabnames T, sysmaster:sysptnhdr P
WHERE T.partnum = P.partnum AND P.nrows > 100000 AND
bitand(P.flags , 134217728 ) == 0 #( <==already compressed )
AND dbsname NOT IN ( "sysmaster", "system") AND T.tabname NOT IN (
SELECT N.tabname FROM systables N where N.tabid < 100 )