PH01309: 04E-00E70005,LOG=DSNXGRDS.DSNXOMTX:M140 WHEN SELECT EXECUTED

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• ABEND 04E-00E70005,LOG=DSNXGRDS.DSNXOMTX:M140 WHEN SELECT
  IS EXECUTED.
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All Db2 11 and 12 for z/OS users of an SQL   *
  *                 statement containing an EXISTS predicate in  *
  *                 the HAVING clause or in the WHERE clause     *
  *                 along with a table expression, view or SQL   *
  *                 table function with GROUP BY clause.         *
  ****************************************************************
  * PROBLEM DESCRIPTION: ABEND04E RC00E70005 at the location     *
  *                      DSNXGRDS DSNXOMTX M140 may happen for   *
  *                      an SQL statement containing an EXISTS   *
  *                      predicate in the HAVING clause or in    *
  *                      the WHERE clause along with a table     *
  *                      expression, view or SQL table function  *
  *                      with GROUP BY clause.                   *
  ****************************************************************
  * RECOMMENDATION: Apply corrective PTF when available.         *
  ****************************************************************
  An example is shown below:
  
  CREATE TABLE T1(
    C1 INTEGER
  , C2 VARCHAR(10)
  )
  ;
  
  SELECT C1, COUNT(C2)
  FROM T1
  GROUP BY C1
  HAVING (COUNT(C2) > 8
          AND EXISTS(SELECT 1
                    FROM SYSIBM.SYSDUMMY1
                    WHERE COUNT(C2) > 8)
         )
         AND(COUNT(C2) > 7
             AND COUNT(C2) < 88
             AND C1 > 2
         )
  ;
  
  Db2 incorrectly processed the EXISTS predicate when moving some
  predicates from HAVING to WHERE clause thus caused the abend.
  

Problem conclusion

• Db2 has been modified to correctly process the aforementioned
  SQL statement.
  
  Additional keywords: SQLHAVING SQLWHERE SQLEXISTS SQLGROUPBY
                       SQLTABLEEXP SQLVIEW
  

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:

  UI58482 UI58485

Modules/Macros

•    DSNXOMTX
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI58485

     UP18/09/29 P F809

• RC10 PSY UI58482

     UP18/09/28 P F809

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.