generating seq nos

dnat · Post by **dnat** » Wed Apr 01, 2009 11:51 pm

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.

ray.wurlod · Post by **ray.wurlod** » Wed Apr 01, 2009 11:57 pm

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).

Scope · Post by **Scope** » Thu Apr 02, 2009 12:15 am

Try this.

(@INROWNUM * @NUMPARTITIONS) - @PARTITIONNUM

This will generate unique numbers.

shalini11 · Post by **shalini11** » Thu Apr 02, 2009 12:30 am

Pass (max(col)+1) as a job parameter and then use this parameter in Surrogate key Generator. Give the start value as the value of this parameter.

dnat · Post by **dnat** » Thu Apr 02, 2009 5:48 am

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.

Jasti · Post by **Jasti** » Thu Apr 02, 2009 6:51 am

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.

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.

dnat · Post by **dnat** » Thu Apr 02, 2009 7:06 am

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.

wahi80 · Post by **wahi80** » Thu Apr 02, 2009 11:20 am

dnat 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.

I would think creating a sequence at DB end would be another option
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

dsedi · Post by **dsedi** » Fri May 22, 2009 12:45 pm

Scope wrote:Try this.

(@INROWNUM * @NUMPARTITIONS) - @PARTITIONNUM

This will generate unique numbers.

This will not work correctly. I checked that by a sample design.
The below one is the working one..

@PARTITIONNUM + (@NUMPARTITIONS * (@INROWNUM - 1)) + 1

Thought of updating this. It might be useful for some one...

pprakash · Post by **pprakash** » Mon Sep 03, 2012 2:01 am

[quote="Scope"]Try this.

chulett · Post by **chulett** » Mon Sep 03, 2012 8:37 am

That's already been noted and corrected three years ago. Not sure what good posting it again does.