How the dynamic lookup is Achieved in Data Stage

vipshiva · Post by **vipshiva** » Wed Apr 04, 2007 2:26 am

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.

BalageBaju · Post by **BalageBaju** » Wed Apr 04, 2007 2:40 am

Shiva,

Load your target table TGT_TBL into Hash file and do lookup on that.

vipshiva · Post by **vipshiva** » Wed Apr 04, 2007 2:44 am

But will this refresh the hash file as soon as my target table is updated?.....

BalageBaju · Post by **BalageBaju** » Wed Apr 04, 2007 2:57 am

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.

WoMaWil · Post by **WoMaWil** » Wed Apr 04, 2007 2:58 am

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.

vipshiva · Post by **vipshiva** » Wed Apr 04, 2007 3:20 am

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....

WoMaWil · Post by **WoMaWil** » Wed Apr 04, 2007 3:36 am

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.

vipshiva · Post by **vipshiva** » Wed Apr 04, 2007 3:56 am

I have given just an example......this will work fine....
But this will not work as Informatica's Dynamic lookup...
Any other solution..... Wolf

WoMaWil · Post by **WoMaWil** » Wed Apr 04, 2007 5:48 am

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!

vipshiva · Post by **vipshiva** » Wed Apr 04, 2007 7:43 am

o.k thank you wolf...

If I get like that scenario I will post the query :D