PM79251: LOOP IN DSNXOEXB OFFSET28B0 CAUSED BY IMPLICIT CAST

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• loop in DSNXOEXB +28B0 08/31/11UK71486
  

Local fix

• in some cases re-writing the query is possible, to avoid
  implicit casting
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 10 for z/OS users of implicit CAST.  *
  ****************************************************************
  * PROBLEM DESCRIPTION: Infinite loop could occur when an SQL   *
  *                      statement satisfies all of the          *
  *                      following conditions:                   *
  *                      1.There is a table expression which     *
  *                        will be merged,                       *
  *                      2.An implicit CAST or timestamp         *
  *                        with time zone data type column is    *
  *                        involved in the table expression,     *
  *                      3.The outer query block of the table    *
  *                        expression contains a predicate       *
  *                        referencing the implicit CAST         *
  *                        or timestamp time zone type column    *
  *                        expression in the table expression.   *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  Infinite loop could occur when an SQL statement satisfies all
  of the following conditions:
  1.There is a table expression which will be merged,
  2.An implicit CAST or timestamp with time zone data type
    column is involved in the table expression,
  3.The outer query block of the table expression contains a
    predicate referencing the implicit CAST or timestamp with
    time zone type column expression in the table expression.
  
  The following examples could recreate the problem,
  CREATE TABLE T1 (T1C1 CHAR(1), T1C2 INTEGER, T1C3 SMALLINT);
  CREATE TABLE T2 (T2C1 INTEGER, T2C2 INTEGER,
                   T2C3 TIMESTAMP WITH TIMEZONE);
  1. Implicit Cast example:
  SELECT  *
  FROM (SELECT CASE WHEN S.SC1 = 1 THEN X.XC1 ELSE T2C2 END CX
        FROM (SELECT 100 XC1 FROM SYSIBM.SYSDUMMY1
              FETCH FIRST 1 ROWS ONLY) X, T2
             LEFT OUTER JOIN (
             SELECT T1C2 ,MAX(T1C1) SC1 FROM T1
             WHERE T1C3 > 1200 GROUP BY T1C2 ) S
             ON S.T1C2 = T2C1
        WHERE  T2C2 =100) C
   WHERE CX IS NULL WITH UR
  CX is a column with case expression, IMPLICIT CAST is added in
  CASE WHEN predicate, DB2 doesn't handle the IMPLICIT CAST well
  which causes the infinite loop.
  
  2. Timestamp with time zone example:
  
  SELECT  *
  FROM (SELECT CASE WHEN S.SC1 = T2C3 THEN X.XC1 ELSE T2C2 END CX
        FROM (SELECT 100 XC1 FROM SYSIBM.SYSDUMMY1
              FETCH FIRST 1 ROWS ONLY) X, T2
             LEFT OUTER JOIN (
             SELECT T1C2 ,MAX(T1C1) SC1 FROM T1
             WHERE T1C3 > 1200 GROUP BY T1C2 ) S
             ON S.T1C2 = T2C1
        WHERE  T2C2 =100) C
   WHERE CX IS NULL WITH UR
  CX is a column with case expression, T2C3 is timestamp with
  time zone type, SC1 is string type, DB2 doesn't handle the
  timestamp with time zone column compares with string type
  column correctly which causes the infinite loop.
  

Problem conclusion

• DB2 is updated to handle the implicit CAST and timestamp with
  time zone column correctly.
  Additional Keywords: SQLIMPLICITCAST SQLOUTERJOIN SQLTIMESTAMP
                       SQLTIMEZONE LOOPDSNXOEXB
  

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:

  UK91433

Modules/Macros

• DSNXOBFA DSNXOBFC DSNXOBFF
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK91433

     UP13/02/22 P F302

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.