Page 1 of 1
How the dynamic lookup is Achieved in Data Stage
Posted: Wed Apr 04, 2007 2:26 am
by vipshiva
Hi All,
Can you please explain me, how the dynamic lookup in Informatica can be achieved in Data Satge Server Edition...
For those who don't know Informatica,
I have a Target Table TGT_TBL. I want to have a lookup against this itself while loading the data into TGT_TBL. How this could be achieved In DS.
Regards,
Shiva.
Posted: Wed Apr 04, 2007 2:40 am
by BalageBaju
Shiva,
Load your target table TGT_TBL into Hash file and do lookup on that.
Posted: Wed Apr 04, 2007 2:44 am
by vipshiva
But will this refresh the hash file as soon as my target table is updated?.....
Posted: Wed Apr 04, 2007 2:57 am
by BalageBaju
Shiva,
Your jobe design should look like this:
Code: Select all
TGT_TBL--------------->Hashfile
|
|
|
Src_Tbl------------------->Transformer----------->TGT_TBL
Each time Datastage will load all the TGT_TBL contents to Hashfile first and then Src_Tbl will do lookup on that hash file.
Posted: Wed Apr 04, 2007 2:58 am
by WoMaWil
Before you start any design you have to analyze what you want.
Sometimes a Sort may help for not need to have a permanent commit.
For to use a Hashfile with commit use an Universe Stage and set commit to 1.
But what is best for your job can only be said when we know more about it.
Posted: Wed Apr 04, 2007 3:20 am
by vipshiva
I think Balaji you are talking about static lookup....
Gerling,
My requirement is as follows
i/p- Sequential file:
eno,ename,sal
101,Siva,10000
102,Prem,2000
101,Siva,9000
o/p- Table:
101,Siva,9000
102,Prem,2000
Since the duplicate records are coming in the same file with different values,
First time
101,Siva,10000 -will be inserted
Second time
101,Siva,9000 -Should be updated... by looking the Target table itself....
Posted: Wed Apr 04, 2007 3:36 am
by WoMaWil
Shiva,
if you fill your data into a hash-File with eno or/and ename as key, then the hash file does what you want withouth any update, because always the last wins.
That is a standard-Feature of a hash-file, in your case fine for you.
Posted: Wed Apr 04, 2007 3:56 am
by vipshiva
I have given just an example......this will work fine....
But this will not work as Informatica's Dynamic lookup...
Any other solution..... Wolf
Posted: Wed Apr 04, 2007 5:48 am
by WoMaWil
Why do you need to solve your problem the way INFORMATICA does?
Why do you worry if you have a solution which solves your problem?
Be happy and don't worry!
And if you don't believe that this solves your problem give me a scenario!
Posted: Wed Apr 04, 2007 7:43 am
by vipshiva
o.k thank you wolf...
If I get like that scenario I will post the query :D