What is DB2 Doing Index Compression

Jorg Lueke

What is DB2 Doing Index Compression
This is for DB2 9 on z/OS.

We can alter the index bufferpool to BP8K2 then Compress Y. This puts the
index in rebuild pending status. But if one removes that flag the index is
accessible via BP8K2 with no obvious adverse affects. Obviously the index
dataset is not compressed and presumable the software decompression is
running. But would there be any adverse affects in removing the rebuild
idnex flag? I suppose we should see what happens on an Insert...

_____________________________________________________________________

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

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

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

Chris Blaicher

Re: What is DB2 Doing Index Compression
(in response to Jorg Lueke)
I have not tried something like that, but my impression is that it is like loading a double-barreled shotgun, aiming it at your foot and pulling the trigger.

At best, you now have a catalog that says it is compressed and an index that is not. How different parts of DB2 and other utility vendors products play with that is up for grabs.

Chris Blaicher
Phone: 512-340-6154
Mobile: 512-627-3803
-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jorg Lueke
Sent: Wednesday, January 06, 2010 1:20 PM
To: [login to unmask email]
Subject: [DB2-L] What is DB2 Doing Index Compression

This is for DB2 9 on z/OS.

We can alter the index bufferpool to BP8K2 then Compress Y. This puts the
index in rebuild pending status. But if one removes that flag the index is
accessible via BP8K2 with no obvious adverse affects. Obviously the index
dataset is not compressed and presumable the software decompression is
running. But would there be any adverse affects in removing the rebuild
idnex flag? I suppose we should see what happens on an Insert...

_____________________________________________________________________

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

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

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: What is DB2 Doing Index Compression
(in response to Chris Blaicher)
If someone is going to test this: be aware that the data in the bufferpool
is never compressed, so as long as the index is in memory things could be
fine. So make sure to flush the buffer pool (e.g. by stopping the index
space) after your INSERT and before verifying whether that INSERT ended up
correctly in the index.

Just my 2c ...

-- Peter Vanroose
ABIS Training & Consulting
Leuven, Belgium

--

_____________________________________________________________________

* 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

Roger Miller

Re: What is DB2 Doing Index Compression
(in response to Peter Vanroose)
As noted, you could try to hurt yourself. One other possibility is that you are
getting access to the data and using 8K pages that have only 4K of data, so
that your bufferpool space would need to double in size or you could have
lots of IO for an under sized buffer pool. Do you you have time to see what
problems you cause for yourself?

Roger Miller, DB2 for z/OS

Subject: Re: What is DB2 Doing Index Compression
From: "Blaicher, Chris" <[login to unmask email]>
Reply-To: IDUG DB2-L <[login to unmask email]>
Date: Wed, 6 Jan 2010 14:13:50 -0700

I have not tried something like that, but my impression is that it is like loading
a double-barreled shotgun, aiming it at your foot and pulling the trigger.

At best, you now have a catalog that says it is compressed and an index that
is not. How different parts of DB2 and other utility vendors products play with
that is up for grabs.

Chris Blaicher
Phone: 512-340-6154
Mobile: 512-627-3803
-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jorg
Lueke
Sent: Wednesday, January 06, 2010 1:20 PM
To: [login to unmask email]
Subject: [DB2-L] What is DB2 Doing Index Compression

This is for DB2 9 on z/OS.

We can alter the index bufferpool to BP8K2 then Compress Y. This puts the
index in rebuild pending status. But if one removes that flag the index is
accessible via BP8K2 with no obvious adverse affects. Obviously the index
dataset is not compressed and presumable the software decompression is
running. But would there be any adverse affects in removing the rebuild
idnex flag? I suppose we should see what happens on an Insert...




_____________________________________________________________________

* 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

Jorg Lueke

Re: What is DB2 Doing Index Compression
(in response to Jorg Lueke)
No, this is not somethign we are doing as standard operating procedure it
just came out of some curiosity. Apparently the software compression is
fairly smart :-)


