Surrogate Key State File Update problem

[tsamui]

Sometimes, the State File may be corrupted or deleted. So I need to create a set of jobs which update the state file with the proper value.
To achieve this I have created 3 jobs.
1-> Delete the existing State File -- this is working fine.
2-> Create the State file -- this is also working fine.
3-> Update the State file with the maximum surrogate key value from the target table.
SELECT MAX(SKEY_FIELD) FROM SKEY_TABLE
Currently the table has maximum surrogate key value of 300001. So the update job is updating the state file with 300001.

But when we are running the load job (to load SKEY_TABLE table) which uses that updated state file to generate surrogate key, giving strange output.
In one node (we have two node configurations) surrogate key is starting from 300002 (which is according to our requirement). But in the 2nd node surrogate key is starting from 157(it is strange).
So it will not fulfill the requirement as surrogate key 157 may exist in the target table and again loading of 157 will result duplicate in the target.

Any suggestion, how can I generate the surrogate keys only from the previous maximum surrogate key value (in my case from 300002)?


Thanks
Tsamui

[ray.wurlod]

In theory what you are describing is not possible. You can not get a key value of 157 out of a newly-created state file that has been initialized to 300001. Therefore what you are experiencing may be a bug. Report it to your official support provider and ask whether it's a known issue and, if so, whether there is a patch to fix it.

You should probably prefer

Code: Select all

SELECT MAX(SKEY_FIELD) + 1 FROM SKEY_TABLE;

because MAX(SKEY_FIELD) already exists in the table.