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

A fix is available

Subscribe

You can track all active APARs for this component.

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 number

    PM78249

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

  • Closed date

    2013-01-18

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

    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.



Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

DB2 for z/OS

Software version:

A10

Reference #:

PM78249

Modified date:

2013-03-04

Translate my page

Machine Translation

Content navigation