I am talking about Datastage Server, not the parallel server..
There is an API routine KeyMgtGetNextValue, which can help us to generate a sequence number ..
Do you guys usually use it to generated sequence number ?
by the way, If I don't want to start from 0, for example I want to start from a certain point like 1000000. Does KeyMgtGetNextValue can help me ?
Or the only way is to write my personal routine ...
Thanks.
How to get sequence number in Datastage
Moderators: chulett, rschirm, roy
-
xinhuang66
- Participant
- Posts: 161
- Joined: Wed Aug 02, 2006 4:30 am
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
The two SDK routines KeyMgt... manage generation of sequence numbers by keeping each sequence name as a record in a hashed file called SDKSequences, which is automatically created as necessary.
Sequences in SDKSequences can be initialized with simple INSERT or UPDATE statements, or with a routine. Search the forum for examples.
Another way to maintain a sequence is, of course, to use a sequence or a serial data type in the database during the load phase. Again, search the forum for explicit techniques.
Sequences in SDKSequences can be initialized with simple INSERT or UPDATE statements, or with a routine. Search the forum for examples.
Another way to maintain a sequence is, of course, to use a sequence or a serial data type in the database during the load phase. Again, search the forum for explicit techniques.
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.
Re: How to get sequence number in Datastage
I tried both the function given in SDK, but every time I run the job it starts from the next value. Means if in the last job it ends at 20 then it will start from 21. For some jobs I want it to restart from 1. How can I do that? 
!! Ashutosh G !!
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You can set up a job parameter with the start value, and simply add the value if @OUTROWNUM to it. This is an alternative to using the SDK routines.
Search the forum; there are detailed instructions on initializing a sequence in SDKSequences.
Search the forum; there are detailed instructions on initializing a sequence in SDKSequences.
Last edited by ray.wurlod on Wed Jan 31, 2007 2:56 pm, edited 1 time in total.
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.
Give different argument for different jobs (atleast for different tables) for the Key Management routines. This way you will not mess up the keys and confuse them. There are ways of updating the SDKSequences file and as advised, search the forum for more details. What I like to do is keep the control in my hands. I do a select max(id) from table. Store the value in a hashed file. Access that file in my transformer and generate keys for each incoming row. Even that method is discussed before. A simple search will reveal it all.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.