Locktimeout and Deadlock Errors in db2diag.log

[Mark A]

I noticed that on some of my DB2 Linux 9.7.4 databases, locktimeout and deadlock errors are written to
the db2diag.log as warning messages, but on others they are not. The only thing I can correlate so far
is that some databases were migrated to 9.7 from 9.5 (and show the errors) and some were created
new in 9.7 (and do not show the errors in db2daig.log). Does anyone know anything about this.

[juraj....@gmail.com]

I would start checking the diaglevel:
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.config.doc/doc/r0000298.html

One difference in versions I am aware is that v9.5 by default: "all
databases have an event monitor defined named DB2DETAILDEADLOCK, which
records detailed information about deadlock events."
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.admin.mon.doc/doc/c0005721.html
New 9.7 databases does not have this monitor.

[Mark A]

On Sat, 07 Jan 2012 04:47:55 -0800, juraj....@gmail.com wrote:
> I would start checking the diaglevel:
> http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.config.doc/doc/
r0000298.html
>
> One difference in versions I am aware is that v9.5 by default: "all
> databases have an event monitor defined named DB2DETAILDEADLOCK, which
> records detailed information about deadlock events."
> http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.admin.mon.doc/doc/
c0005721.html
> New 9.7 databases does not have this monitor.

Diaglevel is the same (3 - All Errors and Warnings). Deadlock and Locktimeouts are Warnings on the
database that shows them (created in 9.5 and migrated to 9.7).

I am aware of the new lock event monitor recommendations for 9.7 (the type of default event monitor
used for DETAILDEADLOCK is deprcated and a new Lock Monitor type exists) and I have implemented
the new lock monitoring on some databases.

But I don't see warning errors in the db2diag.log for these events like on some of my other databases
created in 9.5 and migrated to 9.7. However, I just found out that even on some databases on other
systems that were migrated from 9.5 to 9.7 there is no logging of these warnings in the db2diag.log
either. So my orginal theory seems to be wrong, but I have no idea why this is happening on some and
not others. Not sure if there is change from 9.7.4 to 9.7.5.

[mike]

On Jan 7, 4:55 pm, Mark A <m...@nowhere.com> wrote:

> On Sat, 07 Jan 2012 04:47:55 -0800, juraj.hra...@gmail.com wrote:
> > I would start checking the diaglevel:

> >http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db...

> r0000298.html
>
> > One difference in versions I am aware is that v9.5 by default: "all
> > databases have an event monitor defined named DB2DETAILDEADLOCK, which
> > records detailed information about deadlock events."

> >http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db...

> c0005721.html
> > New 9.7 databases does not have this monitor.
>
> Diaglevel is the same (3 - All Errors and Warnings). Deadlock and Locktimeouts are Warnings on the
> database that shows them (created in 9.5 and migrated to 9.7).
>
> I am aware of the new lock event monitor recommendations for 9.7 (the type of default event monitor
> used for DETAILDEADLOCK is deprcated and a new Lock Monitor type exists) and I have implemented
> the new lock monitoring on some databases.
>
> But I don't see warning errors in the db2diag.log for these events like on some of my other databases
> created in 9.5 and migrated to 9.7. However, I just found out that even on some databases on other
> systems that were migrated from 9.5 to 9.7 there is no logging of these warnings in the db2diag.log
> either. So my orginal theory seems to be wrong, but I have no idea why this is happening on some and
> not others. Not sure if there is change from 9.7.4 to 9.7.5.

One possible reason for the difference (i.e -911 behaviour) is
CUR_COMMIT. On DB2 v9.7 any migrated databases has this OFF while
freshly created v9.7 databases have this ON by default.
This CUR_COMMIT setting influences locking behaviour. To really
discover what's happening on V9.7 locking, use the new 'event monitor
for locking' feature, even if its current implementation is untidy.

[Mark A]

On Mon, 16 Jan 2012 09:28:55 -0800, mike wrote:
> One possible reason for the difference (i.e -911 behaviour) is
> CUR_COMMIT. On DB2 v9.7 any migrated databases has this OFF while
> freshly created v9.7 databases have this ON by default. This CUR_COMMIT
> setting influences locking behaviour. To really discover what's
> happening on V9.7 locking, use the new 'event monitor for locking'
> feature, even if its current implementation is untidy.

I didn't say there is a difference in -911 behavior. I said there is a difference in whether the error
shows up in db2diag.log at level 3. This difference occurs on two different 9.7 databases.