DSXchange

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.

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.

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

If yes, how to avoid it?

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? ).

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?

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.

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.