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$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

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.

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.

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...