[DB2-L] Index compession

Martin Ketterer

[DB2-L] Index compession
Hello Tor

since we run SAP systems on zDB2 there was a good reason for us to "compress" some of our indexes. The main reason was to save space but when my colleague made a test in a sandbox system it also seemed that even SAP programs had a benefit of it - which indeed is one of the benefits being announced.

I think it is a good idea to start with secondary indexes but you surely know that is advised to have a look at the prospective gain with DSN1COMP in order to decide which Size fits best. It is worthwhile.
And ( OBS! ) there is a ceveat : be sure that you have implemented UK51666/PK94165 which corrects a problem with certain combinations of compressed indexes. Otherwise you have to do the work over again.

Best regards

Martin

-----Ursprüngliche Nachricht-----
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Tor-Roger Løken
Gesendet: Mittwoch, 20. Januar 2010 15:41
An: [login to unmask email]
Betreff: [DB2-L] Index compession


Hi !

We are thinking of using index compression in our production enviroment on secondary indexes.
Have anyone out there good/bad experience with index compression or ...... ?

Tor Roger

________________________________


IDUG - The Worldwide DB2 User Community! < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

____________________________________________________________________________________________________
Alfred Kärcher GmbH & Co. Kommanditgesellschaft,
Sitz Winnenden, Registergericht: Stuttgart, HRA 260169
Persönlich haftende Gesellschafterin: Kärcher Reinigungstechnik GmbH,
Sitz Winnenden, Registergericht: Stuttgart, HRB 262404
Geschäftsführer: Hartmut Jenner (Vorsitzender), Markus Asch, Dieter Grajer, Thomas Popp

Please refer to http://www.kaercher.com/legal for additional corporate and legal information.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

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

Peter Vanroose

Re: Index compession
(in response to Martin Ketterer)
For most indexes, you should indeed go for 8K or 16K index bufferpools,
combined with compression.
Beware that you only will have DASD storage reduction (either 50% or 75%),
no bufferpool use reduction (since in memory the index data is
non-compressed). On the other hand, in the BP the index pages are larger so
on average you loose less unusable space at the end of a page. And the #
levels might be smaller than before.

Only do this for indexes on columns with "compressible" data, i.e., mostly
textual data of sufficient width: first verify the compression rate of your
index; based on that, choose the right BP size. (16K when compression ratio
is more than 75%, 8K if more than 50%, 4K without compression otherwise.)

-- Peter Vanroose
ABIS Training & Consulting.


On Wed, 20 Jan 2010 15:41:04 +0100, Tor-Roger Løken <[login to unmask email]> wrote:
>We are thinking of using index compression in our production enviroment on
>secondary indexes.
>Have anyone out there good/bad experience with index compression or ...... ?
>
>Tor Roger

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

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