generating seq nos
Moderators: chulett, rschirm, roy
generating seq nos
Hi,
I am trying to generate uniq ids for the data i am inserting.
i know that there are lot of ways to achieve it, but i will be running the jobs parallely so i think there will be a problem.
If i use the max(col) and pass it as a variable to the job and use @OUTROWNUM , then if parallely i run many jobs , the same uniq ids will be created.
it will be the same if i store the max value in a file or take it from sequence object.
Can anyone help.
I am trying to generate uniq ids for the data i am inserting.
i know that there are lot of ways to achieve it, but i will be running the jobs parallely so i think there will be a problem.
If i use the max(col) and pass it as a variable to the job and use @OUTROWNUM , then if parallely i run many jobs , the same uniq ids will be created.
it will be the same if i store the max value in a file or take it from sequence object.
Can anyone help.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
In parallel @OUTROWNUM starts from 1 on every node. Prefer a stage variable or generated column that is initialized to the partition number (0, 1, 2, 3 for example) and incremented by the number of partitions (4 for example).
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.
i can get uniq nos not a problem. but when multiple jobs run at the same time all will query the database and get the max(col)+1 value, which will be same and when they calculate unique values, all will have the same values and cannot insert if it is a primary key for the output table.
i want to have some lock sort of thing while getting the max(col)+1 value.
i want to have some lock sort of thing while getting the max(col)+1 value.
You need to run that sql to get the max(col)+1 on the target table of your job. If the target tables are different for all those jobs, you will get different values as output from that sql for sure.i can get uniq nos not a problem. but when multiple jobs run at the same time all will query the database and get the max(col)+1 value, which will be same and when they calculate unique values, all will have the same values and cannot insert if it is a primary key for the output table.
Thanks,
Mohan.A.Jasti.
Mohan.A.Jasti.
I would think creating a sequence at DB end would be another optiondnat wrote:my target tables are same for multiple jobs.
so if i start the jobs parallely then i will get the same value for all the jobs as input.
So any record you insert into the table the DB would create the id. In DS would be much tougher, since you have to ensure that number don't overlap.
If you still want to do it in DS, then maybe when you pass max number to each job, you should increment it by diff values. e.g:
Job1 start numbers at max , Job 2 start at max+ 10000000
But I would prefer the DB way, In second option you will have to be careful about the increments
This will not work correctly. I checked that by a sample design.Scope wrote:Try this.
(@INROWNUM * @NUMPARTITIONS) - @PARTITIONNUM
This will generate unique numbers.
The below one is the working one..
@PARTITIONNUM + (@NUMPARTITIONS * (@INROWNUM - 1)) + 1
Thought of updating this. It might be useful for some one...