PM79251: LOOP IN DSNXOEXB OFFSET28B0 CAUSED BY IMPLICIT CAST

A fix is available

Subscribe

You can track all active APARs for this component.

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 number

    PM79251

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2012-12-14

  • Closed date

    2013-02-05

  • Last modified date

    2013-03-04

  • 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.



Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

DB2 for z/OS

Software version:

A10

Reference #:

PM79251

Modified date:

2013-03-04

Translate my page

Machine Translation

Content navigation