PI96417: LOOP IN DSNXOONY CAN OCCUR WITH A QUERY CONTAINING MULTIPLE INNER JOINS AND A LEFT OUTER JOIN

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• A loop in DSNXOONY may occur during prep and/or EXPLAIN of a
  statement containing multiple INNER joins and a LEFT OUTER join.
  This occurs when Db2 attempts to push a predicate down to one of
  the INNER join's
  
  Additional keywords and symptoms:
  LOOP SQLINNERJOIN SQLLEFTOUTERJOIN
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All Db2 12 for z/OS users of an SQL                          *
  * statement containing multiple joins                          *
  * and at least an outer join.                                  *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * Infinite loop may occur for an SQL                           *
  * statement satisfying all of the                              *
  * following conditions:                                        *
  * 1. It contains multiple join and                             *
  * at least an outer join.                                      *
  * 2. There is a join table which is a                          *
  * table expression whose column is                             *
  * not referenced.                                              *
  * 3. There is an always false predicate                        *
  * which is pruned.                                             *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  An example is shown below:
  
  CREATE TABLE T1 (C1 INT, C2 VARCHAR(10) NOT NULL,
                   C3 INT  NOT NULL);
  CREATE TABLE T2 (C1 INT, C2 VARCHAR(10) NOT NULL,
                   C3 INT  NOT NULL);
  CREATE TABLE T3 (C1 INT, C2 VARCHAR(10) NOT NULL,
                   C3 INT  NOT NULL);
  
  SELECT TX1.C1, TX1.C2, TX1.C4, T2.C3
   FROM (SELECT C1, C2, C3,
               (SELECT 1 FROM SYSIBM.SYSDUMMY1) AS C4
          FROM T2) AS TX1
        JOIN T2 ON (TX1.C1 = T2.C1)
        LEFT JOIN T3 ON (TX1.C1 = T3.C1)
    WHERE T2.C3 IS NULL OR T2.C3 > 100 AND 1 = 1;
  
  Db2 did not correctly process the aforementioned SQL
  and resulted in infinite loop.
  

Problem conclusion

• Db2 has been modified to correctly process the aforementioned
  SQL statement.
  
  Additional Keywords: SQLTABLEEXPR SQLLEFTJOIN SQLLEFT
                       SQLRIGHTJOIN SQLRIGHT SQLOUTERJOIN
                       SQLFULLJOIN SQLOUTER SQLFULL SQLJOIN
                       SQLISNULL SQLISNOTNULL
  

Temporary fix

Comments

APAR Information

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

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

  UI55965

Modules/Macros

• DSNXOONY
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI55965

     UP18/06/05 P F806

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.