By using the row compression operator, you can manage table storage within the SQW environment and avoid the inconvenience of performing data compression manually in the database. The row compression feature uses a dictionary-based algorithm for compressing data rows. The compression logic reduces table size by building and utilizing a compression dictionary, which is used as an alphabet to compress the rows of data in database tables. For more information, see Compression dictionaries.
The DB2 9.1 database and its later versions support the row compression feature. If you are using DB2 9.1, you must add a reorg operator after the row compression operator. The reorg operation creates the compression dictionary. If you are working with DB2 9.5, you do not need a reorg operator. When a table is enabled for row compression, the enhanced row compression feature in DB2 9.5 automatically creates a compression dictionary for the table after the table size crosses a threshold value. You must add a reorg operator to compress rows that existed before you enabled the table for row compression.
- Enable compression
- Compresses the table.
- Disable compression
- Decompresses the table.
- Reset dictionary
- The DB2 database creates a new compression dictionary or modifies
an existing dictionary based on the status of the dictionary and the
table that is to be compressed. The following table summarizes the
behavior of this action:The reset dictionary action calls the REORG TABLE statement with the RESETDICTIONARY option.
Table 1. Behavior of resetting the compression dictionary based on the status of the compression dictionary and table that is to be compressed. Compression status Compression dictionary status Action taken by Reset Dictionary option Enabled Exists Rebuilds new dictionary and compresses rows and indexes Enabled Does not exist Builds new dictionary and compresses rows and indexes Disabled Exists Removes the dictionary and decompresses all rows and indexes Disabled Does not exist Decompresses all rows and indexes; this action has no effect on the dictionary - View statistics
- Row compression statistics include the number of rows, average compressed row size, average row compression ratio, and percentage of pages saved. The result of the statistics is shown on the Data Output view. You can perform this action only on tables that are enabled for compression.
Enabling row compression for a table is a quick process because the DB2 database marks the table as enabled for row compression, and creates a compression dictionary only after the table size crosses a threshold value. The amount of space that is saved by using the data row compression feature varies and depends on the data in the table. On an average, row compression can lower storage costs by about 40% to 75%.
You can estimate the compression ratio for a table by running the INSPECT command from the DB2 command prompt. This command collects a sample of data from the table that is to be compressed, and builds a compression dictionary from the sample. This dictionary is then used to test compression against the sample rows, and the compression savings are estimated. You need to format the output of the INSPECT command by using the db2inspf DB2 utility to see the results, which are stored in a file in the DB2 installation directory.
- Percentage of pages saved from compression
- Percentage of bytes saved from compression
- Percentage of rows ineligible for compression due to small row size
- Compression dictionary size
- Expansion dictionary size