IBM Support

IT36818: POSSIBLE DATA LOSS IF DELETE OPERATION AND RUNSTATS/REORG RECLAIM ARE IN SAME TRANSACTION & THEN THE TRANSACTION IS ROLLED BACK

Subscribe to this APAR

By subscribing, you receive periodic emails alerting you to the status of the APAR, along with a link to the fix after it becomes available. You can track this item individually or track all items by product.

Notify me when this APAR changes.

Notify me when an APAR for this component changes.

 

APAR status

  • Closed as program error.

Error description

  • Customers using Db2 v11.5.5.0 or v11.5.5.1 may experience data
    loss and incorrect results when manually executing the RUNSTATS
    or REORG TABLE RECLAIM EXTENTS utility in the same transaction
    that includes delete operations on columnar tables and the
    transaction is later rolled back. The problem only occurs with
    delete and not update operations. When delete operations are
    rolled back, the values are no longer deleted and should be able
    to be accessed by subsequent operations.  Instead, due to a
    metadata problem, these values can no longer be accessed by
    subsequent operations after the table has been processed by the
    RUNSTATS or REORG TABLE RECLAIM EXTENTS utility.
    
    When the RUNSTATS or REORG TABLE RECLAIM EXTENTS utility was
    manually executed inside a transaction, the utility updated the
    table?s metadata based on the uncommitted changes in the
    transaction for the table processed.  The defect introduced in
    Db2 v11.5.5.0 and v11.5.5.1 was that some updated metadata
    changes did not get rolled back when the transaction rolled
    back. This incorrect metadata value was then used by queries to
    optimize scanning the table, but it caused wrong results to be
    returned with the queries missing the deleted values that were
    rolled back.  A subsequent REORG TABLE RECLAIM EXTENTS operation
    may also have freed the storage associated with these rows using
    the incorrect metadata.
    
    A sample scenario to illustrate this issue in 11.5.5.0 and
    11.5.5.1:
    
    -- Start Db2
    db2start
    
    -- Create database and tables and populate tables
    db2 create db test
    db2 connect to test
    db2 DROP TABLE T1
    db2 drop table T2
    db2 +c "create table T1(C1 INT) ORGANIZE BY ROW"
    db2 +c "create table T2(C1 INT) ORGANIZE BY column"
    db2 +c "load from intData.csv of del replace into T1"
    db2 +c "insert into T2 (SELECT * FROM T1 order by C1 fetch first
    1000 rows only)"
    
    -- Commit all changes
    db2 "commit"
    
    -- Start new transaction
    db2 +c "delete from T2"
    -- Execute runstats/reorg reclaim utility manually in same
    transaction
    db2 +c "runstats on table T2"
    
    -- Rollback after RUNSTATS utility is executed
    db2 "rollback"
    
    -- This query returns 2 rows only
    -- This is incorrect, since the transaction is rolled back
    -- 1000 rows should be returned.
    db2 "select count(*) from T2"
    1
     	-----------
                2
    
     1 record(s) selected.
    

Local fix

  • Customers should avoid running the RUNSTATS or REORG TABLE
    RECLAIM EXTENTS utility in the same transaction that has
    uncommitted delete operations where the changes could roll back
    after the RUNSTATS or REORG TABLE RECLAIM EXTENTS has completed.
    As a workaround, they can commit the delete operations before
    running the RUNSTATS or REORG TABLE RECLAIM EXTENTS utility.
    They can also run the delete operations in a separate
    transaction from the RUNSTATS or REORG TABLE RECLAIM EXTENTS
    utility.
    
    The fix for RUNSTATS or REORG TABLE RECLAIM EXTENTS to properly
    handle uncommitted data has been delivered into Db2 v11.5.6.0.
    It?s recommended that customers upgrade to v11.5.6.0 to avoid
    this problem.
    
    The probability of encountering this problem is quite low.
    Unfortunately, there is no way to recover the lost data when
    it?s encountered.
    
    Customers, whose workloads include the described problem
    scenario, need to perform a data integrity check. For tables in
    workload that have data loss, restore data from a good backup
    taken before the data loss (most likely taken before the v11.5.5
    upgrade) and then upgrade to v11.5.6 ASAP.
    
    NOTE: Customer should not run the workload described in problem
    scenario after restoring data from a good backup (most likely
    taken before the v11.5.5 upgrade) in 11.5.5 anymore and upgrade
    directly to 11.5.6.
    
    
    If the customer has had data loss due to this problem and did
    not properly restore from a good backup, then after upgrading to
    Db2 v11.5.6, Db2 may raise the following assertion when the
    RUNSTATS or REORG TABLE RECLAIM EXTENTS utility is run on tables
    that have data loss:
    
    
         AssertionFailedException: An assertion
    '((InsertRange::getRangeIndexFromTSN(desiredLWM) == insRangeIdx)
    && desiredLWM <= hwm)' failed. Additional info: desired LWM is
    not in range of current insert range.
           insRangeIdx = 0
           desiredLWM = 18446744073709551614 (0xfffffffffffffffe)
           hwm = 999 (0x00000000000003e7)
           firstNonDeletedTSN = 0 (0x0000000000000000)
           oldLWM = 998 (0x00000000000003e6)
           pageLSNLessThanCachedReadLSN  = true
           setOfTransIDsContainElements = false
           treatPendingRowsAsDeleted = true
           tupleState = 0
           isTableEnabledForLogicalSchemaBackup = false
           isReorgReclaimEnabledForLogicalSchemaBackup = true
           function trace id = TableReorger::analyzeTupleStatusPage
           Insert Range =         ----- Begin dump of
    ibm_cde::data::InsertRange object at 0x00007f99a6688980 -----
                mTable = 0x00007f99a66884e0
                mTable (summary) =
                    ----- Begin dump of Table object at
    0x00007f99a66884e0 -----
                    TableID: 0x01960009 => (pool=9:obj=406:flags=2)
    TableName: SAMPLE.T2
    
    This assertion indicates an inconsistency has been detected due
    to data loss cause by the problem described here.  This table
    will need to be reconstructed or restored from a backup.
    
    NOTE: Not all tables that have data loss will trigger this
    assertion; it's dependent on the state of the metadata of the
    tables.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * Db2 Version 11.5                                             *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to Db2 Version 11.5.6.0 (11.5 Mod 6 Fix Pack 0)      *
    ****************************************************************
    

Problem conclusion

  • Problem was first fixed in Db2 Version 11.5.6.0 (11.5 Mod 6 Fix
    Pack 0)
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT36818

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    B50

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2021-05-07

  • Closed date

    2021-06-18

  • Last modified date

    2021-08-06

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

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

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU053","label":"Cloud \u0026 Data Platform"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.5"}]

Document Information

Modified date:
07 August 2021