Division error on float data type in DB2

[Mike]

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.SYSDUMMY 1 returns
0.6000000000000 000E+00. When I use DSNTIAUL,DSNTEP 2, or DSNALI (call
attach facility), the same statement returns 0.5999999999999 9999E 00.
The only reason I$B!G(Bve heard to explain this behavior is that float
stores too much precision, but I$B!G(Bve used double-precision floating-
point data types in SQL Server$B!G(Bs Transact-SQL, COBOL, VB.NET and I
haven$B!G(Bt seen this odd behavior. DB2$B!G(Bs SQL is the only language I know
of where 6/10 $B!b(B 0.6.

My particular problem is that my COBOL programs are getting inacurate
results (6/10 $B!b(B 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$B!G(Bt use SQL to do divide the column values
b) Round the resulting value

I$B!G(Bm not keen on either band-aid, but I don$B!G(Bt see any DSNALI call
parameters that could solve this problem. Can anyone help?

P.S. Changing the data type is not an option for us. We need to store
a wide variety of precisions in these columns.

Thanks in advance,
Mike

[Dave Hughes]

Re: Division error on float data type in DB2

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.SYSDUMMY 1 returns
0.6000000000000 000E+00. When I use DSNTIAUL,DSNTEP 2, or DSNALI (call
attach facility), the same statement returns 0.5999999999999 9999E 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.6000000000000 000E+00
CAF: 0.5999999999999 9999E 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.5999999999999 9998

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 "Representi ng Real Numbers" section)

http://en.wikipedia.org/wiki/IBM_Floating_Point_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.

[Boris Stumm]

Re: Division error on float data type in DB2

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.SYSDUMMY 1 returns
0.6000000000000 000E+00. When I use DSNTIAUL,DSNTEP 2, or DSNALI (call
attach facility), the same statement returns 0.5999999999999 9999E 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.

As Dave pointet out, DB2's SQL behaves perfectly normal and the same
as all other programming languages.

P.S. Changing the data type is not an option for us. We need to store
a wide variety of precisions in these columns.

Actually, with the float data type it is not possible to store "a wide
variety of precisions". Floats have a fixed precision. You should read
a bit about this topic, especially when it comes to financial applications
it tends to be somewhat important...