There are two main types of data compression available for tables:
1 Row compression
How Can we COMPRESS the data
When we use COMPRESS YES in CREATE TABLE/ALTER TABLE statements the data compression is achieved.
DB2 scans the table data ,even rows and looks for the repetitive ,duplicate data.
Then internally it builds a dictionary based algorithm with these data and assigns a short numeric keys to those items.
Suppose we have two rows with the below values in one table like below.
Mike 2000 LONDON 70045 UK
JOHN 3000 LONDON 70045 UK
DB2 scan these rows and optimizer decides that the pattern 'LONDON 70045 UK' is repetitive. So DB2 assigns one numeric qualifier to this pattern,say 02.
Thus the above row will be changed to
MIKE 2000 02 JOHN 3000 02
where 02 = LONDON 70045 UK
This Dictionary ,ie the mapping of the repetitive field will be hidden object in the database.It remains cached for quick access.There can be instances where such type of COMPRESSION does not result in space saving.DB2 would not compress in those cases.
NOTE: This data compression happens only when the table dictionary is built which usually happens during the REORG PHASE.
While creating a LOB tablespace, we should not specify the COMPRESS YES OPTION.
There are several types of data compression available for tables:
Example used above shows Adaptive and classic row compression because
they attempt to replace data patterns that spans multiple columns with short symbols.
Value compression is effective when rows with columns with the same value, like CITY, or columns that contain the default value for the data type of the column.
When we need adaptive compression,we use COMPRESS YES ADAPTIVE
when we need classic row compression,use COMPRESS YES STATIC.
When we need value compression,use COMPRESSION clause. If we want to compress data that represents system default column values, also include the COMPRESS SYSTEM DEFAULT clause.