@INROWNUM NOT UNIQUE!!

[ryoung011]

DSXers,

I am loading a table with a unique (surrogate) key.

We have a hash file that maintains the the MAX() surrogate key for each table.

For all new records, the key values are generated in a link variable which uses the MAX() surrogate key from the hash and @INROWNUM:

SurrogateKey = HashMaxKeyLookup.MAX_SURRKEY + @INROWNUM

The issue is this expression is generating duplicates. The hash file has a single record (with value of 36,809). But I'm getting 55,115 as the SurrogateKey on two output records, which indicates that this happens on input row number 18,306 (55,115 - 36,809).

One thing I have noticed is that @OUTROWNUM is not repeating and creates unique keys. BUT, @OUTROWNUM is not available in Stage Variables.

Any ideas?

This is completely reproducible.

[ArndW]

Hello Ryoung011,

first off, the good news is that the @INROWNUM is unique coming into your transform, so something else is going wrong. How are you doing the hash file lookup; are you doing it for each input row (not fast, but it works) ? If this is reproduceable then could you add another output link to a flat file which also contains the @INROWNUM as well as the hash lookup value and see if the error shows up there?

[roy]

Hi,
Also is there another insert performed on that table while your job runs?
this is bound to mess your surogate key and cause duplicate keys.
it might even be a defunct still running from an aborted previous run of your own job.
IHTH,

[Sainath.Srinivasan]

Can it be that the surrogate key fn + inrownum gave this problem.

Eg.
Surr Key In Row Num Total
101 1 102
102 2 103
103 3 104
099 4 103
104 5 109

So please check the logic by passing these rows from the source back through the surrogate key transform.