I have tried searching throughout this forum on Surrogate Keys but difficult to find something on DataStage EE. Would just want to hear from all of yr experiences in generating Surrogate Keys. Here are the alternatives I have identified in populating Surrogate Keys:-
1. Using the Surrogate Key Generator stage in DataStage. I find this key generated is only unique in that particular batch and now throughout the entire table.
2. Using the Oracle SEQUENCE.NEXTVAL. This is accessing the Sequence object to populate the Surrogate Keys into the table.
I would want to know whether is storing the Sequence number in a temporary text file would work as well? During the load, then read the number from the text file and increment according to the records. Have anyone done Surrogate Key generation this way? Please advise what are the stages involved?
Surrogate Keys Options
Moderators: chulett, rschirm, roy
Re: Surrogate Keys Options
I think instead of using seq file using Hash file is better.
thanks
Nivas
thanks
Nivas
Re: Surrogate Keys Options
Hinivas wrote:I think instead of using seq file using Hash file is better.
thanks
Nivas
I am sorry. I am wrong. Please check the forum question and ans which was posted on June 22nd. Priviously I got the ans.
thanks
Nivas
Hi,
Use the column generator with 2 columns one used for lookup and other for generating sequence number, the lookup stage to get the maximum key from Oracle table, the transformer stage variable to add the maximum value from the lookup stage with sequence number from column generator stage. You will end up with surrogate keys.
HTH
--Rich
Think about what you are thinking because as a man thinks so does he become
--Joyce Meyer
Use the column generator with 2 columns one used for lookup and other for generating sequence number, the lookup stage to get the maximum key from Oracle table, the transformer stage variable to add the maximum value from the lookup stage with sequence number from column generator stage. You will end up with surrogate keys.
HTH
--Rich
Think about what you are thinking because as a man thinks so does he become
--Joyce Meyer
-
- Premium Member
- Posts: 70
- Joined: Thu Aug 14, 2003 6:24 am
- Contact: