DSXchange

Hi,

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

Thanks

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.

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';

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';

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

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

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!

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

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