Surrogate Key Generator Stage---How It works ?

[arijitghosh21]

I have designed a job as below
1) Source stage => Sequential file which is reading 3 columns as A,B,C.
2) Intermediate stage => Surrogate key generator stage where I am generating Column D. Here I am using Oracle Database sequence.
3) Target stage => Sequential File to capture all the columns A,B,C,D.

I am running this job using 2 node configuration file for 38 records. Below is my observation

Before running the job
-----------------------
LAST_NUMBER of database sequence is => 121

After running the job
-----------------------
LAST_NUMBER of databse sequence is => 161


Cross validation
----------------------
After checking the target file I found that

minimum value of column D generated by surrogate key generator stage is => 121 ( Based on above info it is supposed to be 122, i.e LAST_NUMBER +1 )
maximum value of column D generated by surrogate key generator stage is => 158 ( Based on above info it is supposed to be 159 )


Why this kind of value mismatch in Database Sequence and target file.
Also Please clarify the concept behind surrogate key generator stage using DB sequence, I mean how the file value and sequence value gets updated etc etc.

[blewip]

Depending which SK Stage you use and you get two different ones even with 8.5, the numbers are allocated to partitions in chunks.

Therefore each partition goes through the numbers in the chunk and once finished asks for a new chunk. Therefore when comparing the numbers in each partition, it will produce perceived oddities

[arijitghosh21]

Hey, I am using simple surrogate key generator stage only available in 8.1. Could you please elaborate the concept with more explanation.
And alos it sould be better if you tell me, Is the approach fine ? If not how can i resolve the issue ?

[blewip]

I don't think there is a problem, the holes in the numbers will be inserted next time the table is loaded.

To resolve run sequentially

[Nagaraj]

Or you can run parallely, Just make sure that wherever you are calling the skey in the job, use the round robin partioning method so that you atleast get near sequential flow numbers.

Group any other thoughts are welcome. we need more insight into this concept.

the holes in the numbers will be inserted next time the table is loaded.

btw are you sure the holes will be filled up later?