PI24602: INCORROUT WHEN SQLDELETE WITH SQLWHERE NOT SQLINSUBQ WHICH ALSO CONTAINS NOT SQLINSUBQ

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Incorrout issues occurs with a DELETE statement when the DELETE
  has a NOT IN subquery and the subquery has nested NOT IN
  subquery too
  
  SAMPLE:
  DDL:
   CREATE TABLE REMARKS (ID INTEGER NOT NULL, REPID INTEGER NOT
  NULL);
   CREATE TABLE REPORT (ID INTEGER NOT NULL, MSGID INTEGER);
   CREATE TABLE MESSAGE (ID INTEGER NOT NULL);
   INSERT INTO REMARKS VALUES (2, 2);
   INSERT INTO REPORT VALUES (2, NULL);
   INSERT INTO MESSAGE VALUE(5);
   COMMIT:
  
  SQL:
   DELETE FROM REMARKS WHERE REPID NOT IN
       (SELECT ID FROM REPORT WHERE MSGID NOT IN
            (SELECT ID FROM MESSAGE));
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 11 for z/OS users of sparse index    *
  *                 and IN predicates                            *
  ****************************************************************
  * PROBLEM DESCRIPTION: DB2 may incorrectly return a row when   *
  *                      comparing the null value to the result  *
  *                      of an IN subquery.  The problem occurs  *
  *                      when a sparse index is used for the     *
  *                      subquery.                               *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  DB2 may incorrectly return a row when comparing the null value
  to the result of an IN subquery.  The problem occurs when a
  sparse index is used for the subquery. The following is an
  example of the incorrect output.
  
   CREATE TABLE REMARKS (ID INTEGER NOT NULL,
                        REPID INTEGER NOT NULL);
   CREATE TABLE REPORT (ID INTEGER NOT NULL,
                        MSGID INTEGER);
   CREATE TABLE MESSAGE (ID INTEGER NOT NULL);
   CREATE INDEX IX1 ON MESSAGE(ID);
   INSERT INTO REMARKS VALUES(2,2);
   INSERT INTO REPORT VALUES(2,NULL);
   INSERT INTO MESSAGE VALUES(5);
  
   DELETE FROM REMARKS
   WHERE REPID NOT IN
     (SELECT ID
      FROM REPORT
      WHERE MSGID NOT IN (SELECT ID FROM MESSAGE));
  

Problem conclusion

• DB2 has been changed to correctly compare the NULL value to the
  sparse index.
  
  Additional keywords : SQLIN SQLNOTIN SQLSPARSEINDEX
                        SQLINCORR INCORROUT SQLINCORROUT
                        DB2INCORR/K
  

Temporary fix

• *********
  * HIPER *
  *********
  

Comments

APAR Information

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

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

  UI21887

Modules/Macros

•    DSNXRSC2
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI21887

     UP14/10/16 P F410 Ž

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.