Surrogate Key Generation in PX

shivakumar · Post by **shivakumar** » Tue Oct 31, 2006 2:13 am

Hi ,

I have to create a surrogate key for a target table by taking the maximum value from the same target table.

For example I get the Maximum value as 100 now I have to insert the values starting from 101 for this I am using the Transformer stage for incrementing the values the Logic is MAXVALUE+INROWNUM.

In the PX the inrow num is not proper because based on the Partitions it is givinig the same rownumbers for next partition.

For Eg

Partion Number Row Number Max(Value)+Rownum
1 1 101
1 2 102

2 1 101
2 2 102

Can you please help me out.

Regards
Shiva

How Can I resolve this

keshav0307 · Post by **keshav0307** » Tue Oct 31, 2006 2:18 am

1st get the maximum value (say MAX_SURG) of surrogate key from the target, for this you may write a routine to parse a single SQL) .

now use surrogate key generator stage, with start value of MAX_SURG+1

kumar_s · Post by **kumar_s** » Tue Oct 31, 2006 2:53 am

Add 'PartitionNum' to the RowNum to get the unique value across partitions.

vmcburney · Post by **vmcburney** » Tue Oct 31, 2006 3:57 am

You have two ways of doing it. The surrogate key generator stage does handle the multiple partitions to give you unique numbers. You can mimic this in a transformer stage variable following the instructions in the FAQ forum on creating a parallel counter.

kool78 · Post by **kool78** » Tue Oct 31, 2006 11:59 am

one of the best possible methods would be using "Oracle Sequence".

keshav0307 · Post by **keshav0307** » Wed Nov 01, 2006 2:05 am

kool78 wrote:one of the best possible methods would be using "Oracle Sequence".

how you will use the oracle sequence, will you give a oracle call for each row, or you will write a before insert trigger??

what is it is a teradata table or any other batabase or if you want to write in a csv file etc.

vmcburney · Post by **vmcburney** » Wed Nov 01, 2006 1:32 pm

Not a fan of the database generated keys unless you have multiple products loading at the same time. The DataStage generated keys do a good job. See my comments at: Why database generated surrogate keys drive me nuts!!!