DSXchange

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.

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.

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?

Welcome Aboard!

The answer is in Ray's reply.

ray.wurlod wrote:Sequences in SDKSequences can be initialized with simple INSERT or UPDATE statements, or with a routine. Search the forum for examples.

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.

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.

You need my reseed job that is a part of EtlStats.