IBM Support

Estimate Disk Space Savings with Data Compression

Question & Answer


How to get an estimate of disk storage savings when compressing a table or table fragment(s)?


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;

2.You can get estimate for a table or table partition(s).
    Table compression syntax:
    EXECUTE FUNCTION task("table estimate_compression",
      "TableName", "DatabaseName", "OwnerName");

        The name of the table that needs to compress
        The name of the database that contains the table
        The name of the database owner
    Table partition(s) syntax:
    EXECUTE FUNCTION task("fragment estimate_compression",
        The list of partition numbers that belong to the same table. Use space as a delimiter.

The following is an example of data compression estimate on a table named ″cust″ in ″stores7″ database owned by ″admin″:

 est   curr  change partnum    table
 ----- ----- ------ ---------- -----------------------------
 88.8% 0.0%  +88.8  0x0040005c stores7:admin.cust

 Succeeded: table estimate_compression  stores7:admin.cust

In the above example table 'stores7:admin.cust' has one extent and is not compressed. If you compress the table, estimated (est) disk saving will be 88.8 percent of its current size. Current (curr) compression ratio is 0.0 percent, indicating the table is not using compression. The "change" column represents a potential disk savings that may be achieved over the existing compression ratio. The "partum" and "table" columns represent the partition number and name of the table used for this compression estimate.

Other estimation methods
  • 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 )

[{"Product":{"code":"SSGU8G","label":"Informix Servers"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Not Applicable","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF022","label":"OS X"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"11.5;11.7;12.1","Edition":"Enterprise;Ultimate","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
16 June 2018