As per IEEE 754 standard for floating point datatypes, floating
point numbers are a binary approximation of an actual number.
As such, when these datatypes are stored, there is potential for
a loss of accuracy (implicit rounding) if there is not enough
bytes in the floating point type to store the binary
representation of the number. For some numbers, in particular
(but not limited to) those numbers that have a large amount of
significant digits, this gives a number that may lose the
precision and accuracy that is needed in some applications.
In DB2, the floating point datatypes are DOUBLE, FLOAT, and REAL
(see documentation about DB2 datatypes). If having precision and
accuracy is required for the application, then it is not
recommended to use floating point types, and the DECIMAL or
DECFLOAT datatype should be used instead.
In DB2, when these floating point types are converted to a
DECIMAL, DB2 imposes a rounding rule on the number.
Specifically, for 8 byte floating point types such as DOUBLE, it
will perform a rounding after the 16th digit. For 4 byte
floating point types such as REAL, it will impose a rounding
after the 7th digit of precision.
REAL example:
db2 "values(decimal(real(123456789),30,10))"
1
--------------------------------
123456800.0000000000
1 record(s) selected.
DOUBLE example:
db2 "values(decimal(double(123456789123456789),30,10))"
1
--------------------------------
123456789123456800.0000000000
1 record(s) selected.
In some cases for numbers that are already suffering from the
binary approximation accuracy concerns, the rounding algorithm
may not be desired.
This APAR provides an alternate approach to the double rounding
technique, by doing a truncation approach instead.
REAL example with fix:
db2 "values(decimal(real(123456789.1235),30,10))"
1
--------------------------------
123456700.0000000000
DOUBLE example with fix:
db2 "values(decimal(double(123456789123456789),30,10))"
1
--------------------------------
123456789123456700.0000000000
1 record(s) selected.
Since this is a behaviour change in how floating point numbers
are treated by DB2, this change is not provided for general use.
If this APAR is required, then please contact DB2 support team,
who can provide guidance on how to enable this alternate mode
for converting floating point types to DECIMAL. |
****************************************************************
* USERS AFFECTED: *
* Customers on V9.1, V9.5, V9.7 using floating point numbers *
* with large number of digits. *
****************************************************************
* PROBLEM DESCRIPTION: *
* FLOATING POINT USAGE CAN RESULT IN LESS ACCURATE RESULTS FOR *
* NUMBERS WITH LARGE AMOUNT OF SIGNIFICANT DIGITS *
****************************************************************
* RECOMMENDATION: *
* Upgrade to fixpack containing this fix. *
**************************************************************** |