On Wed, 6 Jan 2010 14:13:50 -0700, Blaicher, Chris
<[login to unmask email]> wrote:

>SSBoYXZlIG5vdCB0cmllZCBzb21ldGhpbmcgbGlrZSB0aGF0LCBidXQgb
XkgaW1wcmVzc2lvbiBp
>cyB0aGF0IGl0IGlzIGxpa2UgbG9hZGluZyBhIGRvdWJsZS1iYXJyZWxlZCBz
aG90Z3VuLCBhaW1p
>bmcgaXQgYXQgeW91ciBmb290IGFuZCBwdWxsaW5nIHRoZSB0cmlnZ2
VyLg0KDQpBdCBiZXN0LCB5
>b3Ugbm93IGhhdmUgYSBjYXRhbG9nIHRoYXQgc2F5cyBpdCBpcyBjb21w
cmVzc2VkIGFuZCBhbiBp
>bmRleCB0aGF0IGlzIG5vdC4gIEhvdyBkaWZmZXJlbnQgcGFydHMgb2YgR
EIyIGFuZCBvdGhlciB1
>dGlsaXR5IHZlbmRvcnMgcHJvZHVjdHMgcGxheSB3aXRoIHRoYXQgaXMg
dXAgZm9yIGdyYWJzLg0K
>DQpDaHJpcyBCbGFpY2hlcg0KUGhvbmU6IDUxMi0zNDAtNjE1NA0KTW9i
aWxlOiA1MTItNjI3LTM4
>MDMNCi0tLS0tT3JpZ2luYWwgTWVzc2FnZS0tLS0tDQpGcm9tOiBJRFVHI
ERCMi1MIFttYWlsdG86
>REIyLUxASURVR0RCMi1MLk9SR10gT24gQmVoYWxmIE9mIEpvcmcgTH
Vla2UNClNlbnQ6IFdlZG5l
>c2RheSwgSmFudWFyeSAwNiwgMjAxMCAxOjIwIFBNDQpUbzogREIyLUx
AV1dXLklEVUdEQjItTC5P
>UkcNClN1YmplY3Q6IFtEQjItTF0gV2hhdCBpcyBEQjIgRG9pbmcgSW5kZX
ggQ29tcHJlc3Npb24N
>Cg0KVGhpcyBpcyBmb3IgREIyIDkgb24gei9PUy4NCg0KV2UgY2FuIGFsd
GVyIHRoZSBpbmRleCBi
>dWZmZXJwb29sIHRvIEJQOEsyIHRoZW4gQ29tcHJlc3MgWS4gIFRoaXMg
cHV0cyB0aGUgDQppbmRl
>eCBpbiByZWJ1aWxkIHBlbmRpbmcgc3RhdHVzLiAgQnV0IGlmIG9uZSByZ
W1vdmVzIHRoYXQgZmxh
>ZyB0aGUgaW5kZXggaXMgDQphY2Nlc3NpYmxlIHZpYSBCUDhLMiB3aXR
oIG5vIG9idmlvdXMgYWR2
>ZXJzZSBhZmZlY3RzLiAgT2J2aW91c2x5IHRoZSBpbmRleCANCmRhdGFz
ZXQgaXMgbm90IGNvbXBy
>ZXNzZWQgYW5kIHByZXN1bWFibGUgdGhlIHNvZnR3YXJlIGRlY29tcHJlc3
Npb24gaXMgDQpydW5u
>aW5nLiAgQnV0IHdvdWxkIHRoZXJlIGJlIGFueSBhZHZlcnNlIGFmZmVjdHM
gaW4gcmVtb3Zpbmcg
>dGhlIHJlYnVpbGQgDQppZG5leCBmbGFnPyAgSSBzdXBwb3NlIHdlIHNob
3VsZCBzZWUgd2hhdCBo
>YXBwZW5zIG9uIGFuIEluc2VydC4uLg0KDQpfX19fX19fX19fX19fX19fX19f
X19fX19fX19fX19f
>X19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX18NCg0KKiBJR
FVHIE5vcnRoIEFt
>ZXJpY2EgKiBUYW1wYSwgRmxvcmlkYSwgKiBNYXkgMTAtMTQgMjAxMC
AqICBodHRwOi8vSURVRy5P
>UkcvTkEgKg0KX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX
19fX19fX19fX19f
>X19fX19fX19fX19fX19fX19fX19fDQoNCmh0dHA6Ly93d3cuaWR1Zy5vcm
cvZGIyLXZpZGVvcy5o
>dG1sIGhhcyBodW5kcmVkcyBvZiB2aWRlbyBwcmVzZW50YXRpb25zIQ0K
RGlkIHlvdSBtaXNzIG91
>dCBvbiBhdHRlbmRpbmcgYW4gSURVRyBjb25mZXJlbmNlPw0KTWFueS
BvZiB0aGUgcHJlc2VudGF0
>aW9ucyB3ZXJlIHJlY29yZGVkIGFuZCBhcmUgYXZhaWxhYmxlIG9uIG91ciB
3ZWJzaXRlIQ0KX19f
>X19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX1
9fX19fX19fX19f
>X19fX19fX19fDQoNCklmIHlvdSBuZWVkIHRvIGNoYW5nZSBzZXR0aW5nc
ywgaHR0cDovL3d3dy5p
>ZHVnLm9yZy9jZ2ktYmluL3dhP0EwPURCMi1MIGlzIHRoZSBob21lIG9mIElE
VUcncyBEQjItTA0K

