Duplicate Surrogate Key

jerome_rajan · Post by **jerome_rajan** » Mon May 09, 2016 2:35 am

Hi,
I'm not sure if this is a DataStage issue or a Database issue. I have a transformer that has 3 output links. Each output link uses the NextSurrogateKey() to generate a sequence key from an Oracle DB Sequence object. The upstream and downstream all execute in parallel.

I get erratic PK violation errors due to duplicate PK values. What could the reason be?

chulett · Post by **chulett** » Mon May 09, 2016 6:38 am

I'm going to guess it's a DataStage issue somehow as, to the best of my knowledge and experience, an Oracle sequence is not capable of issuing any kind of duplicate value. Not that Oracle ever has any bugs, of course.

In your shoes I'd start with your official support provider.

ray.wurlod · Post by **ray.wurlod** » Mon May 09, 2016 5:16 pm

Might the table be getting new rows from sources other than DataStage, and not updating the Oracle sequence?

jerome_rajan · Post by **jerome_rajan** » Tue May 10, 2016 3:06 am

Craig and Ray,
Thank you for the response.
We did a sanity check to ensure that there are no alternate processes trying to generate the key without the sequence. We even went so far as to tweak the security so that only the system id can insert rows into the table in question.
The only thing that has kept the error from popping up again is changing the execution mode to "Sequential" which led me to believe that the multiple threads accessing the sequence object are causing the conflict somehow. Though, as Craig rightly put, the Oracle sequence is "Incapable" of an anomaly like this