Fail trying to re-initialize a surrogate key value

[jusami25]

Hi all!

Let me try to explain the problem we have...

We are trying to generate surrogate keys in our architecture, consisting in different jobs that loads different tables.
The client requirement is that the keys needs to be different in all the system, so all the jobs will share the same key generator.

Our idea is to generate the keys it using the Surrogate Key tab in the transformer stage. So...

1. We created a job with a Surrogate Key Generator that will create the state file that stores the keys, and to be used in the transformer.
2. We configured the differents transformers to use that file, initializing it with the value 1.

So far so good! The problem comes here...

Sometimes, we need to reset the key, so we created a job with one Row Generator pointing to one Surrogate Key Generator, to re-initialice it. After executing this job, if we execute any of our jobs that use the file, the first row will take the initialized value... but the next ones will continue to use the old value!

Let me put an example:

- The current key its 50.
- We execute one job and loads 10 records, with the keys 51 to 61.
- We try to re-initialize the state file to 1.
- We execute again the jobs to load 10 records, and the key values are 1,62,...,70 instead of 1,2,..,10 !

Looking at the state file, we have see that we have two lines of numbers, for example:

50 50
1 1

After lurking this forum, i've found one thread where it explains why some times there are two lines and so on, but its not our case, because we execute the 're-initialize job' alone.

So... any one knows why our 'reset surrogate key' jobs ADDS a new line to the state file instead of initilaice it?

Thanks in advance!

[boxtoby]

Hi there,

Have you tried launching a simple unix script to delete and recreate the surrogate key file?

Cheers,
Bob.

[boxtoby]

Hi there,

Have you tried launching a simple unix script to delete and recreate the surrogate key file?

Cheers,
Bob.

[ankursaxena.2003]

I also had the same problem. But, there was no answer to that. So, I came up with a solution.

I am using a Surrogate Key Generator which is using a State File. And run the Surrogate Key Generator on Single Node. Otherwise, you will skip few numbers.

Re-Initializing the State File:
I built a job which will first delete the old state file and then I create the state file in the same job. So, now your new state file will have only one record starting from 1.

I hope this help.