1. ∞∞∞∞∞∞
Join Date
Aug 2008
Location
Posts
2,378

Could you please explain why the value is not stored/displayed correctly?
Last edited by db2girl; 11-30-11 at 16:56.

2. :-)
Join Date
Jun 2003
Location
Posts
5,516
Floating point numbers (FLOAT and DOUBLE) are approximate. In the second example you convert the BIGINT literal twice - from BIGINT to DOUBLE, then from DOUBLE to DECIMAL, so I'd think some loss of precision might be expected.

It would be good to know what the customer think are "correct" and "incorrect" results.

3. ∞∞∞∞∞∞
Join Date
Aug 2008
Location
Posts
2,378
In this example, customer thinks scale !=0 is incorrect:

"Correct" result:
444444444444444.00

"Incorrect" results:
444444444444444.50
444444444444444.10

In their case, the column is defined as dec (31,2) - 1st example.

I'm attaching another doc with some additional info provided by the customer.

Do you think what they're seeing is expected (but zOS results are different from LUW), db2 bug, jdbc bug...?
Last edited by db2girl; 11-30-11 at 16:55.

4. :-)
Join Date
Jun 2003
Location
Posts
5,516
Originally Posted by db2girl
In this example, customer thinks scale !=0 is incorrect:

"Correct" result:
444444444444444.00

"Incorrect" results:
444444444444444.50
444444444444444.10

In their case, the column is defined as dec (31,2) - 1st example.

I might be wrong as I've had a couple of beers now, but the FLOAT literal 4.4444444444444444E14 seems to be the same as 444444444444444 plus something around 0.44 (floating point numbers are approximate), which nicely fits into DECIMAL(31,2), so 444444444444444.00 would definitely be incorrect, z/OS or not.

Now, JDBC Double supports only 15 digits for the mantissa, which does not mean that it would truncate the extra digits - rather, if you supply more than 15 digits for the mantissa (which they do) only the first 15 will be defined.

Can your customer explain why they think 444444444444444.00 is the correct decimal representation of 444444444444444.44?

Normally you would never use floating point data type for currency (money) variables - only decimals.

5. ∞∞∞∞∞∞
Join Date
Aug 2008
Location
Posts
2,378
You're correct - 4.4444444444444444E14 is 444444444444444.44 so I'm not sure why they think 444444444444444.00 is correct.

Using their second example of inserting
444444444444444.00

Why does select return?
444444444444444.10 - LUW
444444444444444.00 - zOS

I'm not sure I completely understand about "JDBC Double supports only 15 digits for the mantissa..."

6. :-)
Join Date
Jun 2003
Location
Posts
5,516
Mantissa holds the significant digits of a number, so if you try to store in a Double something that needs more than 15 digits, it will be approximated to a number that has 15 significant digits. "4.4444444444444444E14" has 17 significant digits, and if you were to assign it to a Double, it would turn into ".444444444444444E15" (15 digits).

As for the difference between the LUW and z/OS representation of the DOUBLE 444444444444444.00, I guess it might be attributed to the different floating point implementation that depends on the hardware; both numbers are approximately the same, and that is correct by the definition of the DOUBLE: "A double-precision floating-point number is a 64-bit approximation of a real number. "

7. ∞∞∞∞∞∞
Join Date
Aug 2008
Location