_____________________________________________________________________

* 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

Jorg Lueke

Re: What is DB2 Doing Index Compression
(in response to Roger Miller)
I have colleagues that like to probe a little deeper, then I get caught up in
understanding what exactly is happening. When we use this in production
the indexes will be rebuilt. We probably won't even try to change a
compressed index back to the 4K bufferpool and then try to access it without
doing a rebuild ;-P, I'd think that should definitely fail.

On Fri, 8 Jan 2010 16:18:18 +0000, Roger Miller <[login to unmask email]>
wrote:

>As noted, you could try to hurt yourself. One other possibility is that you are
>getting access to the data and using 8K pages that have only 4K of data, so
>that your bufferpool space would need to double in size or you could have
>lots of IO for an under sized buffer pool. Do you you have time to see what
>problems you cause for yourself?
>
>Roger Miller, DB2 for z/OS
>
>Subject: Re: What is DB2 Doing Index Compression
>From: "Blaicher, Chris" <[login to unmask email]>
>Reply-To: IDUG DB2-L <[login to unmask email]>
>Date: Wed, 6 Jan 2010 14:13:50 -0700
>
>I have not tried something like that, but my impression is that it is like
loading
>a double-barreled shotgun, aiming it at your foot and pulling the trigger.
>
>At best, you now have a catalog that says it is compressed and an index
that
>is not. How different parts of DB2 and other utility vendors products play
with
>that is up for grabs.
>
>Chris Blaicher
>Phone: 512-340-6154
>Mobile: 512-627-3803
>-----Original Message-----
>From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jorg
>Lueke
>Sent: Wednesday, January 06, 2010 1:20 PM
>To: [login to unmask email]
>Subject: [DB2-L] What is DB2 Doing Index Compression
>
>This is for DB2 9 on z/OS.
>
>We can alter the index bufferpool to BP8K2 then Compress Y. This puts
the
>index in rebuild pending status. But if one removes that flag the index is
>accessible via BP8K2 with no obvious adverse affects. Obviously the
index
>dataset is not compressed and presumable the software decompression
is
>running. But would there be any adverse affects in removing the rebuild
>idnex flag? I suppose we should see what happens on an Insert...
>
>
>
>
>__________________________________________________________
___________
>
>* 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

_____________________________________________________________________

* 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

Jorg Lueke

Re: What is DB2 Doing Index Compression
(in response to Jorg Lueke)
It looks like http://www-01.ibm.com/support/docview.wss?uid=swg1PK79312
will reduce the amount of indexes in rebuild pending

_____________________________________________________________________

* 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