Reusable ODBC Lookups.

deepak.shanthamurthy · Fri Apr 14, 2006 4:24 pm

Hi,
Is there any way where i could reuse an odbc lookup other than using it as a hash file.??

ray.wurlod · Post by **ray.wurlod** » Fri Apr 14, 2006 4:57 pm

Not sure what you mean.

The results from a lookup, no matter what stage type is on the other end of a reference input link, is a row containing either data or nulls. (From an ODBC stage or a UV stage it might be more than one such row.)

Columns from these rows can be directed onto the output of the Transformer stage in the same way as columns from the stream input link and are thus accessible for "re-use" downstream.

If that doesn't answer your question, please post clarification of what you meant.

You might also research the use of shared containers if you are on version 5.2 or later.

deepak.shanthamurthy · Fri Apr 14, 2006 5:09 pm

No what i meant to say was that whether i would be able to build a query result set and have it reused to do multiple lookups without having to build the query result set every time i do a lookup.....

kcbland · Post by **kcbland** » Fri Apr 14, 2006 10:15 pm

That would be why hashed files have neat things like caching and such. It permanently records a dataset for repeated high-performance reference capabilities.

ray.wurlod · Post by **ray.wurlod** » Sat Apr 15, 2006 3:32 pm

Ken outlined the advantage of a hashed file.

As to whether ODBC can avoid regenerating the result set, it really depends on the database server, and is not under control of DataStage. The technique used is "prepared SQL", where a query containing parameter markers is sent by DataStage to the database server, which may or may not generate a result set containing every possible key. If it does, then your wish is fulfilled. Most, however, will not but will, instead, rely on an index on the key to provide the required row quickly.

vinaymanchinila · Post by **vinaymanchinila** » Mon Oct 08, 2007 1:59 am

Can we have multiple reference look ups where lookup1 result can be used in look up2 in the same transformer?

i.e result of look up1 is the key for lookup2

Thanks,

ray.wurlod · Post by **ray.wurlod** » Mon Oct 08, 2007 4:34 am

Etiquette Note
Do not hijack threads. This thread is nearly 18 months old. Start a new thread where you have a new question. Search the forum first; you question may already have been answered. Further, your question does not relate to the subject of this thread.

Once you have created a new thread your question will be answered in that thread.