IBM Support

IC70488: SUM DOUBLE COLUMN WITH VALUES THAT ARE CLOSE TO ZERO MAY RESULT IN INTERMEDIATE VALUES SMALLER THAN THE LIMIT RETURNING SQL0802N

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • SUM of a DOUBLE column might return SQL0802N if an intermediate
    floating point calculation returns a value that is
    outside the range for DOUBLE, even though a reasonable result is
    expected.
    
    The following example could reasonably return a SUM of 0, but
    instead
    generates an error.
    
    db2 connect to sample
    db2 "drop table mbj"
    db2 "CREATE TABLE mbj(value INTEGER,number DOUBLE)"
    db2 "INSERT INTO mbj VALUES (1, -1.99000000000000E-300)"
    db2 "INSERT INTO mbj VALUES (1, -1.99000000000000E-300)"
    db2 "INSERT INTO mbj VALUES (1, -1.99000000000000E-300)"
    db2 "INSERT INTO mbj VALUES (1, -1.99000000000000E-300)"
    db2 "INSERT INTO mbj VALUES (1, -1.99000000000000E-300)"
    db2 "INSERT INTO mbj VALUES (1, -1.99000000000000E-300)"
    db2 "INSERT INTO mbj VALUES (1, -1.99000000000000E-300)"
    db2 "INSERT INTO mbj VALUES (1, -1.99000000000000E-300)"
    db2 "INSERT INTO mbj VALUES (1, -1.99000000000000E-300)"
    db2 "INSERT INTO mbj VALUES (1, -1.99000000000000E-300)"
    db2 "INSERT INTO mbj VALUES (1, 1.99000000000000E-300)"
    db2 "INSERT INTO mbj VALUES (1, 1.99000000000000E-300)"
    db2 "INSERT INTO mbj VALUES (1, 1.99000000000000E-300)"
    db2 "INSERT INTO mbj VALUES (1, 3.98000000000000E-300)"
    db2 "INSERT INTO mbj VALUES (1, 1.99000000000000E-300)"
    db2 "INSERT INTO mbj VALUES (1, 1.99000000000000E-300)"
    db2 "INSERT INTO mbj VALUES (1, 1.99000000000000E-300)"
    db2 "INSERT INTO mbj VALUES (1, 1.99000000000000E-300)"
    db2 "INSERT INTO mbj VALUES (1, 1.99000000000000E-300)"
    db2 "SELECT SUM(number) FROM mbj"
    
    Since the sum of the negative rows appears to be equivalent to
    the sum
    of the positive rows, a result of zero would be expected.
    
    >db2 "SELECT SUM(number) FROM mbj"
    
    1
    ------------------------
    SQL0802N  Arithmetic overflow or other arithmetic exception
    occurred.
    SQLSTATE=22003
    
    This error might not occur with similar examples against other
    database Servers.
    This behavior is working as designed since double is not a
    precise data type, but we will accommodate requirements from
    applications like SAP and return 0 instead of SQL0802N in this
    scenario when SAP is used.
    
    Workaround:
    
    Cast column to DECFLOAT or DECIMAL:
    db2 "select SUM( DECFLOAT(number)) from mbj"
    or
    Use DECFLOAT or DECIMAL data type instead.
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All Platforms                                                *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * SUM DOUBLE COLUMN WITH VALUES THAT ARE CLOSE TO ZERO MAY     *
    * RESULT                                                       *
    * IN INTERMEDIATE VALUES SMALLER THAN THE LIMIT RETURNING      *
    * SQL0802N                                                     *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to v9.7 Fixpack 4 or higher.                         *
    ****************************************************************
    

Problem conclusion

  • First fixed in DB2 v9.7 Fixpack 4 or higher.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC70488

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2010-08-11

  • Closed date

    2011-05-09

  • Last modified date

    2011-05-09

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

    IC70675

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC70488

Modified date: 09 May 2011