Incrementing surrogate key
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 57
- Joined: Tue Jun 30, 2009 9:38 am
Incrementing surrogate key
Hi,
I have a flat file which will be loaded into DB tables
everyday.Suppose for example, if I get 10 records today in the
flatfile,I need to assign a Surrogate Key to these records and
load it into the DB table.So next day, when I get new set of
records I need to track the highest Surrogate Value in the DB
table and then assign surrgate key values to these new records
starting from the highest value.
I am new to data Stage and am struggling to implement this
logic.Can any one of you give explain how it can implemented in
Server Edition?
I have a flat file which will be loaded into DB tables
everyday.Suppose for example, if I get 10 records today in the
flatfile,I need to assign a Surrogate Key to these records and
load it into the DB table.So next day, when I get new set of
records I need to track the highest Surrogate Value in the DB
table and then assign surrgate key values to these new records
starting from the highest value.
I am new to data Stage and am struggling to implement this
logic.Can any one of you give explain how it can implemented in
Server Edition?
-
- Premium Member
- Posts: 457
- Joined: Tue Sep 25, 2007 4:05 pm
Re: Incrementing surrogate key
You could also use the Key Management utility routines provided by DataStage. Look for "KeyMgmt..." routines (unsure about the name) and you need to provide a parameter to them (a unique one which will keep track of the previous SK values generated).
Vivek Gadwal
Experience is what you get when you didn't get what you wanted
Experience is what you get when you didn't get what you wanted
Re: Incrementing surrogate key
Use the built-in transform, KeyMgtGetNextValue('YourSequenceName'). In case there is concurrent writing to the same table, you could use KeyMgtGetNextValueConcurrent('YourSequenceName')
gateleys
Surrogate Key
Design the job as shown below:
------------------Surrogate table
FlatFile -------> Lookup ---------->Transformer---------> DB2
............................... ..................... Aggregator--------->Surrogate Table
In the lookup stage ,lookup the table_name(target table name) field and retrieve the Max_val(from surrogate table)for that table_name.
The transformer stage will load data to the target db2 table through one link where it will generate unique key values for the primary key using @rownum+1 function and through the other link max_val data for that particular target table_name will be loaded to the surrogate table via the aggregator stage .In the aggregator stage get the maximum value for the surrogate/primary key field.
------------------Surrogate table
FlatFile -------> Lookup ---------->Transformer---------> DB2
............................... ..................... Aggregator--------->Surrogate Table
In the lookup stage ,lookup the table_name(target table name) field and retrieve the Max_val(from surrogate table)for that table_name.
The transformer stage will load data to the target db2 table through one link where it will generate unique key values for the primary key using @rownum+1 function and through the other link max_val data for that particular target table_name will be loaded to the surrogate table via the aggregator stage .In the aggregator stage get the maximum value for the surrogate/primary key field.
-
- Participant
- Posts: 246
- Joined: Mon Jun 30, 2008 3:22 am
- Location: New York
- Contact:
Re: Surrogate Key
natashab wrote:Design the job as shown below:
------------------Surrogate table
FlatFile -------> Lookup ---------->Transformer---------> DB2
............................... ..................... Aggregator--------->Surrogate Table
In the lookup stage ,lookup the table_name(target table name) field and retrieve the Max_val(from surrogate table)for that table_name.
The transformer stage will load data to the target db2 table through one link where it will generate unique key values for the primary key using @rownum+1 function and through the other link max_val data for that particular target table_name will be loaded to the surrogate table via the aggregator stage .In the aggregator stage get the maximum value for the surrogate/primary key field.
Thought it was a server job...
Arun
Yah, ASCII Art here can be a little challenging as the forum software automagically removes whitespace from everything that is not wrapped in code tags. So what you need to do is use the code tags and then liberally Preview what your output is going to look like in order to get it right. It won't line up like you think it will due to the proportional font used here, so it will need to look a little goofy on your side to line up after it has been posted.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers