Friday, February 7, 2014

Data Compression in DB2

Disk storage and space management is one the hot discussed topics now a days.Coming to database management, DB2 V9 uses Venom technology to compress a row data which leads to reduced IO, efficiency and provide fast access to data.
There are two main types of data compression available for tables:
1 Row compression
2.Value 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.
An Example:
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:
Row compression
Value compression
Adaptive compression

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.

No comments:

Post a Comment