DSXchange

Hi ,
My requirement is to populate the sequence number from 1 to the column 'SK'.
For eg- today we have 10 records and sk column should have '10' as value for the last record inserted. Next day when 5 records are appended to the database , next SK value should be 11 to 15.

I have used 'Surrogate Key' option in Transformer and mapped SK( output column name) to NextSurrogateKey().

Input table has 801 rows. Output has 801 rows but SK column starts with 1 and ended with 1401 which is incorrect. Random number is taken by the SK.

I have selected 'System selected block size' for New Surrogate keys retrieved from Stat file. Initial value - 1

What is the mistake I am doing? How should get next value without random increment? Please let me know.

It's not a random number.

If your requirement does not allow any gaps in the sequence number then you cannot leverage anything related to Surrogate Keys for this as they will have numbering gaps as 'missing' or skipped keys are a normal part of surrogates. Now, there may be a way to get it to do that for you provided you run the job on a single node / sequentially, once you add multiple nodes into the equation all bets are off. Others will have to confirm or deny that, however.

Sounds like you may need to handle this manually by finding the starting point in the job by first gathering the MAX(sequence) and then adding 1 to it for every record through the job. Another trick I've seen is to take that MAX value as a static parameter and then add the 'record number' to it, leveraging a system variable like @INROWNUM. So when your max is 10, the first record gets 10+1, the second record gets 10+2, etc.

Now if your "SK" column is actually a real, honest to goodness surrogate key, then gaps in the numbering should not be an issue. FYI.

Thanks for your reply, Craig.

Seems system selected block size is taken as 1000 hence 1 to 400 and 1001 to 1401 has been generated.

You can change the block size to 1 to see it being generated in a compacted manner, However it has performance implications.

Surrogate key is just a unique number and should not have such requirements imo

Spoke with the architect regarding this surrogate key generation. They are ok with that as they are unique , eventhough not in sequence.

So I am going ahead with Surrogate key generation in Transformer.

Thanks for your support , Priya and Craig

If you absolutely must have sequential numbers (and it doesn't sound like you do), then you should probably tie it to something inside the target database itself (e.g. built-in key generator? rowid?), or grabbing the max(key_field) from the table in question, and using that as a feed to increment it.

But that's such a waste. Surrogate Keys need to be unique; they don't need to be anything else. There shouldn't be anything that ties them directly to the data of a table, including the order the records were initially inserted.

Thanks for your reply, Glover.