DSXchange

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

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

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

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.

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

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!

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

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.

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.

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