Estimating storage savings offered by adaptive or classic row compression

You can view an estimate of the storage savings adaptive or classic row compression can provide for a table by using the ADMIN_GET_TAB_COMPRESS_INFO table function.

Before you begin

The estimated savings that adaptive or classic row compression offers depend on the statistics generated by running the RUNSTATS command. To get the most accurate estimate of the savings that can be achieved, run the RUNSTATS command before you perform the following steps.

Procedure

To estimate the storage savings adaptive or classic row compression can offer using the ADMIN_GET_TAB_COMPRESS_INFO table function:

  1. Formulate a SELECT statement that uses the ADMIN_GET_TAB_COMPRESS_INFO table function.
    For example, for a table named SAMPLE1.T1, enter:
    SELECT * FROM TABLE(SYSPROC.ADMIN_GET_TAB_COMPRESS_INFO('SAMPLE1', 'T1'))
  2. Execute the SELECT statement.
    Executing the statement shown in Step 1 might yield a report like the following:
    TABSCHEMA TABNAME    DBPARTITIONNUM DATAPARTITIONID OBJECT_TYPE ROWCOMPMODE ...
--------- ---------- -------------- --------------- ----------- ----------- ...
SAMPLE1   T1         0              0               DATA        A           ...

  1 record(s) selected.
    PCTPAGESSAVED_CURRENT AVGROWSIZE_CURRENT PCTPAGESSAVED_STATIC ...
--------------------- ------------------ -------------------- ...
                   96                 24                   81 ...
    AVGROWSIZE_STATIC PCTPAGESSAVED_ADAPTIVE AVGROWSIZE_ADAPTIVE
----------------- ---------------------- -------------------
              148                    93                  44