Datastage 6.0 - arithmetic loss of precision

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

[qt_ky]

I tested that in 8.7 and got a result of 3.98.

[huge10]

Thanks for your answer,
do you know if there's a BUG on version 6.0 for the problem?

[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

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

[ray.wurlod]

What happens if your expression is ( A / 30.0 ) * B ?

[chulett]

Oooo... very clever, Mr Bond. I wonder if that will do the trick?

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

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

[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

[chulett]

Wow... I think that's the first time I've heard of float actually solving a problem rather than causing it. And yes, Oracle can handle it just fine.

[stuartjvnorton]

Hang on: version 6?

[ray.wurlod]

Version 6 - first version to include parallel jobs - support ended in April 2010.

Reminisce here.

[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?

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