DB2 FOR LUW / DB2FORLUW / B50 - DB2

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.

****************************************************************
* 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)      *
****************************************************************

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

****************************************************************
* 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 was first fixed in Db2 Version 11.5.6.0 (11.5 Mod 6 Fix
Pack 0)

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

07.05.2021
18.06.2021
06.08.2021