DB2 - Problembeschreibung
Problem IT00818 | Status: Geschlossen |
UNNECESSARY U-LOCKS PLACED ON STATISTICAL VIEWS DURING REFRESH MQT OR SET INTEGRITY | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 | |
Problembeschreibung: | |
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. | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * all * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade the DB2 Server to V9.7 Fix Pack 10 or higher * **************************************************************** | |
Local-Fix: | |
Lösung | |
This problem will first be fixed in V9.7 Fix Pack 10 | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 04.04.2014 05.01.2015 05.01.2015 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
9.7.FP10 | |
Problem behoben lt. FixList in der Version | |
9.7.0.10 |