LI73604: INDEX SCAN USING EXCLUSIVE START KEY MIGHT RETURN INCORRECT RESULTS

DB2 Version 9.1 Fix Pack 7  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 6   for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 6a for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 7a  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 8  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 9  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 10  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 11  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 12  for Linux, UNIX and Windows

APAR status

• Closed as program error.

Error description

• Incorrect scan results might be returned by an index scan if you
  have an index with at least three levels.
  
  The problem might only occur during an index scan if there is a
  concurrent transaction inserting or updating the same index,
  which causes a non leaf index page split. In particular, the
  problem occurs when the scan probes down from the index root
  page and needs to scan the same non-leaf index page that was
  affected by the index page split, and the index key that the
  scan needs to look for has since been moved away from the
  non-leaf page.
  
  
  For example, if you perform a query against index columns (i1,
  i2) and use exclusive start key (using  > or <, instead of >= or
  <=), incorrect rows might be returned:
  
  SELECT i1,i2 FROM t1 WHERE i1 > 14
  I1          I2
  ----------- -----------
           13          1
           12          1
           10          1
            5          1
            4          1
            3          1
            2          1
            1          1
    8 record(s) selected.
  
  In this example the correct results should have been:
  I1          I2
  ----------- -----------
           15           1
           16           1
           17           1
           18           1
           19           1
           20           1
    6 record(s) selected.
  

Local fix

• Use an inclusive start key instead of exclusive start key. For
  example:
  SELECT i1,i2 FROM t1 WHERE i1 >= 15
  

Problem summary

• see error description.
  

Problem conclusion

• First Fixed in DB2 Version 9.1 Fixpack 6
  

Temporary fix

Comments

APAR Information

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

  LI73598

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

Fix information

• Fixed component name

  DB2 UDE ESE LIN

• Fixed component ID

  5765F4104

Applicable component levels

• R910 PSY

     UP