I was wondering if many people had implemented Row Compression on their production databases and what experiences have they had?

Does it yield anywhere near the toted 45-75% space saving?
Does it improve query performance?
Are there any problems with it?
Does it use much additional CPU?
Is it straightforward to use and set-up?

Cheers,

1. Does it yield anywhere near the toted 45-75% space saving?

Depends on the type of data you have. Compressing a database backup can sometimes yield that much compression, so it is possible.

2. Does it improve query performance?

If you have a decision support system (data warehouse) where there are frequent table scans, or you need to access a large number of rows, then it will likely improve query performance by reducing physical and logical (bufferpool) I/O (the number of rows per page is increased with compression). If you have an OLTP system where virtually all access is via an index and only a small number of data rows are evaluated for each SQL statement, then it probably will not help.

3. Are there any problems with it?

In many pure OLTP applications, it could actually slow things down. Also, it costs extra over and above the standard V9 Enterprise Server license (priced per CPU).

4. Does it use much additional CPU?

It does use additional CPU, but it can save CPU overall if you are accessing a lot of pages to satisfy the typical queries you run on those tables.

5. Is it straightforward to use and set-up?

Yes. Just remember that it on an existing table, it does not take effect on existing rows until after a reorg (sort of like adding a column to a table, there is no physical space actually reserved for the new column in the existing rows until after a reorg).

Cheers Marcus...some interesting points there. I'm particularly interested in the license information. I'm having difficulty finding additional info on this from the IBM site...do you have any links?

DB2 Enterprise 9 priced features [This means it costs extra, but I don't know how much. I would guess about $10,000 per CPU, but large IBM customers get a discount].

DB2 Storage Optimization Feature for Linux, UNIX, and Windows:
This feature includes both Data Row Compression and Backup Compression to help maximize the use of existing storage:

Data Row Compression uses a static dictionary-based compression algorithm to compress data by row. Compressing data at the row level is advantageous because it allows repeating patterns that span multiple column values within a row to be replaced with shorter symbol strings, allowing for improved performance. In I/O-bound systems it will not only reduce storage requirements but may improve overall performance.

Backup Compression efficiently compresses data in the DB2 server buffer before data is written to the disk, providing fast backups and significantly smaller backup images.

Program charges: This feature is available as an option for DB2 Enterprise 9 only [formally known as ESE] and can only be acquired if the underlying DB2 data server is licensed with the processor charge metric.

http://www-306.ibm.com/common/ssi/fc...um=ENUS206-128

I am not sure if this means that backup compression is no longer included for free.

OK Thanks for that!

OK So I've installed 9.1 Fixpack 1 on a test system and I've started running the INSPECT ROWCOMPESTIMATE command on the largest tables in the DB.

By the by, INSPECT ROWCOMPESTIMATE is pretty clunky to use ...does anyone know a way to make it better/easier? Can it be used for multiple tables (I can't see an obvious way in the doco)

Anyhoo, it's telling me that I'm going to save between 60% and 83% depending on the table. So I'm wondering if anyone knows how accurate this estimate is? (ie is ROWCOMPESTIMATE good at guessing?)

Cheers