Mike wrote:
I'm running DB2 v7 for z/OS. When I use SPUFI, SELECT CAST(6.0 AS
FLOAT)/CAST(10.0 AS FLOAT) FROM SYSIBM.SYSDUMMY1 returns
0.6000000000000000E+00. When I use DSNTIAUL,DSNTEP2, or DSNALI (call
attach facility), the same statement returns 0.59999999999999999E 00.
The only reason I've heard to explain this behavior is that
float stores too much precision, but I've used
double-precision floating- point data types in SQL Server's
Transact-SQL, COBOL, VB.NET and I haven't seen this odd
behavior. DB2's SQL is the only language I know of where 6/10
!= 0.6.
This isn't an error - it's just DB2 being "brutally honest" about the
result of the calculation. It's impossible to accurately represent the
decimal value 0.6 in binary floating point (0.5999999... is the closest
that can be achieved). It looks like SPUFI is performing a common trick
of rounding off the last digit for display purposes, whereas CAF isn't
bothering and is simply displaying the unrounded result (note the extra
digit in the CAF result):
SPUFI: 0.6000000000000000E+00
CAF: 0.59999999999999999E 00
You can see the same thing in Python on an ordinary PC, which is also
"brutally honest" about the results of floating point calculations
(i.e. doesn't perform any rounding on the result):
Python 2.5 (r25:51908, Sep 19 2006, 09:52:17) [MSC v.1310 32 bit
(Intel)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>6.0 / 10.0
0.59999999999999998
All the other environments you mention (SQL Server, COBOL, VB.NET) will
be performing rounding for display purposes, but internally they'll be
getting 0.599999... as the result of the calculation (if they're using
floating point and not something else).
My particular problem is that my COBOL programs are getting inacurate
results (6/10 ? 10) when using CAF to divide the values of
some FLOAT columns on a DB2 table. The only two work-arounds I can
think of are a) Don't use SQL to do divide the column values
b) Round the resulting value
If CAF isn't doing the rounding step, then your best option is to do it
yourself.
You can find more information on binary floating point representation
here:
http://en.wikipedia.org/wiki/Floating_point
(see the "Value", "Conversion and rounding" and "Accuracy Problems"
sections)
http://en.wikipedia.org/wiki/Binary_numeral_system
(see the "Representing Real Numbers" section)
http://en.wikipedia.org/wiki/IBM_Flo...t_Architecture
(I've no idea if SPUFI or CAF are using standard IEEE754 floating point
values, or the IBM floating point representation - but you'll encounter
such issues in either)
Cheers,
Dave.
