First, DB2 compression is a "dictionary" style
algorithm, very similar to PKZIP -- compressions
builds a dictionary, decompression is a lookup. It is
also row level, which means some rows do not get
compressed. It is not "page level compression".
Table spaces only are compressed, never index spaces.
Compression in the table is a "dictionary match"
level, and not column level - pure data only - so any
reference to index columns regarding what is or is not
compressed is meaningless. Understand that when I say
'dictionary match' I am greatly simplifying the tree
structure used to implement this type of algorithm.
SQL and tablespace scan WILL NOT CAUSE DECOMPRESSION
of 100%. It will only cause decompression on rows
SELECTED, if and only if those columns were in a row
that was compressed.
Also, the more rows that are compressed, the
difference in the costing of the SQL, and hence the
possible difference in access path selection and
methods, such as list prefetch may be more desirable,
As to you comment on the 4k to 2k row size issue --
that is one of the reason that there are 4k, 8k, 16k,
and 32k page sizes to benefit from in V6.
--- "Humphris,Richard P.(NXI)"
<[login to unmask email]> wrote:
> Hi Jim,
> I'm just going to throw out a couple of thoughts. I
> don't know what DB2's
> compression looks like or the internals of DB2. But
> I'll make some
> statements and ask some questions that you (or
> someone else) may be able to
> Is think the following statement is true:
> When the administrator guide says "The Corresponding
> index data is NOT
> compressed.". I think this means that the the index
> columns in the data
> table will be compressed; but the index table will
> not compressed.
> This means that:
> Sql code that DB2 uses the index for, will be almost
> as efficient as before.
> But if DB2 doesn't have an index to use (or decides
> to use a tablespace
> scan anyway), then all 1,000 cylinders will have to
> be decompressed for the
> SQL statement. This may account for a significant
> waste of CPU. On the
> otherhand, a great number of short transactions,
> using the index (and
> avoiding unneccessary decompression) may keep the
> overall CPU increase to
> acceptable levels. It may be that compressing a
> table means creating
> secondary indexes (if they'll be used) may become
> more important. Does this
> also mean that fooling DB2 into using indexes
> becomes more important with
> compressed tables too?
> Another question: does DB2 treat decompressed
> tables any different from
> compressed tables when trying to decide if an index
> should be ignored?
> Btw, the worst thing that could happen is that your
> row sizes are large
> (almost the size of a 4k page) and the rows
> decompressed equivlent is
> greater than 2k. A compress of such a perverse
> table will result in no
> space savings for the table but with still require
> additional cpu usage to
> compress/uncompress the row. Note: IBM documented
> this behaviour.
> > -----Original Message-----
> > From: Jim Lewandowski
> [SMTP:[login to unmask email]
> > Sent: Monday, December 13, 1999 1:36 AM
> > To: [login to unmask email]
> > Subject: Re: DB2 Compression on RVA boxes
> > Since the DBAs think I'm just a "DASD guy", I was
> never involved in the
> > benchmarks. I am ONLY able to look at it
> > Here is the pertinent info:
> > LARGE table (about 1000 cyls - I don't know
> exactly as it is ptn'ed, I
> > believe)
> > 90+% buffer hit (neither randomr or seq. access is
> far away from the
> > other - i.e. both are high as we have/had about
> 60,000 pages for this
> > compressed) table.
> > As, the SQL started, a lot of the data pages are
> in the bufferpool. I
> > would have to look at NPAGES for all the data
> partitions to know what %
> > is sitting (60,000 bufferpool pages) in the
> bufferpool at any point in
> > time.
> > From my memory, these queries were totally CPU
> bound (virtually no
> > physical I/O at all - i.e. 1 complete CP usage in
> the complex).
> > Since, I am using MVS Omegamon, I can ONLY look
> (INSPECT) the thread TCB
> > CPU when it is a long-running query (semi-ugly
> join or high # of data
> > rows hit).
> > When using inspect, I see 33% to 67% (depending on
> what else the SQL may
> > be doing) of the CPU right on the CMPSC X'B263'
> instruction. As I
> > clearly stated in older posts, it COULD be an
> issue of SRB
> > scheduling/sampling that always "happens" to be
> showing the thread TCB
> > PSW on the CMPSC instruction, but I tried
> different sampling rates to
> > see if it changed. I did not see any noticable
> > I had also asked others who have STROBE or TMON to
> see what THEY see in
> > a semi-long-running enough SQL to get some
> > Obviously, running a before and after with
> compress off/on would be
> > ideal but I was not allowed to view the
> benchmarking that allowed the
> > compression decision.
> > Jim Lewandowski
> > To change your subscription options or to cancel
> your subscription visit
> > the DB2-L webpage at http://www.ryci.com/db2-l.
> The owners of the list can
> To change your subscription options or to cancel
> your subscription visit the DB2-L webpage at
owners of the list
[login to unmask email]
Do You Yahoo!?
Thousands of Stores. Millions of Products. All in one place.
Yahoo! Shopping: http://shopping.yahoo.com