Surrogate Key

c341 · Post by **c341** » Wed Mar 24, 2004 5:49 pm

Hi
I'm using @OUTROWNUM variable to generate surrogate keys...and I entered my records in the Oracle table. When i try to insert a new record i've to find the max surrogate key generated and include this in the derivation with an increment.
How can I do this!
Thank You

sivatallapaneni · Post by **sivatallapaneni** » Wed Mar 24, 2004 7:32 pm

In a before job get the max surrogate key and write it to a hash file and write a routine to get the value in the hash file and assign that to a stage variable and increment that stage variable using your @OUTROWNUM.

Hope this helps.
Siva

ray.wurlod · Post by **ray.wurlod** » Wed Mar 24, 2004 7:48 pm

You can certainly do this. There are many techniques, you can search for them on the Forum.
Then again, you may not need to. If the target database supports serial data type, or sequences, then you can have the surrogate keys generated when inserting rows into the dimension tables.

phanee_k · Post by **phanee_k** » Thu Mar 25, 2004 4:55 am

Hi
We can use the KeyMgtGetNexValConcurrent routine which is present in datastage for generating the sequence numbers using Datastage.Tyr this routine

Hope this Helps
Phani

nag0143 · Post by **nag0143** » Thu Mar 25, 2004 10:58 am

hi sivatallapaneni,

write a routine to get the value in the hash file and assign that to a stage variable

i din't understand it... can u show me a sample (ex..) routine which do that.

thank you

nag

sivatallapaneni · Post by **sivatallapaneni** » Thu Mar 25, 2004 5:50 pm

Do a search and you will definetly find some thing to get going.

Here is a good one.

Code: Select all

http://dsxchange.com/viewtopic.php?t=86052&highlight=openpath

This should get you going.

Siva.

c341 · Post by **c341** » Fri Mar 26, 2004 6:07 pm

Hi
It worked....
Thank you All