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,
Key generation in a multiple instance job
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 483
- Joined: Thu Jun 12, 2003 4:47 pm
- Location: St. Louis, Missouri USA
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
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
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.
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
-
- Premium Member
- Posts: 483
- Joined: Thu Jun 12, 2003 4:47 pm
- Location: St. Louis, Missouri USA
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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(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.
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
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.