DB2 Administrative SQL Cookbook: BLU Compression Ratios

Posted by

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.

Lead Db2 Database Engineer and Service Delivery Manager , XTIVIA
Ember is always curious and thrives on change. Working in IT provides a lot of that change, but after 17 years developing a top-level expertise on Db2 for mid-range servers and more than 7 years blogging about it, Ember is hungry for new challenges and looks to expand her skill set to the Data Engineering role for Data Science. With in-depth SQL and RDBMS knowledge, Ember shares both posts about her core skill set and her journey into Data Science. Ember lives in Denver and work from home for XTIVIA, leading a team of Db2 DBAs.

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.