Surrogate Key issue

prem84 · Post by **prem84** » Mon Jun 27, 2011 7:11 am

Hi
I used a surrogate key generator stage. My job consists of

Flat file -- > Surrogate stage --> Flat file

Input file consists of name and I have to assign a sequence number to it.
Input file
------------
xxxx
yyyyy
zzzzz
ccccc

I created a state file using UNIX touch command and in the surrogate key generator stage
I used file initial value to 1 and file block size as user specified set to 1.When I run it first time
I get the following data out
1 xxxx
2 yyyyy
3 zzzzz
4 ccccc
And the state file is also updated. I have restricted node map constraint to one node. But when I run the second time I have sequence value starting from 6 and not 5

6 xxxx1
7 yyyyy1
8 zzzzz1
9 ccccc1

And same problem for subsequent runs

11 xxxx2
12 yyyyy2
13 zzzzz2
14 ccccc2

Last value is getting skipped.
Kindly advice on this issue

ray.wurlod · Post by **ray.wurlod** » Mon Jun 27, 2011 3:19 pm

Keys are allocated in blocks. If not all keys in a block are used (due to slight variations in the number of rows per partition) they are discarded.

That DOES NOT MATTER. Surrogate keys provide only uniqueness. They are not - and should not be - guaranteed to be contiguous.

mhester · Post by **mhester** » Mon Jun 27, 2011 4:34 pm

And if your desire is to have a contiguous value then the SK generator is not what you want to use anyways.