Surrogate key using only one instance.

dsadm_ws · Posted: Mon Jun 06, 2011 9:08 am

Have anyone come across this issue.

I am trying to generate a surrogate key using the following logic in my transformer which works fine:

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

However, it's using only one 1 instance on 2 nodes(using a dataset in the downstream).

Thanks!

jwiles · Posted: Mon Jun 06, 2011 9:31 pm

So, do you have a question or problem?

Gokul · Posted: Mon Jun 06, 2011 11:36 pm

Datastage works within nodes. Hence your surrogate key generator logic would work if either it is run one instance or the rows are equally distributed accross all the partitions.

To generate , unique consecutive values in datastage
a> we used to use transformer ( with partition number and number of partitions used) in sequential mode.
or
b> the columne generator in sequential mode.

dsadm_ws · Posted: Wed Jun 08, 2011 1:14 pm

I finally found out what the problem was.

I am running my job on two nodes and before generating the SK i was using Hash Partitioning.

And this was placing all the records in one dataset.

So, tried with round robin, and it worked like a magic.

Thanks Again for all the inputs.

pprakash · Posted: Mon Sep 03, 2012 1:51 am

Just want to add, how "Round Robin Partition" works with the logic below;

This will work!

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

i tried with 3 nodes by with 'Round Robin Partitioning'(there are 9 records). Please find the detailed below;

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

Partition 1:-

0+(3*(1-1)+1=1
0+(3*(2-1)+1=4
0+(3*(3-1)+1=7

Partition 2:-

1+(3*(1-1)+1=2
1+(3*(2-1)+1=5
1+(3*(3-1)+1=8

Partition 3:-

2+(3*(1-1)+1=3
2+(3*(2-1)+1=6
2+(3*(3-1)+1=9