DSXchange

Any advise on how to generate surrogate keys within datastage and reuse them within and in the next batch process. Currently, we utilize oracle's sequence option to manage the surrogate keys in our database. We are planning to leverage the Datastage Key generator option to minimize calls to the database during the batch processing. Any suggestions is appreciated.

Either you can do that by using @INROWNUM and @OUTROWNUM variables in tranformer or if you are on PX version you will have 'Surrogate Key Generator stage' which can do that. If you need more details do a search on exact term.

THIS will help you

Link isn't working for me. And it's more than just the missing 'www.' on dsxchange.com - the topic doesn't seem to exist or we need a different syntax. Can you try that again, please?

Ah... here we go.

I had a similar requirement, where the sur-key was incremented in one process and had to used in another. I wrote the Sur-key value to a work table, then in a Server job did a select max on that value, passed it to a variable and executed the next job through the server transform. The job executed from the server process, would increment the sur-key value and write it to the work table. I sure there are other ways to do this, but this worked for me. I hope this helps.

I understand that your req is to generate surrogate keys for the database..

The best way i think you can do it (alternative to sequence of database) is to design a shared container so that it will generate a surrogate key for the previous high value by using a transformer or surrogate key generator..
And plugin the shared container in your job wherever applicable..

Write the max(surrogate key) to a file.
read the vlaue for the file
and increment the value by 1 for each input record read...
You can use transformer stage for this..

Regards,
Subhani

You don't need anything intermediate. You can select the value directly into the reference link into a Lookup stage. Make sure that partitioning is Entire. Then add this (constant) value to the zero-based sequence generated in the Surrogate Key Generator or Column Generator stage, where it should have a beginning value of the partition number and an increment of the partition count.