parallel key_generation() and Key_Generation

dataservices job design
1

Hi,

I found a strange thing today. I picked up a Dataflow that was duplicating surrogate keys. When I looked, the developer had done a ‘manual’ SCD Type 2 / History Preservation but without using the built in transforms.

On New inserts, the used the Key_Generation transform.
On Update inserts, he has the key_generation() function on the same table.

It seems that when there are both inserts and updates to process, these 2 calls generate the same numbers. When I saw this my first thought was that this would happen, but it does beg the question; when there are many Key Generations for the same table in the same Dataflow, do they generate unique keys? Also the same question applies to larger scopes; same workflow, same job, many jobs.

Does anyone have a comprehensive answer for this?

Any help appreciated!

philmorris :uk: (BOB member since 2002-11-12)

2

The key generation process happens exactly once using a SELECT MAX(). So it doesn’t know if some other process is also adding rows to the table.

eganjp :us: (BOB member since 2007-09-12)

3

Hi again,

Having explored this further, it seems there is absolutely no co-ordination between any number of key generation transforms/functions, wherever they appear.

So, I guess my next visit is to the ‘requested enhancements’ section… :nopity:

philmorris :uk: (BOB member since 2002-11-12)

4

I think the only way to make this possible, is to let the database platform generates the key.

For SQL Server, this could be an IDENTITY type, and MySQL Auto Increment.

H. Westerhof :netherlands: (BOB member since 2010-03-26)

5

[quote:27dd9fcf99=“H. Westerhof”]I think the only way to make this possible, is to let the database platform generates the key.

For SQL Server, this could be an IDENTITY type, and MySQL Auto Increment.
[/quote]

cha (BOB member since 2009-01-06)