- Surrogate Key Generation In Datastage Parallel Job Application
- Surrogate Key Generation In Datastage Parallel Jobs
Generating surrogate keys. To generate surrogate keys, add a Surrogate Key Generator stage to a job with a single output link to another stage. If you want to pass input columns to the next stage in the job, the Surrogate Key Generator stage can also have an input link. Jun 14, 2011 Generate ROW NUMBER (or row id) Column in a DataStage job Posted on June 14, 2011 by Ivan Georgiev Sometimes in DataStage it is necessary that you assign consecutive numbers (e.g. 1, 2, 3, ) to rows. AFAIK, you'll have to take another tack for SQL Server, either just let DataStage generate them (if that is a viable option for you) or use another mechanism to access the surrogate. From what I recall, a sparse lookup could be done to 'get' the next value. But I'm sure people will chime in with other thoughts.
In this post, We will see how to generate surrogate key for data, where we have to use surrogate key stage.A) Design :
Below design is a demo design of job. Here our data source is a row generator which is generating rows. In real time scenario, Source can be a flat file, DB stages, Passive Stage or can be a Active stage also.
In Row Generator Stage, we are generating a col 'Name'.
Surrogate Key Generation In Datastage Parallel Job Application
B) Surrogate Key Stage Properties :In Surrogate Stage, fill all the properties of Surrogate Key Stage like below...
Generated Output Column Name = Skey
Source Name = <the path of Surrogate State File (which we generated) >
Source Type = Flat File
File Block Size = User specified
User Specified Block Size = 1 ( by this we can control the gaps in surrogate keys )
C) Output Column Mapping :
D) OutPut File :
Below I have attached output file which have surrogate keys generated with data.
More post in Series :
Surrogate Key Generation In Datastage Parallel Jobs
I hope, Now you got how to generate the Surrogate Keys for the data without having headache of management of keys. But there is still a scenario what if our state file got corrupted or deleted ? We will discuss this in next post.....Keep Reading.....
Like the Facebook Page & join Group
https://www.facebook.com/DataStage4you
https://www.facebook.com/groups/DataStage4you
https://twitter.com/datastage4you