Designing Tables and Indexes for Performance › Table Space Compression

Table Space Compression

Using the COMPRESS clause of the CREATE TABLESPACE and ALTER TABLESPACE SQL statements allows for the compression of data in a table space or in a partition of a partitioned table space. In many cases, using the COMPRESS clause can significantly reduce the amount of DASD space that is required to store data. But the compression ratio that is achieved depends on the characteristics of the data.

Compression lets you place more rows on a page resulting in the following performance benefits, depending on the SQL workload and the amount of compression:

• Higher buffer pool hit ratios
• Fewer I/Os
• Fewer getpage operations
• Reduced CPU time for image copies

The processing cost when using compression is relatively low, but consider the following:

• The processor cost to decode a row using the COMPRESS clause is less than the cost to encode the same row.
• The data access path that DB2 uses affects the processor cost for data compression. In general, the relative overhead of compression is higher for table space scans and less costly for index access.

Some data does not compress well, so you should query the PAGESAVE column in SYSIBM.SYSTABLEPART to be sure that you are getting a savings (at least 50 percent is average). Data that does not compress well includes binary data, encrypted data, and repeating strings. Additionally, do not compress small tables or rows if you are concerned about concurrency issues because this configuration places more rows on a page.

Note: When you compress, the row is treated as varying length and the length may change when updates occur, resulting in potential row relocation causing high numbers in NEARINDREF and FARINDREF. This outcome indicates that you are now doing more I/O to get to your data because it has been relocated, and you will have to REORG to get it back to its original position.