Surrogate Keys Options

[krystlecsy]

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?

[nivas]

I think instead of using seq file using Hash file is better.

thanks
Nivas

[nivas]

I think instead of using seq file using Hash file is better.

thanks
Nivas

Hi

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

[richdhan]

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

[s_boyapati]

Hi,
Instead of requesting Database to provide maximum number of the key column and then using lookup, we better to use "sequence" object to provide the next value for table population in performance point-of-view.

Sree

[sck]

Hi All,
If I use multiple instances or invocations of the same job then How can I generate Surrogate keys be generated, the what is the way if the Oracle Seq.Nextval not available.

Can anyone suggest on this please

Thanks
SCK