KeyMgtGetNextValue

[dwandbi]

Hello,
How would you reset surrogate key value generated to load target table from stating number say '1' again? What's exact routine in Before job sub-routine or command used in DS Adminstrator?

thanks,
Tom

[kcbland]

There isn't one. Write a job that does a SELECT MAX(yourkeycolumn) FROM yourtable and puts the result into the appropriate hashed file used by the function.

[gateleys]

That depends on how you generated your surrogate key. If it was generated using Sequence in Target database, then you would follow Kenneth's suggestion. However, if you used the KeyMgmt utility provided in DS, then you can read the SDKSequences into a hashed file with 2 fields (SequenceName as key, and NextVal), and then reset the desired Sequence's NextVal in the transformer. If you want to update it from Administrator command, it has been discussed many times. Search for UPDATE SDKSequences USING DICT VOC. I could write the whole command, but just want you to do a little work.

gateleys

[jreddy]

If you dont want the sequence value to continue from previous load and if you want it to start from 1 for every load job run.. then maybe you could use @INROWNUM or @OUTROWNUM depending on your job design, rather than have to reset the keyMgmt value every time. My 2 cents

[avi21st]

I suggest you search the forum for your topic. Generally we update the SDK sequence hashed file with a seperate job. The structure of the SDK Sequence is that it hads the Sequence name and the present value stored. So a Datastage job can take it in a Hash file and update it. Or run this command from Administrator:

Code: Select all

UPDATE SDKSequences USING DICT VOC SET F1 = '1' WHERE @ID = 'Sequence Name';

I have seen several post by top posters on this topic- do search a while.