PI92895: ABEND04E RC00C90101 DSNIDM DSNIWNRF:5033 CAN OCCUR ON BEHALF OF A COMPLEX JOIN PROCESSING

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• An ABEND04E RC00C90101 DSNIDM DSNIWNRF:5033 can occur for a
  query that involves complex join processing.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All Db2 11 for z/OS users of deep level join *
  *                 queries that could qualify to use in-memory  *
  *                 workfiles.                                   *
  ****************************************************************
  * PROBLEM DESCRIPTION: An unexpected ABEND04E RC00C90101 at    *
  *                      DSNIDM DSNIWNRF ERQUAL5033 can occur    *
  *                      for a complex join query if in-memory   *
  *                      workfiles are used when fetch next      *
  *                      processing incorrectly precedes fetch   *
  *                      first processing.                       *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  An unexpected ABEND04E RC00C90101 at DSNIDM DSNIWNRF ERQUAL5033
  can occur for a complex join query if in-memory workfiles are
  used and fetch next processing incorrectly precedes fetch first
  processing.
  
  
  Examples of a complex or deep level join query are as follows.
  
  Example 1.  Deep level JOIN query diagram with table expressions
  (TE) where Lj means left join, Ij means inner join, Fj means
  full join, and Rj means right join.
  
                     Lj
                    /  \
                   Ij  TE1
                  /  \
                 Lj   TE2
                /  \
               Fj   TE3(Ij)
              /  \
             Lj   TE4
            /  \
          Ij    TE5(Rj)
          /  \
         Ij   TE6
       /   \
      T12  T11    (Tables T12 and T11)
  
  
  Example 2. Deep level JOIN query with table expressions.
  
    SELECT COUNT(*)
    FROM
    TABLE(SELECT T1.INTCOL, T2.SMINTCOL, T1.DEC62COL,
          T2.CHARCOL, T1.FLOATCOL
          FROM TBKB0100.mtypes_1  T1
          LEFT JOIN TBKB0100.mtypes_2  T2
          ON T1.charcol = T2.vcharcol
          WHERE  T1.intcol = T2.dec62col) AS EX1
  
    LEFT JOIN
  
    TABLE(SELECT T4.INTCOL, T3.DEC62COL, T3.CHARCOL
          FROM TBKB0100.mtypes_6  T3
          JOIN TBKB0100.MANYTYPES_NOTNULL  T4
          ON  T3.dec62col = T4.dec62col
          WHERE LENGTH(T3.CHARCOL) = 7
          AND T4.CHARCOL = EX1.CHARCOL) AS EX2
    ON EX1.INTCOL = EX2.INTCOL
  
    JOIN
  
    TABLE(SELECT INTCOL, DEC62COL FROM TBKB0100.mtypes_1
          WHERE INTCOL = EX2.INTCOL) AS EX3
    ON EX1.DEC62COL = EX3.DEC62COL
  
    LEFT JOIN
  
    TABLE(SELECT T5.FLOATCOL, T5.INTCOL FROM TBKB0100.mtypes_2  T5
          LEFT JOIN TBKB0100.mtypes_1  T6
          ON  T5.FLOATCOL = T6.FLOATCOL
          WHERE T6.FLOATCOL <> '1.700000000000000E+01'
          AND T6.INTCOL = EX1.INTCOL ) AS EX4
    ON EX1.INTCOL = EX4.INTCOL
  
    FULL JOIN
  
    TABLE(SELECT T7.FLOATCOL, T7.INTCOL
          FROM TBKB0100.MANYTYPES_NOTNULL  T7
          RIGHT JOIN TBKB0100.mtypes_6  T8
          ON  T7.FLOATCOL = T8.FLOATCOL ) EX5
    ON EX1.FLOATCOL = EX5.FLOATCOL
  
    LEFT JOIN
  
    TABLE(SELECT T9.CHARCOL, T9.INTCOL, T10.FLOATCOL
          FROM TBKB0100.mtypes_1  T9
          JOIN TBKB0100.mtypes_2  T10
          ON  T9.SMINTCOL = T10.SMINTCOL
          WHERE T9.FLOATCOL=EX5.FLOATCOL) AS EX6
    ON EX1.CHARCOL = EX6.CHARCOL,
       TBKB0100.MANYTYPES_NOTNULL  T11, TBKB0100.MANYTYPES  T12
    WHERE ( ( T11.DEC72COL >= 20.00 OR
              T12.SMINTCOL BETWEEN 1 AND 10 ) AND
              EX6.FLOATCOL IN ( 2.000000000000000E+01,
                                1.000000000000000E+01,
                                3.000000000000000E+01 )
              OR T11.INTCOL = 20 ) ;
  

Problem conclusion

• The code in Db2 is modified to ensure that the fetch next
  processing will follow the fetch first processing to prevent the
  possible aforementioned abend.
  
  Additional Keywords: SQLJOIN SQLFETCH
  

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:

  UI54925

Modules/Macros

•    DSNXRFF
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI54925

     UP18/04/18 P F804

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.