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.
