About the only way to guarantee you have no gaps in a sequence number assignment using a formulaic method like this is to run the stage in sequential mode. Even though Round Robin partitioning gives you as-close-to-possible even distribution among the partitions, that's only possible when the number of records is evenly divisible by the number of partitions and how often does that occur naturally in parallel data processing?
Surrogate Key Generator stage using a static file with a block size of 1 would work in a parallel environment, but that blocksize will create a major bottleneck to processing (I've seen it done). I would prefer running a sequential stage rather than SKG with a blocksize of 1. A database sequence MIGHT give better performance but I haven't tried it.
The question to ask is: Must you absolutely have no gaps in the assigned sequence number? If so, and you're reading from a sequential file (and not in parallel), you could assign record numbers in the sequential file stage.
Regards,
- james wiles
All generalizations are false, including this one - Mark Twain.
