Help in gathering deadlock information

[Eric.Jones]

I'm trying to assist in analyzing a deadlock situation that sometimes occurs on a UDB/AIX system.
We have several java applications that connect via jdbc contending for a workflow table on the
server. Under certain conditions this leads to some threads deadlocking, and a resulting -911 from
the UDB deadlock detector. (UDB 8.2 on AIX 5.3)

Via db2monitor, and event monitor (for Statements, Transactions, and Deadlock with details), we have
been able to narrow it down to row-level contention between Insert/Select. (Lock types W & NS with a
type-2 index on primary key). The Deadlocked Connection record provides info on lock type, object
name, and RID, and the Statement record shows some of the dynamic SQL statement text. But still a
few key bits are ambiguous, and I'm not sure how to collect them. Namely:

1) How to fetch row info via RID in LUW?
We need to see the row contents for the RID listed in the lock object, but how?

For DB2 UDB v8 for z/OS one could use direct row access:
..where ID = ROWID(X'023C1F');
or maybe ..SQL TYPE IS ROWID my_rid;

But I don't find anything like this in UDB v8 for LUW..?
How can we do this with SQL?
2) How to find the actual values used by the deadlocked threads?

From the statement event info we can see SQL text, but with only parameter markers (?).
ie. INSERT INTO DSC.UT_LOCK (IDENT, KONTEXT, FK_BENUTZER, LETZTEMUTATION) VALUES ?, ?, ?, DEFAULT)
We need to see the actual host variable values to determine what was happening here. I didn't find
any SQLDA/SQLVAR info in the statement or transaction record..? Are these captured by the event
monitor?

I've asked the developers to try to dump info for both these items in a java exception handler
for the -911 event, but I'd prefer to get it from UDB directly if possible. Anyone have some
suggestions for filling in these blank spots?
Merci,
Eric

[Jan M. Nelken]

Eric.Jones wrote:

I'm trying to assist in analyzing a deadlock situation that sometimes
occurs on a UDB/AIX system.
We have several java applications that connect via jdbc contending for a
workflow table on the
server. Under certain conditions this leads to some threads deadlocking,
and a resulting -911 from
the UDB deadlock detector. (UDB 8.2 on AIX 5.3)

Via db2monitor, and event monitor (for Statements, Transactions, and
Deadlock with details), we have
been able to narrow it down to row-level contention between
Insert/Select. (Lock types W & NS with a
type-2 index on primary key). The Deadlocked Connection record provides
info on lock type, object
name, and RID, and the Statement record shows some of the dynamic SQL
statement text. But still a
few key bits are ambiguous, and I'm not sure how to collect them. Namely:

1) How to fetch row info via RID in LUW?
We need to see the row contents for the RID listed in the lock object,
but how?

For DB2 UDB v8 for z/OS one could use direct row access:
..where ID = ROWID(X'023C1F');
or maybe ..SQL TYPE IS ROWID my_rid;

But I don't find anything like this in UDB v8 for LUW..?
How can we do this with SQL?
2) How to find the actual values used by the deadlocked threads?

From the statement event info we can see SQL text, but with only
parameter markers (?).
ie. INSERT INTO DSC.UT_LOCK (IDENT, KONTEXT, FK_BENUTZER,
LETZTEMUTATION) VALUES ?, ?, ?, DEFAULT)
We need to see the actual host variable values to determine what was
happening here. I didn't find
any SQLDA/SQLVAR info in the statement or transaction record..? Are
these captured by the event
monitor?

I've asked the developers to try to dump info for both these items in a
java exception handler
for the -911 event, but I'd prefer to get it from UDB directly if
possible. Anyone have some
suggestions for filling in these blank spots?
Merci,
Eric

Can you forward me data collected so far?

Use rhis e-mail address:

jasinet at attglobal dot net
Jan M. Nelken

[Jan M. Nelken]

Eric.Jones wrote:

I'm trying to assist in analyzing a deadlock situation that sometimes
occurs on a UDB/AIX system.
We have several java applications that connect via jdbc contending for a
workflow table on the
server. Under certain conditions this leads to some threads deadlocking,
and a resulting -911 from
the UDB deadlock detector. (UDB 8.2 on AIX 5.3)

Via db2monitor, and event monitor (for Statements, Transactions, and
Deadlock with details), we have
been able to narrow it down to row-level contention between
Insert/Select. (Lock types W & NS with a
type-2 index on primary key). The Deadlocked Connection record provides
info on lock type, object
name, and RID, and the Statement record shows some of the dynamic SQL
statement text. But still a
few key bits are ambiguous, and I'm not sure how to collect them. Namely:

1) How to fetch row info via RID in LUW?
We need to see the row contents for the RID listed in the lock object,
but how?

For DB2 UDB v8 for z/OS one could use direct row access:
..where ID = ROWID(X'023C1F');
or maybe ..SQL TYPE IS ROWID my_rid;

But I don't find anything like this in UDB v8 for LUW..?
How can we do this with SQL?
2) How to find the actual values used by the deadlocked threads?

From the statement event info we can see SQL text, but with only
parameter markers (?).
ie. INSERT INTO DSC.UT_LOCK (IDENT, KONTEXT, FK_BENUTZER,
LETZTEMUTATION) VALUES ?, ?, ?, DEFAULT)
We need to see the actual host variable values to determine what was
happening here. I didn't find
any SQLDA/SQLVAR info in the statement or transaction record..? Are
these captured by the event
monitor?

I've asked the developers to try to dump info for both these items in a
java exception handler
for the -911 event, but I'd prefer to get it from UDB directly if
possible. Anyone have some
suggestions for filling in these blank spots?
Merci,
Eric

Can you forward me data collected so far?

Use rhis e-mail address:

jasinek at attglobal dot net
Jan M. Nelken