To compress or not is the question

Robert Catterall

To compress or not is the question
Very late for chiming in, but I just now saw this thread and wanted to add a
couple of points:

1) It was stated at one point in this thread that "indexes are not
compressed." Of course, starting with DB2 9 for z/OS (in New Function Mode)
indexes can be compressed.

2) Prior to the advent of universal tablespaces (also delivered with DB2 9,
in New Function Mode), tablespace compression could sometimes cause a
performance problem for inserts into partitioned tablespaces clustered by a
key that is not continuously ascending. The reason: compression would make
otherwise fixed-length rows variable-length rows (because rows with
different column values will often compress differently, in terms of length
reduction). In a traditional partitioned tablespace, a space map page has
only two bits per data page to indicate the space available in a page for
possible insertion of a new row. Two bits do not provide much precision, so
DB2 could infer from that information only that a to-be-inserted row MIGHT
fit in a given page -- it actually had to examine the data page to verify
that the row would or wouldn't fit there. In some cases, DB2 might have to
examine quite a few data pages before finding one that would accommodate the
new row. That meant more GETPAGEs (occasionally a lot more), and that drove
up CPU time for insert jobs. People would deal with this either by
specifying relatively large values for PCTFREE (and maybe FREEPAGE, as well)
and REORGing relatively frequently to reestablish that free space (so a page
examined would be more likely to have space for the new row), or by
specifying 0 for PCTFREE and FREEPAGE (so that new rows would go quickly to
the end of partitions) and REORGing periodically to reestablish clustering
sequence.

Universal tablespaces (which are always partitioned -- either
range-partitioned or partition-by-growth), like segmented tablespaces, have
space map pages in which there are four bits per data page providing
information about available space for new rows. This much greater precision
means that DB2 will not have to examine a data page to determine whether or
not a to-be-inserted row will fit -- it KNOWS that a row will fit in a given
data page based on the information in the associated space map page.

Bottom line: as has been pointed out, compression is typically a good thing
for most tablespaces. If a tablespace is partitioned and has a
non-continuously-ascending clustering key, compression will be an even
better option if that tablespace is a universal tablespace.

Robert


On Wed, Oct 27, 2010 at 2:02 PM, Sameer Rana <[login to unmask email]> wrote:

> Does anyone know of any whitepaper/redbook reference/Information Center
> Bookmanager reference on Version 9 Db2 zOS on the performance tradeoffs in
> enabling compression on tablespaces?
> What gotcha's/risks should I be aware of before enabling compression on
> Production tables for humungous highly active legacy objects?
>
> My knowledge on this subject has become somewhat outdated and before I dive
> into the subject I would appreciate if folks can share their
> opinion/knowledge/experience on this.
>
> Regards,
> Sameer
>
> _____________________________________________________________________
> * IDUG North America * Anaheim, California * May 2-6 2011 *
> http://IDUG.ORG/NA *
> * If you are going to attend only one conference this year, this is it!
> *
> _____________________________________________________________________
> http://www.IDUG.org/mentor
> Mentoring should be a rewarding experience for everyone...
> IDUG is offering up to 80% off when you both come to the conference!
> _____________________________________________________________________
>
> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
> the home of IDUG's Listserv
>



--
Robert Catterall
DB2 Specialist
IBM US - East
[login to unmask email]

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv