IBM Support

IT18837: UPDATE STATEMENT MAY TRAP WHEN USING A NOT-IN SUBQUERY

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error 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
    6) DB2_ANTIJOIN is set to EXTEND;
    
    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:
    <StackTrace>
    -----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
    </StackTrace>
    

Local fix

  • 1) Use optimization level 1 using:
      i) set query optimization 1
      or
      ii) <query> /*<OPTGUIDELINES><QRYOPT
    VALUE='1'/></OPTGUIDELINES>*/;
    or
    2) unset ENABLE_NESQ_PD_THRU_UA using
      i)db2set DB2_UNION_OPTIMIZATION=ENABLE_NESQ_PD_THRU_UA=NO
      or
      ii) <query>/*<OPTGUIDELINES><REGISTRY>
    				<OPTION NAME='DB2_UNION_OPTIMIZATION'
    VALUE='ENABLE_NESQ_PD_THRU_UA=NO'/>
    			   </REGISTRY></OPTGUIDELINES>*/;
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to Db2 11.1 Mod 4 Fixpack 4 or higher                *
    ****************************************************************
    

Problem conclusion

  • First fixed in Db2 11.1 Mod 4 Fixpack 4
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT18837

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    B10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2017-01-16

  • Closed date

    2018-11-27

  • Last modified date

    2018-11-27

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

    IT18834

  • 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

[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.1","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
30 June 2020