IBM Support

IT22221: WRONG RESULTS OR SQL901N MAY OCCUR WHEN EXECUTING A SQL STATEMENT CONTAINING TWO OR MORE NOT IN OR NOT EXISTS PREDICATES

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • You might encounter wrong results or SQL901N errors when
    executing a SQL
    statement containing two or more NOT IN (or NOT EXISTS)
    predicates under the following conditions, where
    PRD_NOTIN_A and PRD_NOTIN_B are two
    eligible NOT IN predicates:
    
    1. The columns on the left hand side of PRD_NOTIN_A and
    PRD_NOTIN_B are from different tables;
    2. The table containing the column on the left hand side of
    PRD_NOTIN_A also contains column(s) used in another predicate
        (Let's call it PRD_NON_NOT_IN);
    3. PRD_NON_NOT_IN has a sub-query that contains a correlated
    column with the column on the left hand side of PRD_NOTIN_B.
    
    Here is an example of a failing query:
    
    SELECT a1
    FROM   a
    WHERE  a.a2 NOT IN (SELECT b1
                        FROM   b)
    //PRD_NOTIN_B
           AND EXISTS (SELECT c1
                       FROM   c
                       WHERE  c.c1 = (SELECT d1
                                      FROM   d
                                      WHERE  d.d2 = a.a1)
    //PRD_NON_NOT_IN
                              AND c.c1 NOT IN (SELECT e1
                                               FROM   e));
    //PRD_NOTIN_A
    

Local fix

  • When encountering this problem, you can apply the following
    registry setting using an optimization guideline on the affected
    queries as a workaround:
    <query>
    /*
         <OPTGUIDELINES>
          <REGISTRY><OPTION NAME='DB2_ANTIJOIN' VALUE='NO'/>
    </REGISTRY>
         </OPTGUIDELINES>
    */;
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Please, See Error Description and install this fix.          *
    ****************************************************************
    

Problem conclusion

  • Please, See Error Description and install this fix.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT22221

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    B10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2017-08-31

  • Closed date

    2018-03-16

  • Last modified date

    2018-03-16

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

    IT20883

  • 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

  • RB10 PSY

       UP

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

Document Information

Modified date:
16 March 2018