Let’s talk about this feature that started on version 9.
If you want to use this feature on your database you need to activate at table level, at each table you want, using the COMPRESS YES option either at creation time or at anytime after that. Let’s take a look on how to do this:
CREATE TABLE table_name (
ALTER TABLE table_name COMPRESS YES
For both new and old tables the compression will only start after the compression dictionary is created. This dictionary is used as a lookup table for retrieving and inserting new data, this dictionary will be created after an offline reorg. This reorg is needed to DB2 can check the repeating strings or substrings on the entire table and then create the new dictionary.
Even if you do massive loads, new reorgs to update the dictionary will not likely be needed because of the substrings that it keeps on the dictionary. The command for creating a compression dictionary on a table is:
However every time from now on that you decide to run a reorg you will need to add KEEPDICTIONAY on the command. For example:
After this reorg finish to run, your table will have the benefits of the compression, for example: Less I/O, sometimes less CPU, and less storage needed.
But you may be asking by now, are all tables granted the good news when using this feature? The short answer no.
If you want to know how much space will be saved even before doing all these steps, you can use the INSPECT tool.
The INSPECT tool will check the entire table and analyze it, and give to you estimates of how much will be saved, however you need to run the output of INSPECT through db2inspf, so you can get a nice readable output. The commands for getting this information are:
INSPECT ROWCOMPESTIMATE TABLE NAME table_name
SCHEMA schema_name RESULTS KEEP inspect_out.txt
db2inspf inspect_out.txt db2inspf_out.txt
NOTE: The inspect creates the output file only at ../sqllib/db2dump
Example of the final output:
VERSION : SQL09050
Action: ROWCOMPESTIMATE TABLE
Schema name: EIWDB2
Table name: HIST_EE_WW_05
Tablespace ID: 9 Object ID: 4
Result file name: res.txt
Table phase start (ID Signed: 4, Unsigned: 4; Tablespace ID: 9) :
Data phase start.
Object: 4 Tablespace: 9
Row compression estimate results:
Percentage of pages saved from compression: 67
Percentage of bytes saved from compression: 67
Compression dictionary size: 46208 bytes.
Expansion dictionary size: 32768 bytes.
Data phase end.
Table phase end.
Processing has completed. 2008-06-18-126.96.36.1994557