Surrogate Key generator
Moderators: chulett, rschirm, roy
Surrogate Key generator
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.
-
- Charter Member
- Posts: 822
- Joined: Sat Sep 17, 2005 5:25 pm
- Location: USA
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.
I haven't failed, I've found 10,000 ways that don't work.
Thomas Alva Edison(1847-1931)
Thomas Alva Edison(1847-1931)
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.
Charlie
-
- Participant
- Posts: 8
- Joined: Sat Apr 22, 2006 2:28 am
- Location: Bangalore
- Contact:
Surrogate Key generator
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.