Latest versionsfixlist
11.1.0.6 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Have problems? - contact us.
Register for free anmeldung-x26
Contact form kontakt-x26
Question in the chat LiveZilla Live Help

DB2 - Problem description

Problem IC70675 Status: Closed

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

product:
DB2 FOR LUW / DB2FORLUW / 950 - DB2
Problem 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.
Problem Summary:
**************************************************************** 
* USERS AFFECTED:                                              * 
* All Platforms                                                * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* SUM of a DOUBLE column might return SQL0802N if              * 
* anintermediatefloating point calculation returns a value     * 
* that isoutside the range for DOUBLE, even though a           * 
* reasonableresult isexpected.The following example could      * 
* reasonably return a SUM of 0,butinsteadgenerates an          * 
* error.db2 connect to sampledb2 "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    * 
* equivalenttothe sumof the positive rows, a result of zero    * 
* would be expected.>db2 "SELECT SUM(number) FROM              * 
* mbj"1------------------------SQL0802N  Arithmetic overflow   * 
* or other arithmetic exceptionoccurred.SQLSTATE=22003This     * 
* error might not occur with similar examples                  * 
* againstotherdatabase Servers.This behavior is working as     * 
* designed since double is not aprecise data type, but we will * 
* accommodate requirements fromapplications like SAP and       * 
* return 0 instead of SQL0802N inthisscenario when SAP is      * 
* used.Workaround:Cast column to DECFLOAT or DECIMAL:db2       * 
* "select SUM( DECFLOAT(number)) from mbj"orUse DECFLOAT or    * 
* DECIMAL data type instead.                                   * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 v9.5 Fixpak 7 or higher                       * 
****************************************************************
Local Fix:
available fix packs:
DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows

Solution
First Fixed in DB2 v9.5 Fixpak 7
Workaround
not known / see Local fix
BUG-Tracking
forerunner  : APAR is sysrouted TO one or more of the following: IC71283 
follow-up : 
Timestamps
Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :
20.08.2010
25.10.2010
25.10.2010
Problem solved at the following versions (IBM BugInfos)
9.5.
Problem solved according to the fixlist(s) of the following version(s)
9.1.0.7 FixList
9.5.0.7 FixList
This site uses cookies to make it easier for us to provide you with our services. By using our site you agree to the use of cookies.