Sequence number using Surrogate Key in transformer

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
Vrisha
Premium Member
Premium Member
Posts: 60
Joined: Sat Jul 15, 2017 9:32 pm
Location: Texas,USA

Sequence number using Surrogate Key in transformer

Post by Vrisha »

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.
Suja
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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.
-craig

"You can never have too many knives" -- Logan Nine Fingers
Vrisha
Premium Member
Premium Member
Posts: 60
Joined: Sat Jul 15, 2017 9:32 pm
Location: Texas,USA

Post by Vrisha »

Thanks for your reply, Craig.
Suja
priyadarshikunal
Premium Member
Premium Member
Posts: 1735
Joined: Thu Mar 01, 2007 5:44 am
Location: Troy, MI

Post by priyadarshikunal »

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
Priyadarshi Kunal

Genius may have its limitations, but stupidity is not thus handicapped. :wink:
Vrisha
Premium Member
Premium Member
Posts: 60
Joined: Sat Jul 15, 2017 9:32 pm
Location: Texas,USA

Post by Vrisha »

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
Suja
R.K.Glover
Participant
Posts: 8
Joined: Mon Mar 11, 2013 2:51 pm
Location: RTP, North Carolina

Post by R.K.Glover »

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.
Vrisha
Premium Member
Premium Member
Posts: 60
Joined: Sat Jul 15, 2017 9:32 pm
Location: Texas,USA

Post by Vrisha »

Thanks for your reply, Glover.
Suja
Post Reply