How to ckeck information about tables compressed on DB2 V9
Perfom the command below on sysibmadm.ADMINTABCOMPRESSINFO table to check information about tables in your target schema for DB2 V9.5:
db2 "select substr(tabschema,1,13) as schema, substr(tabname,1,13) as table, \
compress_attr, DICT_BUILD_TIMESTAMP,substr(dict_builder,1,15) as dict_build, \
COMPRESS_DICT_SIZE as compress_size, EXPAND_DICT_SIZE as expand_size, ROWS_SAMPLED, \
PAGES_SAVED_PERCENT pages_percent, BYTES_SAVED_PERCENT as bytes_percent, AVG_COMPRESS_REC_LENGTH \
from sysibmadm.ADMINTABCOMPRESSINFO where tabschema='<schema_name>'"
Output example:
db2 "select substr(tabschema,1,13) as schema, substr(tabname,1,13) as table, compress_attr, DICT_BUILD_TIMESTAMP,substr(dict_builder,1,15) as dict_build, COMPRESS_DICT_SIZE as compress_size, EXPAND_DICT_SIZE as expand_size, ROWS_SAMPLED, PAGES_SAVED_PERCENT pages_percent, BYTES_SAVED_PERCENT as bytes_percent, AVG_COMPRESS_REC_LENGTH from sysibmadm.ADMINTABCOMPRESSINFO where tabschema='DB2INST1'"
SCHEMA TABLE COMPRESS_ATTR DICT_BUILD_TIMESTAMP DICT_BUILD COMPRESS_SIZE EXPAND_SIZE ROWS_SAMPLED PAGES_PERCENT BYTES_PERCENT AVG_COMPRESS_REC_LENGTH
------------- ------------- ------------- -------------------------- --------------- -------------------- -------------------- ------------ ------------- ------------- -----------------------
DB2INST1 CL_SCHED N - NOT BUILT 0 0 0 0 0 0
DB2INST1 PROJECT N - NOT BUILT 0 0 0 0 0 0
DB2INST1 DEPARTMENT N - NOT BUILT 0 0 0 0 0 0
DB2INST1 EMPLOYEE Y 2009-01-06-09.39.43.000000 REORG 13312 10224 42 46 46 47
DB2INST1 EMP_PHOTO N - NOT BUILT 0 0 0 0 0 0
DB2INST1 EMP_RESUME N - NOT BUILT 0 0 0 0 0 0
DB2INST1 PROJACT N - NOT BUILT 0 0 0 0 0 0
DB2INST1 EMPPROJACT N - NOT BUILT 0 0 0 0 0 0
DB2INST1 ACT N - NOT BUILT 0 0 0 0 0 0
DB2INST1 IN_TRAY N - NOT BUILT 0 0 0 0 0 0
DB2INST1 ORG N - NOT BUILT 0 0 0 0 0 0
DB2INST1 STAFF N - NOT BUILT 0 0 0 0 0 0
DB2INST1 SALES N - NOT BUILT 0 0 0 0 0 0
DB2INST1 STAFFG N - NOT BUILT 0 0 0 0 0 0
DB2INST1 ADEFUSR N - NOT BUILT 0 0 0 0 0 0
DB2INST1 EMPMDC Y 2009-01-06-09.44.01.000000 REORG 27904 32768 10000 34 34 16
Check the compression ratio in a specific table for DB2 V9:
db2 "select substr(tabname,1,20) as tabname,card,AVGROWCOMPRESSIONRATIO,AVGCOMPRESSEDROWSIZE, \
AVGROWSIZE,PCTROWSCOMPRESSED,PCTPAGESSAVED from syscat.tables where tabschema='<schema_name>'"
Output exmple:
TABNAME CARD AVGROWCOMPRESSIONRATIO AVGCOMPRESSEDROWSIZE AVGROWSIZE PCTROWSCOMPRESSED
-------------------- -------------------- ------------------------ -------------------- ---------- ------------------------
APLICATION 3800282 +3.49317E+000 89 89 +1.00000E+002
ACCOUNT 47871929 +2.18380E+000 33 33 +1.00000E+002
CUSTOMER 439131 +0.00000E+000 0 60 +0.00000E+000
DEPARTMENT 235565 +0.00000E+000 0 382 +0.00000E+000