During query compilation of REFRESH MQT or SET INTEGRITY
statements, U - update - locks are obtained on sysibm.systables
rows that correspond to other MQTs that are used during query
compilation of the statement.
This is correct behaviour and working as designed. However, it
is also possible that these U locks will be placed on rows
corresponding to statistical views as well.
If concurrent REFRESH or SET INTEGRITY statements are then
executed, it can lead to lock timeouts or deadlocks during query
compilation for these statements. This is internally handled by
the query compiler and the compilation will succeed, but the
recompilation will no longer make use of the statistical view
information. This can then lead to suboptimal query execution
for concurrently running REFRESH or SET INTEGRITY statements.
The following test shows that during the execution of the
refresh MQT statement, a U - for update - lock is held on the
statistical view for the duration of the refresh :
create table tab1 ( col1 int, col2 int, col3 int );
create table tab2 ( col1 int , col2 int , col3 int );
create view sv1 as ( select tab1.* from tab1, tab2 where
tab1.col1 = tab2.col1
and tab1.col2 = tab2.col2 );
alter view sv1 enable query optimization;
runstats on table db2inst1.sv1 with distribution;
create table mqt1 ( col1 , col2 ) as ( select tab1.col3, sum(
tab2.col3)
from tab1 , tab2
where tab1.col1 = tab2.col1 and
tab1.col2 = tab2.col2
group by tab1.col3 )
DATA INITIALLY DEFERRED
REFRESH DEFERRED ENABLE QUERY OPTIMIZATION
MAINTAINED BY SYSTEM ;
db2 +c -tvf "refresh table mqt1"
db2pd -db sample -locks show
will show a U lock on systables matching the SV1 entry
( and an X lock matching the MQT1 entry )
The fix for this APAR will relax the lock mode for the
statistical views to a lock mode that will allow concurrent
compilations to make use of the statistical view information. |
