DB2 index and concurrency Analysis

DB2 index and concurrency analysis before DB2 V9.7 or if the CC feature is not enabled for DB2 V9.7, if the query scans records that are being inserted or modified, the query will be suspended, until the session of the modification record is submitted or rolled back. [Huateng @ db2server ~] $ Db2 connect to dbtest Database Connection Information Database server = DB2/LINUX 9.7.0 SQL authorization ID = HUATENG Local database alias = DBTEST [huateng @ db2server ~] $ Db2 get db cfg | grep CUR_COMMIT Currently Committed www.2cto.com (CUR_COMMIT) = DISABLED: the current database version is 9.7. The Currently Committed feature is not enabled. Session 1 was modified as follows and was not submitted: db2 => select * from tID ----------- 1 2 3 4 5 6 7 8 8 record (s) selected. db2 => update t set id = 9 where id = 8 DB20000I The SQL command completed successfully. db2 => when Session 2 performs the following query, it will cause suspension: db2 => select * from t where id = 1 until Session 1 is submitted. Www.2cto.com if the T table ID has an index, the result will be different: session 1: db2 => select * from tID ----------- 1 2 3 4 5 6 7 8 8 record (s) selected. db2 => create index idx_t_01 on t (id) DB20000I The SQL command completed successfully. db2 => commitDB20000I The SQL command completed successfully. db2 => update t set id = 9 where id = 8DB20000I The SQL command completed successfully. db2 => Session 2: perform the following query to instantly complete db2 => select * from t where id = 1I D ----------- 1 1 record (s) selected. At this time, the query will locate the record with id = 1 through the index, without scanning the record with ID = 8, so it will not block. Author: TOMSYAN