V9 Compression & Log Use

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mike_dba - 05 Dec 2007 15:06 GMT

I have been testing compression for update operations. Can anyone
tell me why I require more log for an update of a compressed table
than I do for the same table that is not compressed ?

I tried an update for the same number of rows for two copies of a
table, one compressed and one not. The compressed UOW exceeds my log
allocation while the non-compressed does not.

Thanks

Reply to this Message

Serge Rielau - 05 Dec 2007 16:26 GMT

> I have been testing compression for update operations. Can anyone
> tell me why I require more log for an update of a compressed table
[quoted text clipped - 3 lines]
> table, one compressed and one not. The compressed UOW exceeds my log
> allocation while the non-compressed does not.

Huh? That's odd. The log records remain compressed. Simply speaking you
should see a similar compression ration for the logs as for the table.

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

mike_dba - 05 Dec 2007 17:07 GMT

> > I have been testing compression for update operations. Can anyone
> > tell me why I require more log for an update of a compressed table
[quoted text clipped - 14 lines]
> DB2 Solutions Development
> IBM Toronto Lab

I was thinking that maybe the updated column had some immense
compression on it (the table went from 22 Gb to 9 Gb). And the update
changed the dictionary and maybe there was no entry for the new value
in the dictionary so this caused some trickle down effect and caused
additional logging. But the fact that it fit into my logs for non-
compressed data is puzzling.

I am updating a 10 byte column in a 559 byte wide row. There are 72
million rows to update. The log started at 8 Gb and was increased to
12 Gb and still wouldn't fit.

Reply to this Message

Serge Rielau - 05 Dec 2007 18:24 GMT

>>> I have been testing compression for update operations. Can anyone
>>> tell me why I require more log for an update of a compressed table
[quoted text clipped - 23 lines]
> million rows to update. The log started at 8 Gb and was increased to
> 12 Gb and still wouldn't fit.

I do have a thought.
Could it be that you do have the dictionary, but the data is NOT
compressed. Then when you do the UPDATE DB2 compresses the page in
question which, of course, requires to log the whole page.
So what you are logging is not the update, but the piecemeal compression
of the table.

Make sure you run REORG after getting the dictionary

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

mike_dba - 05 Dec 2007 19:55 GMT

> >>> I have been testing compression for update operations. Can anyone
> >>> tell me why I require more log for an update of a compressed table
[quoted text clipped - 41 lines]
>
> - Show quoted text -

I just double checked :

alter table schema.fact_tbl_comp COMPRESS YES;
reorg table schema.fact_tbl_comp resetdictionary;
runstats ...
update --> sql0964 log full

Reply to this Message

mike_dba - 05 Dec 2007 20:35 GMT

> > >>> I have been testing compression for update operations. Can anyone
> > >>> tell me why I require more log for an update of a compressed table
[quoted text clipped - 50 lines]
>
> - Show quoted text -

Do you know if subsequent Inserts after the reorg simply use the
existing dictionary or add entries to the dictionary ?
Do you know the effect of Not Logged Initially on compression ?

I believe that you may have pointed my in the right direction with
regard to reorg. I'll test some more.

Reply to this Message

Serge Rielau - 06 Dec 2007 02:22 GMT

> Do you know if subsequent Inserts after the reorg simply use the

> existing dictionary or add entries to the dictionary ?
> Do you know the effect of Not Logged Initially on compression ?

Once the dictionary is created it remains static.
NLI has no impact on compression and vice versa.

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message