IC99860: POSSIBLE WRONG RESULTS WHEN INDEX JUMP SCANS ARE USED IN REFERENTIAL INTEGRITY CHECKING

DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• 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.
  

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.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All users                                                    *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to Version 10.1 Fix Pack 4                           *
  ****************************************************************
  

Problem conclusion

• First fixed in Version 10.1 Fix Pack 4
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  IT01024

Fix information

• Fixed component name

  DB2 FOR LUW

• Fixed component ID

  DB2FORLUW

Applicable component levels

• RA10 PSN

     UP