Better for Surrogate Key Generation? Dstage or DB procedure

[rggoud]

Hi,

Currently I'm having the surrogate key generation logic in datastage. The jobs queries the max Surrogate Key from the database and increments for every new source dimension records. One of my collegue is debating that if we put the surrogate key generation logic into the database stored procedure so that the load jobs will get some increased performance . What is the best practice to generate surrogate key ? In Database or in Datastage. I somehow feel Datastage should be used to handle any data management and database be should for data access/storage. I need pros and cons for these both options. This topic might be discussed long back, but i'm unable to locate those easily. Thanks.

Raj.

[ray.wurlod]

Main considerations, it seems to me, are

• whether other, non-DataStage, updates may be made to the table (in which case use the database rather that DataStage)
  
  whether the bulk loader (if you're using one) can make use of database-generated keys - if not, you need to generate them in DataStage