we are using an MQT for the first time . when we started testing application even with volume (100 rows per table), the deadlocks are all over the place . If we drop the MQT , deadlocks are gone. Here are the details of the MQT

TableA( Column_A_1, Colum_A_2, Column_A_3, Column_A_4)

TableB(Column_B_1, Column_B_2)

and the MQT is defined as

Select sum(A.Column_A_3), count(*) Column_A_2, Column_B_2 from
TableA A, TableB B
where A.Column_A_1 = B.Column_B_1
refresh imediate, query optimization enabled

TableA & TableB gets updated/inserted virtually every millisecond and these are high volume tables. And we are using this MQT in an OLTP application

Did any one face the similar problem? Any help/clues will be appreciated

-Rama

Hmm, deadlocks are not directly connected to MQTs as such.
And I thinks that is still to few information.

So you have multiple applications doing insert/update on table A and table B and selects on the MQT?

Since it is defined as refresh immediate, any update on table A or B will also cause an update including lock on the MQT.

Well, you have to be more careful then: commit as often as possible.

If not needed, don't use the refresh immediate but refresh yourself on e regular basis - this avoids locking issues as well.

How is the ratio of update/inserts on table A and B versus selects on the MQT? do you gain much using the MQT at all ?

In OLTP I have used MQTs only few times as a tool to avoid long calculation times e.g. with XML extender exporting documents: I precalculated the strings, so the export run much faster (In V7 that used to be a problem). Here I did not need the refresh immediate.

And then of course: the usual stuff applies:
- check your isolation levels
- check whether decent indices are defined also for the MQT selects
- specify select statements always with 'for read only'
- check on db2set profile variables DB2_EVALUNCOMMITED, DB2_SKIPDELETED, DB2_SKIPINSERTED etc, whether those are interesting for you.

Or use View instead of MQT (unless there is a specific reason to use MQT)

I don't think it is wise to use a REFRESH IMMEDIATE MQT in your situation. While the refresh process calculates the summary it has to ensure both tables are consistent so it will most likely lock them to prevent updates.