Datastage 6.0 - arithmetic loss of precision
Moderators: chulett, rschirm, roy
Datastage 6.0 - arithmetic loss of precision
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..
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..
the power is nothing without contol ![Smile :-)](./images/smilies/icon_smile.gif)
![Smile :-)](./images/smilies/icon_smile.gif)
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
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
Choose a job you love, and you will never have to work a day in your life. - Confucius
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Or, to go completely overboard
Then you get control of the rounding to some extent.
Code: Select all
( DecimalToDecimal(A) / DecimalToDecimal(30) ) * DecimalToDecimal(B)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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
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
the power is nothing without contol ![Smile :-)](./images/smilies/icon_smile.gif)
![Smile :-)](./images/smilies/icon_smile.gif)
-
- Participant
- Posts: 527
- Joined: Thu Apr 19, 2007 1:25 am
- Location: Melbourne
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Actually when Oracle 9 write the value "3,98 float" I see on the table "3,980000019" ![Sad :-(](./images/smilies/icon_sad.gif)
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?
![Sad :-(](./images/smilies/icon_sad.gif)
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?
the power is nothing without contol ![Smile :-)](./images/smilies/icon_smile.gif)
![Smile :-)](./images/smilies/icon_smile.gif)
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
As to whether you have a "solution" - only time will tell.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.