db2 ALTER TABLE table_name COMPRESS YES

This allows the table to be compressed. Now DB2 needs to scan the data in the table to find the common components that it can compress out of the table and put in a dictionary. To do this you use the REORG TABLE command. The first time you compress a table (or if you want to rebuild the dictionary) you must run

db2 REORG TABLE table_name RESETDICTIONARY

This will scan the table, create the dictionary, and then perform the actual table reorg compressing the data as it goes. From this point onward, any insert into this table or subsequent load of data will honor the compression dictionary and compress any new data in the table. If in the future you want to run a normal table reorg and not rebuild the dictionary you can run

db2 REORG TABLE table_name KEEPDICTIONARY

Note that each table object has its own dictionary. That means that a partitioned table will have a separate dictionary for each partition. This is good because it allows DB2 to adapt to changes in the data as you roll in a new partition.

In Viper II, the LOAD utility can now create a compression dictionary

• LOAD REPLACE RESETDICTIONARY

         Will unconditionally create a new dictionary or replace an existing one
         A dictionary is built even if just 1 row of data is loaded (analogous to REORG TABLE
         RESETDICTIONARY)

•  LOAD REPLACE KEEPDICTIONARY

         Will keep an existing dictionary but if one does not exist, will create one if sufficient data was loaded

•  LOAD INSERT

         A dictionary can be automatically created if sufficient data is loaded or exists already in the table

Example:

Schema: db2inst1
Table: employee

db2 load from employee.ixf of ixf replace resetdictionary into db2inst1.employee

If you want to just see how much space you can save without actually compressing the table you can do that to. The DB2 INSPECT command now has an option to report on the number of pages saved if you were to implement compression on a given table. The syntax is

DB2 INSPECT ROWCOMPESTIMATE TABLE NAME table_name RESULTS KEEP file_name

You then run the command

db2inspf file_name output_file_name

to convert the binary output file of inspect into a readable text file called output_file_name. This file contains the estimated percentage of data pages saved from compression. In a test I ran, the estimate was 75% compression and when I actually compressed the table the actual compression was 75.5%.

If you are starting with a new system, what you may want to do is the following

• create the table with compression yes

• load a representative sample of the data into the table

• reorg the table with resetdictionary to create a new dictionary

• load the rest of the data into the table (this load will respect the dictionary and compress on the fly as it loads

Source: Ittoolbox Blogs