DB2 - Problem description
Problem IT18836 | Status: Closed |
UPDATE STATEMENT MAY TRAP WHEN USING A NOT-IN SUBQUERY | |
product: | |
DB2 FOR LUW / DB2FORLUW / A50 - DB2 | |
Problem description: | |
DB2 may abend in runtime if the following conditions are satisfied: 1) This is an update query 2) There is an IN predicate in where clause 3) IN subquery has a NOT IN/NOT EXISTS predicate 4) Column used in NOT IN/ NOT EXISTS subquery has index on it 5) Regsitry variable DB2_UNION_OPTIMIZATION is set to ENABLE_NESQ_PD_THRU_UA Example: create table t (tc1 int, tc2 int); create table t1 (t1c1 int); create table t2(t2c1 int primary key not null); insert into t values (1,1),(2,2); insert into t1 values (1),(2); insert into t2 values (3),(4); update t set tc1=1 where tc2 in (select t1c1 From t1 where t1c1 not in (select t2c1 From t2)) ; Stack of the failure will show the similar to the following: -----FUNC-ADDR---- ------FUNCTION + OFFSET------ 0x00007FFFEFFDB9DA ossDumpStackTraceInternal(unsigned long, OSSTrapFile&, int, siginfo*, void*, unsigned long, unsigned long) + 0x020a 0x00007FFFEFFDB76B ossDumpStackTraceV98 + 0x002b 0x00007FFFEFFD6693 OSSTrapFile::dumpEx(unsigned long, int, siginfo*, void*, unsigned long) + 0x0103 0x00007FFFF390DC07 sqlo_trce + 0x0407 0x00007FFFF395A4AD sqloEDUCodeTrapHandler + 0x027d 0x00007FFFF7BCF710 address: 0x00007FFFF7BCF710 ; dladdress: 0x00007FFFF7BC0000 ; offset in lib: 0x000000000000F710 ; 0x00007FFFF48025EF sqlriupd(sqlrr_cb*) + 0x010f 0x00007FFFF246F6D1 sqlriExecThreadFunc(sqlrr_cb*, sqlri_opparm*, long*) + 0x0031 0x00007FFFF48007A4 sqlriSectInvoke(sqlrr_cb*, sqlri_opparm*) + 0x0324 0x00007FFFF235DDBD sqlrr_execute_immediate(sqlrr_cb*, int) + 0x04fd 0x00007FFFF2351D44 sqlrr_execimmd(db2UCinterface*, db2UCprepareInfo*) + 0x0134 0x00007FFFF1998FAB sqljs_ddm_excsqlimm(db2UCinterface*, sqljDDMObject*) + 0x05ab 0x00007FFFF4677D66 sqljsParseRdbAccessed(sqljsDrdaAsCb*, sqljDDMObject*, db2UCinterface*) + 0x03a6 0x00007FFFF4678147 sqljsParse(sqljsDrdaAsCb*, db2UCinterface*, sqeAgent*, bool) + 0x0377 0x00007FFFF197C4C1 address: 0x00007FFFF197C4C1 ; dladdress: 0x00007FFFF04D1000 ; offset in lib: 0x00000000014AB4C1 ; 0x00007FFFF197B2CF address: 0x00007FFFF197B2CF ; dladdress: 0x00007FFFF04D1000 ; offset in lib: 0x00000000014AA2CF ; 0x00007FFFF19790A3 address: 0x00007FFFF19790A3 ; dladdress: 0x00007FFFF04D1000 ; offset in lib: 0x00000000014A80A3 ; 0x00007FFFF1978E33 sqljsDrdaAsDriver(SQLCC_INITSTRUCT_T*) + 0x00f3 0x00007FFFF17350A3 sqeAgent::RunEDU() + 0x0823 0x00007FFFF27B9E63 sqzEDUObj::EDUDriver() + 0x00f3 0x00007FFFF27B9D69 sqlzRunEDU(char*, unsigned int) + 0x0009 0x00007FFFF2225431 sqloEDUEntry + 0x02a1 0x00007FFFF7BC79D1 address: 0x00007FFFF7BC79D1 ; dladdress: 0x00007FFFF7BC0000 ; offset in lib: 0x00000000000079D1 ; 0x00007FFFEF3578FD clone + 0x006d | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * All DB2 LUW users * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to DB2 LUW 10.5 Fix Pack 10 or later * **************************************************************** | |
Local Fix: | |
1) Use optimization level 1 using: i) set query optimization 1 or ii) /**/; or 2) unset ENABLE_NESQ_PD_THRU_UA using i)db2set DB2_UNION_OPTIMIZATION=ENABLE_NESQ_PD_THRU_UA=NO or ii) /* */; | |
Solution | |
Workaround | |
not known / see Local fix | |
BUG-Tracking | |
forerunner : IT18834 follow-up : | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 16.01.2017 12.07.2018 12.07.2018 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |