Surrogate Key Generation using DS routine
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 95
- Joined: Thu Sep 24, 2009 12:15 am
- Location: India
Surrogate Key Generation using DS routine
Hi All,
I am completely new to DataStage and I am yet to learn the basics.
I would like to know how to generate surrogate keys in a sequential order using DS Routine. Do we use KeyMgtGetNextValue()? If yes, how exactly do we go about using it in the Transformer stage?
I used a Stage Variable, but am unable to give the routine-KeyMgtGetNextValue() in its derivations column.
Regards,
Deepa
I am completely new to DataStage and I am yet to learn the basics.
I would like to know how to generate surrogate keys in a sequential order using DS Routine. Do we use KeyMgtGetNextValue()? If yes, how exactly do we go about using it in the Transformer stage?
I used a Stage Variable, but am unable to give the routine-KeyMgtGetNextValue() in its derivations column.
Regards,
Deepa
Last edited by deepa_shenoy on Tue Sep 29, 2009 6:00 am, edited 1 time in total.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 95
- Joined: Thu Sep 24, 2009 12:15 am
- Location: India
Thanks guys!!
I realised its possible only in Server Jobs and not in Parallel Jobs.
Is there any simple efficient method to generate Surrogate Keys in sequence, in a parallel job, without using Database Sequence??
I tried using the Flat File option but it generates surrogate keys like 1,1001,2001,2002,3001,4001,5001 etc which I am guessing is because of the 8 node configuration.
Can I use the Flat File option and get 1,2,3,4.. as the sequence?
Thanks in advance.
Deepa
I realised its possible only in Server Jobs and not in Parallel Jobs.
Is there any simple efficient method to generate Surrogate Keys in sequence, in a parallel job, without using Database Sequence??
I tried using the Flat File option but it generates surrogate keys like 1,1001,2001,2002,3001,4001,5001 etc which I am guessing is because of the 8 node configuration.
Can I use the Flat File option and get 1,2,3,4.. as the sequence?
Thanks in advance.
Deepa
Last edited by deepa_shenoy on Tue Sep 29, 2009 6:00 am, edited 1 time in total.
What 8.x version do you have? There is something new in 8.1 (from what I recall) that will allow the generation of a surrogate key with a call to a new function in the Transformer stage. There's also this FAQ posting if you want to do it in a manual fashion.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 95
- Joined: Thu Sep 24, 2009 12:15 am
- Location: India
Hi,
Thanks for the input. I am trying the other method too.
I am using version 8.1. Is there any routine available here?
Regards,
Deepa
Thanks for the input. I am trying the other method too.
I am using version 8.1. Is there any routine available here?
Regards,
Deepa
Last edited by deepa_shenoy on Tue Sep 29, 2009 6:01 am, edited 1 time in total.
Don't use a routine to do this in PX. Either the surrogate key generator or you can also use functionality for surrogate keys built into the transform stage.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 95
- Joined: Thu Sep 24, 2009 12:15 am
- Location: India
Hey,
I used the manual method, but the records are not in order.
When I use the Sorter Stage, the records are still inserted into the target table in a jumbled manner. How to resolve this?
Thanks in advance.
Regards,
Deepa
I used the manual method, but the records are not in order.
When I use the Sorter Stage, the records are still inserted into the target table in a jumbled manner. How to resolve this?
Thanks in advance.
Regards,
Deepa
Last edited by deepa_shenoy on Tue Sep 29, 2009 6:01 am, edited 1 time in total.
It is not quite clear what you are having problems with. Surrogate key generation is unique across all nodes and ordered within each node. If you have multiple nodes and write to a sequential target your ordering will not sequential. Either run on 1 node or put a sort on the surrogate key into your job if the ordering is important.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
It is a fundamental tenet of database technology that the database engine determines the order of row storage, not you. If you want sorted values when you query the table, specify this requirement in the query.
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.
-
- Participant
- Posts: 95
- Joined: Thu Sep 24, 2009 12:15 am
- Location: India