IBM Support

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

Subscribe

You can track all active APARs for this component.

 

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 number

    IC99860

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2014-03-05

  • Closed date

    2014-07-18

  • Last modified date

    2014-07-18

  • 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

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"10.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
18 July 2014