Tag Archives: Row Compression

The Squeeze

My friendly AIX admin recently asked me to move some of my test databases from a P570 to a new Power 7 (P750) box.  I used db2look and db2move to replicate these on the new system, but I wanted to take it a bit further.  Since I’ve been called a “DASD pig,” I saw this as a good opportunity to enable DB2 row compression on some of the larger tables and see how much space I could save.

Look Before You Squeeze

Before moving, I took some compression estimates on the source system. Since it was DB2 9.1, I had to use inspect:

db2 inspect rowcompestimate table name TABLENAME schema SCHEMANAME results keep TABLENAME.insp
db2inspf sqllib/db2dump/TABLENAME.insp TABLENAME.out

 

This gave “percentage saved” estimates in the 61 to 81 percent range for the tables I selected: very promising, considering the table sizes.

The target system was DB2 9.7, so I could use admin_get_tab_compress_info to get estimates there:

db2 “select * from table(sysproc.admin_get_tab_compress_info(‘SCHEMA’, ‘TABLE’, ‘estimate’)) as t”

For one 8-million-row table, the estimated savings were 61%: the same as the inspect estimate for this table on the DB2 9.1 box.

YMMV

I was curious how accurate these estimates were, so I measured it.  Since I had some similar databases, I created the first database without row compression and added it after loading data.

I compared sizes before and after the “alter table … compress yes” and reorg. The tablespace snapshots revealed a 51% reduction in 4K pages: from 909,985 to 442,817.  And the (SMS) disk space savings were in that same range: from 2.48 GB to 1.19 GB for that table’s data.  The index space savings weren’t significant (from 0.98 GB to 0.92 GB), but I didn’t expect much there.

I ran some quick tests using the system against the new (compressed) tables and found no significant performance impacts.  The initial reorgs and imports to enable compression and load data were painfully slow, but those are “off hours” activities anyway.

I’m Convinced

This was enough to convince me to enable compression from the very beginning for some of the larger tables in the remaining databases.  That is, I edited the db2look-generated DDL to add “compress yes” to the create table statements before running them.  I considered switching some of the SMS tablespaces to automatic storage, but decided to stick with only one change at a time for this round.

So far, I’m pleased with the results.  I’ll know more the next time we do a performance test, and I’ll be less of a “DASD pig” in the meantime.