PI16121: LOOP MAY OCCUR FOR A QUERY WITH NOT IN SUBQUERY ON V11

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• LOOP may accour for a query with NOT IN SUBQUERY on V11.
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 11 for z/OS users of queries which   *
  *                 contains an IN or NOT IN subquery and the    *
  *                 subquery is using an ascending order index.  *
  ****************************************************************
  * PROBLEM DESCRIPTION: An infinite loop can occur when         *
  *                      running a query that contains an        *
  *                      IN or NOT IN subquery and the subquery  *
  *                      is using an ascending order index.      *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  An infinite loop can occur when running a query that contains
  an IN or NOT IN subquery and the subquery is using an ascending
  order index.
  
  The following simple example illustrates a failing case.
  
   CREATE TABLE SUPPLIER (
         S_SUPPKEY INTEGER NOT NULL
        ,S_NAME CHAR(25)  NOT NULL
        ,S_PHONE CHAR(15) NOT NULL);
  
   CREATE TABLE CUSTOMER(
         C_CUSTKEY INTEGER NOT NULL
        ,C_NAME VARCHAR(25) NOT NULL
        ,C_PHONE CHAR(15) NOT NULL);
  
   CREATE  INDEX CNAME
     ON  CUSTOMER(C_NAME ASC);
  
   SELECT *
   FROM  SUPPLIER
   WHERE S_SUPPKEY  < 1890 AND
         S_NAME NOT IN
           (SELECT C_NAME
            FROM CUSTOMER
            WHERE C_CUSTKEY < 450000);
  

Problem conclusion

• The code in DB2 is modified to process the IN or
  NOT IN subquery properly which will prevent an
  infinite loop.
  
  Additional Keywords: SQLSUBQUERY SQLINSUBQ SQLLOOP
  

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:

  UI17873

Modules/Macros

• DSNXOML
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI17873

     UP14/05/28 P F405 ½

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.