Key generation in a multiple instance job

NEO · Post by **NEO** » Fri Sep 17, 2004 8:11 am

Hi,
I have a job which retreives the max value of the key from a DB2 table and increments it in datastage by using @OUTROWNUM when loading data to the above DB2 table. This approach works fine when my job is the only job loading to the table. But this approach will fail if multiple jobs are loading to the table at the same time. But retreiving the max value from the table for every row sounds like a lot of load. And I am not quite sure how to exactly do that even if I wanted to.
Thanks,

tonystark622 · Post by **tonystark622** » Fri Sep 17, 2004 8:41 am

You can use a Sequence in DB2 (I think they have Sequences) and get a new one for each insert.

Another approach would be to use a DataStage Sequence and acquire it with the KeyMgtGetNextValueConcurrent routine. There are ways to seed it, so that it starts with the correct value if you need that, but you should only have to do that once, unless values are inserted into the DB2 table from other sources than your DataStage jobs.

Good Luck,
Tony

NEO · Post by **NEO** » Fri Sep 17, 2004 8:51 am

If I use the KeyMgtGetNextValueConcurrent routine, it gaurantees that if multiple instances of my job run, then the key is still unique. That gets me to the next question. If there are multiple jobs running in multiple instance loading to the same table, the uniqueness of the key cant be maintained unless all the different jobs use the same ID in the KeyMgtGetNextValueConcurrent routine when generating the key. If they use different IDs then it will be a problem. How are such scenarios handled?
Thanks.

tonystark622 wrote:You can use a Sequence in DB2 (I think they have Sequences) and get a new one for each insert.

Another approach would be to use a DataStage Sequence and acquire it with the KeyMgtGetNextValueConcurrent routine. There are ways to seed it, so that it starts with the correct value if you need that, but you should only have to do that once, unless values are inserted into the DB2 table from other sources than your DataStage jobs.

Good Luck,
Tony

kommven · Post by **kommven** » Fri Sep 17, 2004 9:16 am

I have faced a similar scenario and I used same ID which is the target table key column name. and it solved my problem. Editing jobs and changing the key sequencer ID is much easier.

tomengers · Post by **tomengers** » Fri Sep 17, 2004 10:55 am

NEO ...

Be aware that KeyMgtGetNextValueConcurrent() locks the SDKSequences table for each number retrieved. If you have a volume job ... this will create a significant choke point.

... tom

tonystark622 · Post by **tonystark622** » Fri Sep 17, 2004 2:52 pm

Tom,

What solution do you recommend for this problem?

Tony

ray.wurlod · Post by **ray.wurlod** » Fri Sep 17, 2004 4:12 pm

Whatever you do to guarantee unique IDs will involve single-threading, and therefore a "choke point", so you have to make it as quick as possible.

You could modify the SDK routine and make it more efficient. You could rely on a sequence or a SERIAL data type in the target database. If you're using integer instance numbers you could use an expression like

Code: Select all

startvalue + 1000000 * invocationID + @OUTROWNUM

(select an appropriate constant based on your load volumes; startvalue is obtained from the target table as the maximum value for which the invocation range is the one in question).

You can NOT use an approach that uses a variable defined to be in a COMMON area, as this is local to one process. Multiple instances run in separate processes.