Surrogate Key

[kumar66]

Hi All,

How to generate surrogate key in server jobs? Is there any way ?

[g_rkrish]

Hi All,

How to generate surrogate key in server jobs? Is there any way ?

There is a routine under rotuines/sdk/keymgt.....You can use those ..

[ray.wurlod]

There are lots of ways. Research them by searching the forum.

[Luciana]

KeyMgtGetNextValue(Arg1)
===================
- Routine to generate a sequential number. This routine does not support access to a particular sequence by more than one process concurrently.
- The routine argument is the name associated with the sequence.
- The routine uses a file to store the next available number. It reads the number once, then increments and stores the value in common, writing the next value back to file each time.

or

You can create a hash to keep the maximum value of the surrogate key.
Then you create a stage variable in transformer stage beginning with zero. In derivation, verify if the stage (sk) it is zero, if it, will recover the surrogate key of the hash or else it adds one more in the stage.

Code: Select all

if sk = 0 then in.maxSurrogate else sk + 1

[ray.wurlod]

Will other applications also be generating surrogate keys for these tables? This is major factor in your choice of technique.

[chulett]

Yet another wrinkle - will other apps or jobs also be generating keys for these tables at the same time? Another major technique choice factor.

[sudeepmantri]

Well if ur not comfortable with the routines, there is one more option of column generator.Say my source has 2 cols A,B to which I want to add a surrogate key column.
1.Select A,B, 1 as dummy from the source.
2.In col generator generate a dummy col with value=1 always (Set initial value=1, increament by 0) and another col INCR with initial value=part increament by part count)
3.Then based on Dummy col have a join
Now there's the problem like if job aborts!!!!! I have worked out a way for that.. But I dont know how will I be able to explain you.

Say a table has ID n Name. I make a lookup on Name to fetch the ID on the same table. provide the I/P to the Col generator which generates just a dummy (value 1 always). Get the records where ID=0 by applying filter. Join it with the dummy col (inner join) with the same source table (another instance with query like select max(id)+1 as ID, 1 as dummy from <<table>>. Feed this to a col generator which now generates an increament value(incr). Feed this to a transformer which assgins id=id+incr & then writes to the table.


Hope u understand :D

[ray.wurlod]

Ur is a city of ancient Babylon. The second person personal pronoun is "you", not "u". We expect professionalism in communication here.

[chulett]

Well if you are not comfortable with the routines, there is one more option of column generator.

In a Server job?

[ray.wurlod]

Sure. They just choose to call it a Transformer stage type. But there's no reason not to caption it Column_Generator_42.

[chulett]

But we all know the suggestion was to use a specific PX stage.

[hemachandra.m]

Ur is a city of ancient Babylon. The second person personal pronoun is "you", not "u". We expect professionalism in communication here.

Since professionalism is expected. A professional will give solutions to problems rather than looking for loopholes in the questions. I believe what I have said is right?

[battaliou]

Babylon is nice this time of year.

[ray.wurlod]

Not looking for loopholes; attempting to narrow the universe of discourse so as to provide the most apposite answer.