Page 1 of 2
Datastage 6.0 - arithmetic loss of precision
Posted: Wed Oct 15, 2014 1:01 am
by huge10
Hi,
I got a crazy result for a calculation inside the transformer in my parallel Job.
I have 3 stage variables:
A decimal(15,2)
B decimal(15,2)
C decimal(15,2)
Considering that
A = 3.98
B = 30 (30.00)
When I try to do the operation
C = (A / 30) * B
I get 3.97 as result!!!
I tried with the same stage variables but format "decimal(15,3)" and the result change into 3.979.
It seems to be a loss of precision!!!
Could the problem depends for the version of Datastage (6.0)?
I need some help..
Posted: Wed Oct 15, 2014 6:06 am
by qt_ky
I tested that in 8.7 and got a result of 3.98.
Posted: Wed Oct 15, 2014 6:26 am
by huge10
Thanks for your answer,
do you know if there's a BUG on version 6.0 for the problem?
Posted: Wed Oct 15, 2014 11:53 am
by qt_ky
It's possible. If you create a new test job from scratch, what is the result?
Here are a few technotes you may be interested in, although I doubt either one is an exact match:
Float data incorrect or corrupt in a DataStage job
http://www-01.ibm.com/support/docview.w ... wg21515626
Parallel jobs: Data can be being corrupted when moved between different data types with no warning issued (APAR JR46434)
http://www-01.ibm.com/support/docview.w ... wg21639629
Posted: Wed Oct 15, 2014 2:02 pm
by chulett
Are you planning on upgrading some day? I can't imagine there would be any patches nor support for the 6.x release. Neither of the Technotes mention that version.
Posted: Wed Oct 15, 2014 3:24 pm
by ray.wurlod
What happens if your expression is ( A / 30.0 ) * B ?
Posted: Wed Oct 15, 2014 4:33 pm
by chulett
Oooo... very clever, Mr Bond. I wonder if that will do the trick?
Posted: Wed Oct 15, 2014 5:47 pm
by qt_ky
I actually ran across an IBM technote for version 4, just today! Version 6 is definitely unsupported unless possibly someone has paid big $$ to extend a support contract since then.
Posted: Wed Oct 15, 2014 6:24 pm
by ray.wurlod
Or, to go completely overboard
Code: Select all
( DecimalToDecimal(A) / DecimalToDecimal(30) ) * DecimalToDecimal(B)
Then you get control of the rounding to some extent.
Posted: Thu Oct 16, 2014 1:49 am
by huge10
Thanks to everybody but I'm afraid there's a real BUG on datatype "decimal".
I tried the following:
1- ( A / 30.0 ) * B
unsuccessful
2- ( DecimalToDecimal(A) / DecimalToDecimal(30) ) * DecimalToDecimal(B)
obviously without precision I get the result 3 instead of 3.98
if I define the precision "DecimalToDecimal(A,2)" I get an error of the Job; I don't know why!
3-new job from scratch
same problem 3.97 instead of 3.98
4- I tried also the following:
stage1 decimal(17,2) = 3.98 * 100 --> 398
stage2 decimal(17,2) = 30 * 100 --> 3000
but
stage1 / 30 * stage2 --> 397.99 !!!
The only way I can resolve the problem is to use float(17,2) datatype.
I don't know if float could be easilsy converted by Oracle when I save the information to the DB... but stay tuned, I'll try as soon as possible
Posted: Thu Oct 16, 2014 7:27 am
by chulett
Wow... I think that's the first time I've heard of float actually
solving a problem rather than causing it.
![Wink :wink:](./images/smilies/icon_wink.gif)
And yes, Oracle can handle it just fine.
Posted: Thu Oct 16, 2014 4:45 pm
by stuartjvnorton
Hang on: version 6?
![Razz :P](./images/smilies/icon_razz.gif)
Posted: Thu Oct 16, 2014 6:05 pm
by ray.wurlod
Version 6 - first version to include parallel jobs - support ended in April 2010.
Reminisce
here.
Posted: Fri Oct 17, 2014 12:46 am
by huge10
Actually when Oracle 9 write the value "3,98 float" I see on the table "3,980000019"
But may be I've found a work around:
(a / 30) * b
is
3.98 / 30 = 0,13266666666666666666666666666667 <-- I think here there's a loss of precision in DECIMAL
but if I use
(a * b) / 30
3.98 * 30 = 119.4 ... 119.4 / 30 = 3.98 <-- without loss of precision
What do you think?
Could it be the solution?
Posted: Fri Oct 17, 2014 4:22 am
by ray.wurlod
Back in the day (when 8KB was a LOT of memory) I was taught always to complete multiplications before doing any divisions. Multiplication apparently suffers less precision loss than division.
As to whether you have a "solution" - only time will tell.