Surrogate id generation - multiple inst jobs at same time.
Posted: Sun Jul 29, 2007 9:49 am
We are using DB2 database has dpf (dynamically partitioned feature) enabled. Our Px job say JobA has multiple invocation enabled.
For an incoming record if surrogate id is created based on if previously generated. In the next stage this id generated gets stored into the database.
When job is called at same time 2 or more instances, all its instances create same surrogate id. When these jobs attempts to hit the database at the same time we face warnings in the log attempt to insert duplicate ids.
We could not find any isolation level settings with DB2 EE stage. As well we attempted to use DB2 API with isolation level setting as Repeatable Reads, still it did not work as expected.
I am sure DataStage must have solution to address such issues. Can someone please enlighten me, the steps or approach how we can achieve this?
For an incoming record if surrogate id is created based on if previously generated. In the next stage this id generated gets stored into the database.
When job is called at same time 2 or more instances, all its instances create same surrogate id. When these jobs attempts to hit the database at the same time we face warnings in the log attempt to insert duplicate ids.
We could not find any isolation level settings with DB2 EE stage. As well we attempted to use DB2 API with isolation level setting as Repeatable Reads, still it did not work as expected.
I am sure DataStage must have solution to address such issues. Can someone please enlighten me, the steps or approach how we can achieve this?