How to refresh a lookup in every job iteration

yserrano · Post by **yserrano** » Thu Jan 31, 2008 2:00 pm

Hello all, please excuse me if this is a newbie question, I am actually new to DataStage development.

Is it possible to refresh a lookup to reflect inserts done to the lookup table within the same job?
My job writes records to a table X, It is capable of insert new records, insert new versions and update old versions of existing records. The lookup to identify existing records and new versions is done to the same table X. The problem arises because the input data may have several versions of the same record inside.

This question was probably asked before here, please point me out the right keywords to include in a search for similar situations.

Thank you

Yonny Serrano

chulett · Post by **chulett** » Thu Jan 31, 2008 2:42 pm

Your reference lookup is done to an actual database table or a hashed file?

yserrano · Post by **yserrano** » Thu Jan 31, 2008 2:46 pm

to a database table
(the same table I am inserting and updating records into)

chulett · Post by **chulett** » Thu Jan 31, 2008 3:18 pm

That makes it problematic as you'd need to commit every record to make them available to the lookup. A hashed file solution is simple - write the new records to the same hashed file and don't cache the lookup.

yserrano · Post by **yserrano** » Thu Jan 31, 2008 4:06 pm

mmm...

it seems that I'll have to sign up for the Premium Membership to see your complete answer.
Let me see if my boss wants to authorize that expense.

Regards,

YS

chulett · Post by **chulett** » Thu Jan 31, 2008 4:27 pm

You couldn't have missed much... check again.

However, the 'expense' is well worth it. Not only are you helping support the site that helps support you, but from what I'm told there's more goodies on the way for Premium Members. Not only that, but you'd get full access to all of the Wurlod Goodness available here!

SHARAD123 · Post by **SHARAD123** » Sun Feb 03, 2008 12:36 am

hi,
y cant u try using a stage the update the lookup table after every iteration or b4 the lookup actually starts.

That should be an easy way i guess....

jus revert if there is anyother idea

yserrano · Post by **yserrano** » Mon Feb 04, 2008 12:55 pm

I am working right now on the "Hash-Files" approach, and it seems like it is going to work. But I am curious about SHARAD post.
It would be nice if he (she) could develop a little more into his (her) idea.

This is how my original server job looks like:

BTW, Thank you for your help.

chulett · Post by **chulett** » Mon Feb 04, 2008 1:10 pm

The hashed file approach is bog standard DataStage Server stuff. Replace your two ODBC lookup stages with hashed file stages (and build something to do the initial population, if needed) and you'll be fine. As noted, make sure the hashed lookup is either not cached (my personal preference) or set to 'Locked for updates'.

As to the SHARAD123 suggestion, right now it is fairly incomprehensible. I too would be curious as to what the actual suggestion would be, once clarified.

ray.wurlod · Post by **ray.wurlod** » Mon Feb 04, 2008 4:46 pm

Not cached AND set for "lock for updates" would be my suggestion.