Generate the Unique Sequence ID

rkumar28 · Post by **rkumar28** » Tue Mar 30, 2004 2:34 pm

Hi,

How do we generate the Unique ID like (1,2,3....) for every row inserted in RDBMS using DataStage.

Thanks

harshkohli · Post by **harshkohli** » Tue Mar 30, 2004 3:00 pm

You can use system variable @OUTROWNUM for generating a unique ID.
First store the max(seq_id_column_name from the table) to a variable say var1 and then use var1+ @OUTROWNUM in the mapping.

RamonaReed · Post by **RamonaReed** » Wed Mar 31, 2004 8:02 am

You can use KeyMgtGetNextValue or KeyMgtGetNextValueConcurrent depending on whether you have mutiple processes using the same sequence key, and then if you need to re-set the keys, you can use a command stage with the following command: UPDATE SDKSequences USING DICT VOC SET F1 = '1' WHERE @ID = 'your key name';

rkumar28 · Post by **rkumar28** » Wed Mar 31, 2004 2:48 pm

Hi,
Is there a way to start the UniqueId from 1268265 rather than from one using KeyMgtGetNextValue.

Thanks

RamonaReed wrote:You can use KeyMgtGetNextValue or KeyMgtGetNextValueConcurrent depending on whether you have mutiple processes using the same sequence key, and then if you need to re-set the keys, you can use a command stage with the following command: UPDATE SDKSequences USING DICT VOC SET F1 = '1' WHERE @ID = 'your key name';

chulett · Post by **chulett** » Wed Mar 31, 2004 2:59 pm

Your quote holds the answer. Simply change the F1 = '1' part to F1 = '1268265' and you are set.

raviyn · Post by **raviyn** » Wed Mar 31, 2004 11:08 pm

Think that Sequence Id is better if created at the DataBase level...
Need to be careful if there is any sort of migration , as these conditions are easily handled at the RDBMS level

chulett · Post by **chulett** » Thu Apr 01, 2004 7:37 am

Granted. But there are times when there isn't either the time or the inclination to quote Chapter & Verse on the subject of Better Ways and we simply... answer the question asked.

Besides, I'd rather leave that to Ken most of the time. He's so darn good at it!

raviyn · Post by **raviyn** » Fri Apr 02, 2004 12:24 am

Right, I think I should have Posted with a comment saying suggestion

evanmaas · Post by **evanmaas** » Fri Apr 02, 2004 6:33 am

raviyn wrote:Think that Sequence Id is better if created at the DataBase level...
Need to be careful if there is any sort of migration , as these conditions are easily handled at the RDBMS level

This is right.
Generate a unique sequence in the RDMS. This is use over the different DS projects
Example Orqacle: create a sequence #Sequence_Name#, after that you use this user defines sq voor a new value:
SELECT #Sequence_Name#.NEXTVAL FROM DUAL

Regards,

Erik

DSXchange

Generate the Unique Sequence ID

Generate the Unique Sequence ID

Starting the Unique Id from 1268264 instead of from 1