DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
This topic has been marked "Resolved."
Author Message
Vrisha



Group memberships:
Premium Members

Joined: 15 Jul 2017
Posts: 55
Location: Texas,USA
Points: 650

Post Posted: Tue Aug 29, 2017 2:00 pm Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Windows
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

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42273
Location: Denver, CO
Points: 217068

Post Posted: Tue Aug 29, 2017 3:33 pm Reply with quote    Back to top    

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

_________________
-craig

Watch out where the huskies go and don't you eat that yellow snow
Rate this response:  
Not yet rated
Vrisha



Group memberships:
Premium Members

Joined: 15 Jul 2017
Posts: 55
Location: Texas,USA
Points: 650

Post Posted: Wed Aug 30, 2017 7:00 am Reply with quote    Back to top    

Thanks for your reply, Craig.

_________________
Suja
Rate this response:  
Not yet rated
priyadarshikunal



Group memberships:
Premium Members

Joined: 01 Mar 2007
Posts: 1735
Location: Troy, MI
Points: 9315

Post Posted: Thu Aug 31, 2017 2:56 am Reply with quote    Back to top    

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
Rate this response:  
Not yet rated
Vrisha



Group memberships:
Premium Members

Joined: 15 Jul 2017
Posts: 55
Location: Texas,USA
Points: 650

Post Posted: Thu Aug 31, 2017 7:46 am Reply with quote    Back to top    

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
Rate this response:  
Not yet rated
R.K.Glover
Participant



Joined: 11 Mar 2013
Posts: 7
Location: RTP, North Carolina
Points: 45

Post Posted: Thu Aug 31, 2017 12:40 pm Reply with quote    Back to top    

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.
Rate this response:  
Not yet rated
Vrisha



Group memberships:
Premium Members

Joined: 15 Jul 2017
Posts: 55
Location: Texas,USA
Points: 650

Post Posted: Fri Sep 01, 2017 7:14 am Reply with quote    Back to top    

Thanks for your reply, Glover.

_________________
Suja
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours