DB2 - Problembeschreibung
Problem IT01024 | Status: Geschlossen |
POSSIBLE WRONG RESULTS WHEN INDEX JUMP SCANS ARE USED IN REFERENTIAL INTEGRITY CHECKING | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / A50 - DB2 | |
Problembeschreibung: | |
Incorrect results might be returned when an index jump scan is used in referential integrity checking, and when another concurrent transaction moves a child row during the referential integrity scan of the child table by updating the index key of the child table. For example, assume we have a child table c (c1, c2, c3), where (c1, c2) is foreign key with delete cascade. And we have an index on (c1, c3, c2). The index has the following key values ('b', 'a', 'a'), ('b', 'c', 'a'), ('b', 'x', 'b'), ('b', 'y', 'b'), ('b', 'z', 'b'). Transaction A executes a delete statement: delete from p where c1 = 'b' and c2 ='b' A referential integrity scan is needed on the child table to delete the rows that have the corresponding foreign key value. If we choose to use index jump scan using key predicates c1='b' and c2='b', when the scan is positioned at ('b', 'x', 'b'), transaction B updates ('b', 'z', 'b') to ('b', 'b', 'b'); that is, transaction B moves the row behind the current position of the referential integrity scan. After both transactions finish, the child table has a foreign key value that doesn't exist in the parent table. $ db2 "select * from p" C1 C2 -- -- a a b a 2 record(s) selected. $ db2 "select * from c" C1 C2 C3 -- -- -- b a a b b b b a c 3 record(s) selected. To confirm if you hit the problem, get a query plan of the SQL statement. If you see index jump scan is used in the referential integrity validation scan (Look for "Gap Info" to identify jump scan), e.g. Gap Info: Status --------- ------ Index Column 1: No Gap Index Column 2: Gap Index Column 3: No Gap and the following in the operator details of the index jump scan: ROWLOCK : (Row Lock intent) ROWLOCK : ... WITHOUT END OF RANGE TBISOLVL: (Table access Isolation Level) TBISOLVL: REPEATABLE READ Then you will probably hit the problem. | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * All users * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to DB2 version 10.5.0.4. * **************************************************************** | |
Local-Fix: | |
You can avoid the problem by disabling jump scan using the JUMPSCAN option of the DB2_REDUCED_OPTIMIZATION variable: DB2_REDUCED_OPTIMIZATION="JUMPSCAN OFF". You can also use the REGISTRY optimization guideline element to set this at the application or statement level. | |
verfügbare FixPacks: | |
DB2 Cancun Release 10.5.0.4 (also known as Fix Pack 4) for Linux, UNIX, and Windows | |
Lösung | |
The problem is first fixed in DB2 version 10.5.0.4. | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 14.04.2014 08.09.2014 08.04.2015 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
Problem behoben lt. FixList in der Version | |
10.5.0.4 |