By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,309 Members | 1,452 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,309 IT Pros & Developers. It's quick & easy.

Division error on float data type in DB2

P: n/a
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.

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

I’m not keen on either band-aid, but I don’t 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
Feb 11 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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.
Feb 12 '08 #2

P: n/a
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 Ive heard to explain this behavior is that float
stores too much precision, but Ive used double-precision floating-
point data types in SQL Servers Transact-SQL, COBOL, VB.NET and I
havent seen this odd behavior. DB2s 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...
Feb 12 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.