The effect of data compression on performance

In many cases, compressing the data in a table space significantly reduces the amount of disk space that is needed to store data. Compressing data can also help improve buffer pool performance. For example, you can store more data in a buffer pool, and DB2® can scan large amounts of data more easily.

With compressed data, performance improvements depend on the SQL workload and the amount of compression. You might see some of the following benefits:

• Higher buffer pool hit ratios. The hit ratio measures how often a page is accessed without requiring an I/O operation.
• Fewer operations in which DB2 accesses a data page.

The compression ratio that you achieve depends on the characteristics of your data. Compression can work well for large table spaces. With small table spaces, the process of compressing data can negate the space savings that compression provides.

Consider these factors when deciding whether to compress data:

• DB2 compresses data one row at a time. If DB2 determines that compressing the row yields no savings, the row is not compressed. The closer that the average row length is to the actual page size, the less efficient compression can be.
• Compressing data costs processing time. Although decompressing data costs less than compressing data, the overall cost depends on the patterns in your data.

If the compression ratio is less than 10%, compression is not beneficial and, therefore, is not recommended. You can use the DSN1COMP utility to determine the probable effectiveness of compressing your data.

You use the COMPRESS clause of the CREATE TABLESPACE and ALTER TABLESPACE statements to compress data in a table space, data in a partition of a partitioned table space, or data in indexes. You cannot compress data in LOB table spaces.