DSXchange

HI THERE
I have to handle type 2 dimension in dim table can u let me know how datastage handle it
thanxs
samra

Search for the topic slowly changing dimension you will find many threads with that discussion in this forum.

Thanks
Siva

Search is your friend because, as you may imagine, this topic has been discussed quite a bit on the forums.

For starters, here is one possible starting point.

Design both the population of dimension tables, and the subsequent population of fact tables. Design, too, the business rules that determine which columns are significant in determining that a change has occurred and which are not (for example, you usually don't keep old telephone numbers).
Leverage DataStage's hashed file technology to perform the "reverse lookups" from natural key to surrogate key in each case.

You can follow this. This is one of the approches we follow.

1. There will be records for Update ( That needs to be a changed record of the existing record) and there will be new records also.

2. For insert records look up into the target table and see if the exact record exists, if it exists then do not process it or reject it. If it does not exist then insert it into the targer with BEG_DT as CURRENT_DATE and END_DT as a distant future dat such ass 9999-12-31.

3. For Update records look up into the target table and see if this record has undergone any change. If yes then Update the existing record in target by updating the END_DT of existing record as CURRENT_DT. And insert a new record with new SUROGATE_KEY (if any) and BEG_DT as CURRENT_DATE and END_DT as a distant future dat such ass 9999-12-31.

Please lemme know if the process is clear to you.

Thanks