Doubt in Surrogate key

[somu_june]

Hi,

Iam generating a keys using surrogate key . My actual requirement is

Ex: If Iam running the same job twice then I have to generate keys for first run as 1......70 . Its working fine. But If I want to run the job for second time with different file I want to generate key starting with 71.....so on. Is there any option like that in surrogate key stage.



Thanks,
somaraju.

[meena]

Hi Somaraju,
I am new to datastage. But I can give you a idea how to do it, well lets try it. It may work. I want to know what are the stages you are using in the DS job design. If you are using a Oracle stage in your design then you can use "sequencename(columnname).NextVal" and assign 71 in Insert.

Meena

Hi,

Iam generating a keys using surrogate key . My actual requirement is

Ex: If Iam running the same job twice then I have to generate keys for first run as 1......70 . Its working fine. But If I want to run the job for second time with different file I want to generate key starting with 71.....so on. Is there any option like that in surrogate key stage.



Thanks,
somaraju.

[somu_june]

Hi meena,

Iam not using any oracle stages. Actually Iam generating seq of numbers and writing to a file. Iam getting a date from flat file and Iam grouping by date and generating a surrogate key for that group and loading that number like DMT1,DMT2,........DMt79 to seq file.



Thanks,
Somaraju

[aramachandra]

Surrogate key generator is not persistant in px jobs.

It expects you to remember your last generated number.

YOu could write last sequence into a seqencial file , so in your case you will write 70 to a sequential file.

On the next run you will read it from the sequential file and pass it as a paremeter to your job which has the surrogate key and use that as a starting seed.

You will have to use sequences to pass parameters, but the above is one way.

I am sure there are a few other ways as well

arvind

[somu_june]

Hi Arvind,


Can you explain me how to make a parameter the value and how to pass that parameter when Iam running the same job again.

Can any one tell me is there any other way than this.



Thanks,
Somaraju

[kumar_s]

Hi,
During each run, find the max of the key which got generated. As explained, write the that value to another file with any identifier. Using the edentifier pick up the lates one and pass as a paramter to a job. You can use Batch code to perform this. Or you can also use execute command activity to read (cat) the file (from the sequence) ans pass as paramter to the next statage.
In surrogate Key Generator, a option called 'Start Value' is available. It can be parameterized. Pass the parameter with thevalue '70' in your case to the start value.

-Kumar