Multiple Instance Surrogate Key Generator

[altruist]

Hi,

Can I run multiple instance of jobs to generate surrogate key generator with SAME State File i.e. jobs to run simultaneously and generate common surrogate key

[ArndW]

Yes.

[abhinavagarwal]

Hi

Technically yes you can run multi instance job having surrogate key gen stage.

But functionally, if you want to use the same starting point for the key then okie, otherwise if you have to think for the logic which will be providing the correct start point for every instance.

Cheers.

[vmcburney]

By default when you are using a state file for surrogate keys each job will retrieve the latest number and increment it one row at a time. For multiple instances jobs and multiple partitions sharing a state file this will end up causing slow processing times due to file locking I/O bottlenecks on that file. Add the optional property "File Block Size" and set it to a big number like 10000 - this means each job will grab a block of 10000 surrogate key numbers at start up and increment the file. There will be less frequent updates to the file.

[altruist]

Thanks a lot Vincet for the detailed explanation

[SURA]

Do you wish to have an unique SK across DW?

I tried this approach in 8.5 and the file got corrupted at one stage (few times). I would recommend not to use file, rather than you could use table, if it is multiple instance.