Decimal value is out of range

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
balu99999
Participant
Posts: 5
Joined: Wed May 13, 2015 9:40 am

Decimal value is out of range

Post by balu99999 »

Hi,
One of my datastage jobs is failling with below error in SCD stage.
Fatal Error: APT_Decimal::asIntegerU64: the decimal value is out of range for the integer result.

Job design: Source(oracle) ->Transformer -> SCD -> Target(oracle)

The issue is with dummy record (Surrogate Key = -100) in the dimension table. There are 2 columns in source (key value pair). If the source key matches with the dummy record in target, ETL job is failing.

Dummy record (target dimension table):
SurrogateID Key Value
-100 N/A N/A


I was able to resolve the issue with either of 3 approaches, but still want to know the actual reason of failure.
a.) Filtered out 'N/A' key from source.
or
b.) Changed data type of surrogate key from Decimal(38,0) to Integer (10) and job runs fine without filtering the 'N/A' record in source.
or
c.) Using abs(surrogate key) while fetching data from target to SCD stage for comparison.

I searched forums and the suggestion given by ArndW for this issue was to contact IBM support.
viewtopic.php?t=147220

Please help on this.
Best Regards,
Balakrishna V
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Arnd's suggestion to contact support was for the situation where the values were allegedly appropriate for the data type. However, that "IntegerU64" is unsigned so -100 would indeed be "out of range" for it.
-craig

"You can never have too many knives" -- Logan Nine Fingers
balu99999
Participant
Posts: 5
Joined: Wed May 13, 2015 9:40 am

Post by balu99999 »

I defined data type as Decimal(38,0).
Is SCD converting Decimal(38,0) to unsigned integer?

If yes, how to avoid it?
Best Regards,
Balakrishna V
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

The surrogate key value is ALWAYS generated as UInt64 (unsigned BigInt). You can manipulate this in a Transformer stage if you need negative surrogate key values ( but why would you need them? ).
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

We use them (-1 specifically) for the single "dummy" or N/A record in dimensions, something pretty common. However, they are never generated by anything but are rather inserted as part of the table build, DML as part of the delivered DDL. They're matched to when the business key lookup during the fact load fails, i.e. NULL results either converted to post-lookup or NULL values converted to "N/A" pre-lookup.

Not really sure how this plays into the SCD stage, never having actually used it. Does it have a problem simply matching to / fetching the dummy record since the surrogate value for it is negative?
-craig

"You can never have too many knives" -- Logan Nine Fingers
UCDI
Premium Member
Premium Member
Posts: 383
Joined: Mon Mar 21, 2016 2:00 pm

Post by UCDI »

just bits ...
-1, if dumped without any logic into unsigned int, will give the maximum value of that int.

That is if you put -1 into an unsigned byte without any error checks you get 255, and if you do it for a 64 bit int you get 2^64-1 whatever that is.

That might work just as well as -1 for a sentinel value. That is, you are unlikely to fill a 64 bit int's worth of keys between now and the time they unplug your server for scrap. Its a really large number; A program that does nothing but iterate from 0 to this value as fast as it can go takes years to complete. It should be a 'safe' value.

-100 is close to this value, and equally usable for the same reasons.
balu99999
Participant
Posts: 5
Joined: Wed May 13, 2015 9:40 am

Post by balu99999 »

We are inserting this Dummy record (Surrogate Key = -100) manually to the table.


When source has a Key that matches to the dummy record's key, job is failing.
If I filter out that record from source, job runs fine.
Best Regards,
Balakrishna V
Post Reply