By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,756 Members | 1,721 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,756 IT Pros & Developers. It's quick & easy.

BIZZARE MQT-BASE Table Locking Behaviour

P: n/a
I have come across a bizzare behaviour with DB2/UDB 8.2 on SuSE Linux

When I have a MQT Refresh going on (complete refresh) it appears to
lock the underlying base tables used to build the MQT.

When I attempt to SELECT these tables in other sessions, it simply
refuses to yield and are waiting for the MQT to finish.

Any clues ? Is this the intended behaviour?

With capability for Rollback and Read Consistency that DB2 does not
have (in comparison to Oracle), it appears to be reasonable behaviour
if extremely annoying behaviour.

If I could not have a incremental refresh, let me know how to get this
MQT refreshed short of having to have it USER MANAGED.

Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply

P: n/a
Even more bizarre is the following behaviour:

First let me out line the situation.

Session A of the DB2 command line has REFRESH TABLE statement running
on a MQT (based on a join of several tables)

Session B of the DB2 CLP (with or without Auto commit options)
a) SELECT on one of the tables that the MQT is based off
The command line does not return and the session appears to be
locked because the MQT holds a lock (this we verified by checking the
locks/waits on the system)

b) I issued a CONTROL-C on this Session B, it displayed the data

c) I issued the statement again...IT displayed the data for the
SELECT with no hesitation (Locks held by the MQT disappared on the base

d) I terminated session, started a new session and issued the same
select, it returns all the rows for the SELECT with no locks.

e) The SELECT on the table where the CONTROL-C was issued returned
results after this point, even though the MQT was still running.

I could repeat the same behaviour for all the tables in the MQT.
Here is my concern:

The Locking of the Base tables I can sympathise with

But how can a CONTROL-C issued in a session waiting for Locks
to be released, release the locks in the Holding session.

Simply beyond me.

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.