PM78249: ABEND0C7 AT DSNXRDEC OFFSET 59BC WHEN QUERY CONTAINS UNION ALL PLUS BOTH ABS AND SUM FUNCTION

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Abend0c7 at dsnxrdec offset 59bc may happen when query contains
  three conditions: union all, abs function and sum function.
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 10 for z/OS users of an SQL          *
  *                 statement that contains a table expression   *
  *                 with UNION ALL and a HAVING predicate with   *
  *                 scalar function(ABS, SIGN, CEILING or ROUND) *
  *                 containing a SUM function on a DECIMAL       *
  *                 column of the table expression.              *
  ****************************************************************
  * PROBLEM DESCRIPTION: ABEND0C7 RC00000000 at the location     *
  *                      DSNXGRDS DSNXRDEC OFFSET59BC could      *
  *                      happen for an SQL statement that        *
  *                      satisfies all of the following          *
  *                      conditions:                             *
  *                                                              *
  *                      1. It contains a table expression       *
  *                         with UNION ALL.                      *
  *                      2. It contains a HAVING predicate       *
  *                         with scalar function ABS, SIGN,      *
  *                         CEILING or ROUND.                    *
  *                      3. The scalar function contains a       *
  *                         SUM function in the input            *
  *                         expression.                          *
  *                      4. The SUM function is on a DECIMAL     *
  *                         column of the table expression.      *
  *                      5. The DECARTH DECP value is DEC31      *
  *                         or 31, and the DECIMAL column is     *
  *                         with precision 15.                   *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  ABEND0C7 RC00000000 at the location DSNXGRDS DSNXRDEC
  OFFSET59BC could happen for an SQL statement that satisfies all
  of the following conditions:
  
  1. It contains a table expression with UNION ALL.
  2. It contains a HAVING predicate with scalar function ABS,
     SIGN, CEILING or ROUND.
  3. The scalar function contains a SUM function in the input
     expression.
  4. The SUM function is on a DECIMAL column of the table
     expression.
  5. The DECARTH DECP value is DEC31 or 31, and the DECIMAL column
     is with precision 15.
  
  An example is shown below:
  
  CREATE TABLE T1 (
     C1 DECIMAL(15, 0)
  );
  CREATE TABLE T2 (
     C1 DECIMAL(15, 0)
  );
  
  INSERT INTO T1 VALUES(11.1);
  INSERT INTO T1 VALUES(12.1);
  INSERT INTO T2 VALUES(21.1);
  INSERT INTO T2 VALUES(22.1);
  
  SELECT ABS(SUM(C1))
  FROM (SELECT C1
          FROM T1
        UNION ALL
        SELECT C1
          FROM T2
        ) TX(C1)
  HAVING ABS(SUM(TX.C1)) > 0;
  
  DB2 did not correctly process the aforementioned SQL statement,
  which caused the ABEND.
  

Problem conclusion

• DB2 has been modified to correctly process the aforementioned
  SQL statement, so there will be no ABEND.
  
  Additional keywords: SQLTABLEEXPR SQLUNIONALL SQLDECFLOAT SQLSUM
  

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:

  UK91019

Modules/Macros

•    DSNXOEXE
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK91019

     UP13/02/02 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.