DB2 Administrative SQL Cookbook: BLU Compression Ratios

Purpose

This statement calculates the compression ratio for BLU tables. The compression ration can be used to help identify tables where compression is not optimal and you may need to look into why. Compression is critical to optimal performance on BLU.

Understanding Compression Ratios.

Compression ratios across platforms and outside of databases are generally represented as:
Compression Ratio = Compressed Size / Uncompressed Size
When talking about the results we generally refer to the compression ratio as NUM X compression. For example from the results section below, for the last table, I would say we’re seeing 10X compression on that table.

Source

While I’ve modified this statement, it comes from these two sources, both influenced by David Kalmuk:
http://www.ibm.com/developerworks/data/library/techarticle/dm-1407monitor-bluaccel/index.html
http://www.dbisoftware.com/blog/db2nightshow.php?id=619

Statement

SELECT     substr(tabschema,1,18) as tabschema
    ,substr(tabname,1,33) as tabname
    , card
    , npages
    , decimal(1/(1-float(pctpagessaved)/100),5,2) as compr_ratio
FROM SYSCAT.TABLES
WHERE tableorg='C' 
    and tabschema not like 'SYS%'
    and card > 0
order by compr_ratio
with ur;

Sample Output

TABSCHEMA          TABNAME                           CARD                 NPAGES               COMPR_RATIO
------------------ --------------------------------- -------------------- -------------------- -----------
EBT_ODS            EBTUSG_OUTOF_CNTY                               397483                  315        7.69
EBT_ODS            EBT_OUT_CNTY                                    514329                  455        7.69
SSIRS              AGEN_PERF                                       501321                  262        8.33
EBT_ODS            EBT_ACT_TYPE1                                 26598452                49867        9.09
CIS_ODS            INDV                                           2605558                 5787        9.09
SSIRS              ES_CSLD_VER2                                  18659499                40773        9.09
SSIRS              STAFF                                           317367                  172       10.00
CMIPS_ODS          RELBCO01                                        914192                 1934       10.00

Notes

I like to return the card and npages so I can understand if a table is so small that its geometry may affect the compression ratio. The results are actual results from a production BLU database that I support, but represent the best few tables as far as compression goes.

Ember Crooks
Ember Crooks

Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, spent a year working with high-volume MySQL, and is now learning Snowflake. Ember shares both posts about her core skill sets and her journey learning Snowflake.

Ember lives in Denver and work from home

Articles: 544

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.