Surrogate key Generator State File Issue

[highpoint]

Hi,


I will describing the process below please let me know where the problem might be:

We have a update surrogate key job which updates the state file with the maximum value in the target table.
Before it updates the statefile, in before job subroutine this job runs a touch command with the state file name
so that if the state file does not exist it creates it.(this is helpful for the first run)

Now i created new job to view the contents of the state file, this job has only surrogate key stage in it.
when i run this job and check in director for its contents:

Surrogate_Key_Generator_0,0: Content of state file /etl/process/dev/cfg/skey_product.ctl:
1 1

Why i am seeing 2 times 1, even though i am running surrogate key generator stage in sequential mode. Is this an issue?

And do you see any issue with the creation of state file using touch command.


NOTE: We are using surrogate key generator in sequential mode.

[highpoint]

EDIT:

I updated the target table to value 85 for the surrogate key column.

And ran the update surrogate key job.

Now i see 4 values in state file.

Surrogate_Key_Generator_0,0: Content of state file /etl/process/dev/cfg/skey_product.ctl:
2 2
86 86

Also noticed that the state file is generating random values instead of sequential values from the last surrogate id in the target table.

Help is really apprciated.

[ray.wurlod]

The state file can not generate random values. What are you actually seeing? Is the remainder of the job running in sequential mode?

[highpoint]

The state file can not generate random values. What are you actually seeing? Is the remainder of the job running in sequential mode?

Sorry for the delayed response.

The portion of job before surrogate key generator is in parallel mode.
And then the surrogate key generator in sequential mode.
And this is connected to a sequential file..

When surrogate key generator is in sequential mode.
Why i am seeing multiple values in the state file as per my post above.

[highpoint]

Appreciate help on this please.

[jwiles]

The state file used by Surrogate Key Generator is composed of multiple rows of two integers, each row indicating a the first and last generated values within a single block of values.

For instance: assuming a block size of 100 values:

1 1 indicates that the value 1 has been assigned and 2-100 are available for assignment.

1 35 would indicate that the values 1-35 have been assigned and 36-100 are available for assignment.

The example you gave of:
1 1
86 86

indicates two different blocks of numbers, one of which has assigned only the value 1, and the other block has assigned only the value 86. I could guess that the block size is 85 based on this.

Why two blocks of data with only one value assigned in each? Two possibilities are:
1) A single job ran the SKG in two partitions, each of which processed one record (each partition is given a different block of numbers to assign from)
2) Two jobs ran at the same time with SKG pointing to the same state file, each job processing one record.

Regards,