DB2 - Problem description
Problem IT38870 | Status: Closed |
RARE WRONG RESULT IN CDE INDEX CASE WHEN WITH UR IS USED | |
product: | |
DB2 FOR LUW / DB2FORLUW / B10 - DB2 | |
Problem description: | |
Wrong result (lost row) is possible in a rare plan: - CDE Table access. - WITH UR is used - CDE Table has unique index on the column which is used in a predicate - Plan picks a different index to use and applies predicate after rows are fetched from base table. | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * all * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to 11.1.4.7 * **************************************************************** | |
Local Fix: | |
Make sure good plan is used: unique index on the column in the predicate is picked OR turn off CDE indexes in the plans: db2set -im DB2_REDUCED_OPTIMIZATION="COL_IXSCAN_ALL_V1133 OFF" If the registry variable is already set to another value, append this new setting to the end of the existing setting, separated by a comma (no spaces). static packages will need to be re-created package cache needs to be flushed. | |
Solution | |
Workaround | |
**************************************************************** * USERS AFFECTED: * * all * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to 11.1.4.7 * **************************************************************** | |
Comment | |
Upgrade to 11.1.4.7 | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 28.10.2021 16.04.2022 20.04.2022 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |