Prev: SQL code 437 in DB2 V9.5
Next: Communications problems
From: Troels Arvin on 5 Jan 2010 08:09
Hello,

I've recently upgraded a number of DB2 (AIX) instances from v. 9.5.4 to
v. 9.7.1, and I've set CUR_COMMIT to ON (yeah!).

However, I have feeling that some DB configuration parameters might have
to be adjusted because of this.

Right now, I'm looking at the logbufsz parameter in a rather large
database (10TB; server has around 20GB of RAM), currently set to 512. The
documentation[1] suggests that one should look at the unit of work log
space used for a transaction and somehow from that be able to determine a
sane value for logbufsz. However:

- There are many concurrent operations in the database; which
transaction should I look at?

- If the UOW log space used for a transaction is x, what does
that mean for the logbufsz DB configuration parameter?

For example, I'm looking at a long running transation; it has
UOW log space used (Bytes) = 26293331
What does this tell me?




1: http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/
com.ibm.db2.luw.admin.config.doc/doc/r0000243.html

--
Troels
From: MarkB on 11 Jan 2010 03:14
Hi Troels,

>
>  - If the UOW log space used for a transaction is x, what does
>    that mean for the logbufsz DB configuration parameter?
>
> For example, I'm looking at a long running transation; it has
> UOW log space used (Bytes)                 = 26293331
> What does this tell me?
>

When the log buffer becomes full it have to be written to disk.
So in case of such large transaction (when for this transaction 'log
space used' > logbufsz) you can get more i/o logging activity then in
case of appropriate logbufsz.
You should investigate num_log_buffer_full monitor element.
The goal is to minimize this counter.
To optimize rollbacks performance you can look at the log_reads
monitor element to minimize this counter also by increasing logbufsz.

Regards,
Mark B.
 | 
Pages: 1
Prev: SQL code 437 in DB2 V9.5
Next: Communications problems