Data Compression

[Anurag]

Hi,
I am told that Oracle has this "data compression" feature that allows
you to store online data ina compressed format.
This is different from archived data - you compress only that data
which is infrequently accessed but is still available in the online
database.

I am interested in finding out an equivalent feature in DB2 UDB on LUW.
If there exists one, how should I go about implementing this?
If there is an alternative strategy to achieve the same goal, what is
it and how to implement the same.

Finally, are there any benchmarks or white papers(publishe d by Oracle /
IBM / independents) that bring out the storage benefits of this data
compression feature of Oracle?

Rgds.........an urag

[Anton Versteeg via DBMonster.com]

DB2 on the mainframe (z/OS) supports tablespace compression.
DB2 LUW doesn't.

--
Message posted via http://www.dbmonster.com

[Serge Rielau]

Anurag wrote:

Hi,
I am told that Oracle has this "data compression" feature that allows
you to store online data ina compressed format.
This is different from archived data - you compress only that data
which is infrequently accessed but is still available in the online
database.

I am interested in finding out an equivalent feature in DB2 UDB on LUW.
If there exists one, how should I go about implementing this?
If there is an alternative strategy to achieve the same goal, what is
it and how to implement the same.

Finally, are there any benchmarks or white papers(publishe d by Oracle /
IBM / independents) that bring out the storage benefits of this data
compression feature of Oracle?

Rgds.........an urag

AFAIK DB2 for LUW, supports compression for DEFAULT values.
DB2 for zOS supports hardware assisted general data compression.
You will hardly find an IBM witepaper detailing the benefits of Oracle's
data compression ;-)
Either way compression comes at a price. You trade CPU cycles for space.

It's an interesting question how compression on the I/O subsystem itself
ocmpares against compression on the DBMS level.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Buck Nuggets]

> Either way compression comes at a price. You trade CPU cycles for
space.

Here's a scenario begging for compression: you've got a warehouse with
3 years of detailed data totalling 5 TB. This warehouse is only used
to generate summary data and marts - no end-user queries occur. The
older data is kept online - since it allows the warehouse to regenerate
a mart or create a new summary with full historical data quickly &
easily. However, this only happens every 6 months or so. In this case
the occasional cpu penalty is a great trade-off for the continual
savings on storage. Alternatively, I can keep the data on
slower/cheaper storage - but that isn't always conveniently available.
And db2 doesn't move partitions around in a way to make that easy
either.

Anyhow it's not the only such scenario. I vote to add it to db2 :-)

buck

[Serge Rielau]

Buck Nuggets wrote:

Either way compression comes at a price. You trade CPU cycles for
space.

.... Anyhow it's not the only such scenario. I vote to add it to db2 :-)

I did not mean to marginalize the feature.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Mark Townsend]

Anurag wrote:

Finally, are there any benchmarks or white papers(publishe d by Oracle /
IBM / independents) that bring out the storage benefits of this data
compression feature of Oracle?

Rgds.........an urag

http://www.oracle.com/technology/ora...tech_data.html
http://www.oracle.com/technology/pro...rmance_twp.pdf

[Mark A]

"Anurag" <an**********@g mail.com> wrote in message >

Finally, are there any benchmarks or white papers(publishe d by Oracle /
IBM / independents) that bring out the storage benefits of this data
compression feature of Oracle?

Rgds.........an urag

http://www.tpc.org/tpch/results/tpch_perf_results.asp

[Mark Townsend]

Mark A wrote:

"Anurag" <an**********@g mail.com> wrote in message >

Finally, are there any benchmarks or white papers(publishe d by Oracle /
IBM / independents) that bring out the storage benefits of this data
compression feature of Oracle?

Rgds......... anurag

http://www.tpc.org/tpch/results/tpch_perf_results.asp

I don't think any of the Oracle published benchmarks use the compression
feature. An earlier one did (referenced in the TWP I posted), but it was
on the old spec and has since been withdrawn. There is little
justification to use compression when doing a TPC-H

[Mark A]

"Mark Townsend" <ma***********@ comcast.net> wrote in message
news:42******** ****@comcast.ne t...

I don't think any of the Oracle published benchmarks use the compression
feature. An earlier one did (referenced in the TWP I posted), but it was
on the old spec and has since been withdrawn. There is little
justification to use compression when doing a TPC-H

In theory, data compression could help certain aspects of performance on the
TPC-H benchmark. The decision support benchmark has a lot of tablespace
scans required to process the SQL queries, and if the data was compressed,
then fewer physical page I/O's would be required. Also, if less disk space
were needed, the Price/QphH would be lower.

The problem (as noted) is that there is extra CPU processing required to
de-compress the data, so the net benefit may be minimal, or perhaps even
negative (but I don't really know). If you say that Oracle TPC-H benchmarks
would not benefit from compression, then I will take your word for it.

DB2 for z/OS data compression was first implemented when most people stored
data on mainframe disk systems (3390, etc), which where an order of
magnitude more expensive than the cost of disk space on UNIX systems.