PI41168: ABEND04E RC00E70005 DSNXRBND M101

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• ABEND04E RC00E70005 DSNXRBND M101 caused by query which
  predicate is on UNIQUE index and the group by contains same
  columns, one must have condition is there should be a COALESCE
  or VALUE function on one or several index column.
  
  For example:
  CREATE UNIQUE INDEX IX1 ON USER.TESTTB
    (C1 , C2, C3) ;
  SELECT C1, COALESCE (C3, C2)
    FROM   USER.TESTTB
      WHERE C1='2015-01-01-00.00.00.000000'
      GROUP BY C1, COALESCE (C3, C2) ;
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 10 and 11 for z/OS users of query    *
  *                 statements that contain an equal predicate   *
  *                 with a column (having a unique index) that   *
  *                 is also included in a group by clause along  *
  *                 with a scalar function.                      *
  ****************************************************************
  * PROBLEM DESCRIPTION: An ABEND04E RC00E70005 can occur at     *
  *                      location DSNXGRDS DSNXRBND M101 for an  *
  *                      SQL statement that contains an equal    *
  *                      predicate and group by clause that      *
  *                      includes the same column and a scalar   *
  *                      function.                               *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  An ABEND04E RC00E70005 can occur at location DSNXGRDS DSNXRBND
  M101 for a query that contains an equal predicate on a column
  that has a unique index which is included in a GROUP BY clause
  along with a COALESCE function.
  
  This problem occurs because DB2 doesn't handle an internal
  format for a data type variable properly.
  
  The following query illustrates an example of failing case.
  
  1. Define a table and an index on the table.
  
    CREATE TABLE USER.TESTTB
           (C1  TIMESTAMP NOT NULL
           ,C2  VARCHAR(255)
           ,C3  VARCHAR(255) ) ;
  
    CREATE UNIQUE INDEX USER.XDMP1
           ON USER.TESTTB  (C1 ASC) ;
  
  
  2. Insert a row into the table.
  
    INSERT INTO USER.TESTTB
           VALUES('2015-01-01-00.00.00.000000',
           NULL,NULL);
  
  
  3. Process the following query statement.
  
    SELECT C1, COALESCE (C3, C2)
      FROM   USER.TESTTB
      WHERE C1='2015-01-01-00.00.00.000000'
      GROUP BY C1, COALESCE (C3, C2) ;
  
  
  Please note that the above mentioned abend can occur during the
  execution of this query statement. Also, the COALESCE function
  can be replaced by some other scalar function which returns a
  value.
  

Problem conclusion

• DB2 has been modified to process the internal variable correctly
  which will avoid the abend.
  
  Additional Keywords: SQLGROUPBY SQLCOALESCE SQLSCALAR ABEND04E
  

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:

  UI33097 UI33098

Modules/Macros

• DSNXGSGP
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UI33097

     UP15/12/04 P F512

• RB10 PSY UI33098

     UP15/12/04 P F512

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.