Teradata Enterprise stage dynamic lookup

AKUMAR21 · Post by **AKUMAR21** » Thu May 15, 2008 11:39 am

My TD Enterprise stage is acting as a source that queries a couple of tables using joins. These tables are getting parallelly updated by other jobs.

The records queried by the TDE stage pass through a lookup stage and based on a particluar column value the matching records are inserted/updated to the target table via TPUMP stage.

As and when the source tables get updated in the real time process the lookup needs to be refreshed i.e. the query in Teradata Enterprise stage
should be proccessed again. Is there any option to do so. Do we have any APT parameters that would help in this scenario?

throbinson · Post by **throbinson** » Thu May 15, 2008 11:48 am

What is the source stage of the look-up? Dataset, fileset, Flat File, etc.? You appear to be saying that the source of the lookup is static, therefore it isn't a DBMS stage of some kind. What it is?

keshav0307 · Post by **keshav0307** » Thu May 15, 2008 8:25 pm

your problem is not very clear to me.
how do you expect a record which was already extracted and is in the datastage server, to be updated automatically which is updated in the database.
the updated records after the extract will be updated only on the next run.

vmcburney · Post by **vmcburney** » Thu May 15, 2008 8:30 pm

Teradata is one type of database that does not support sparse lookups (where the SQL is fired for every row being processed). It only supports normal lookups where the lookup data is cached when the job starts. You could try doing all your updates on a shadow table (dataset) on the ETL server using change capture and change apply stage and then load the changed table into Teradata.

bkumar103 · Post by **bkumar103** » Fri May 16, 2008 6:59 am

Can anybody explain what could be the alternate way if sparse lookup is required on the teradata table for runtime implementation?

keshav0307 · Post by **keshav0307** » Fri May 16, 2008 8:14 am

using ODBC stage, you can get the functionilty of sparse loolup

AKUMAR21 · Post by **AKUMAR21** » Tue May 20, 2008 11:32 am

However when we connect ODBC enterprise stage with lookup stage and choose the sparse lookup option, we are not able to choose the column meant for lookup in the lookup stage. It says "Key expressions cannot be set on key columns of this link. The connected stage defines the key lookup."

ray.wurlod · Post by **ray.wurlod** » Tue May 20, 2008 4:35 pm

That's correct. Therefore you must have a stream input column that matches the lookup key column name and data type. This will be used to supply the value to be looked up. Use a Copy stage which can rename the column at no cost.

vmcburney · Post by **vmcburney** » Tue May 20, 2008 6:07 pm

Sparse lookups do not use drag and drop for matching of key columns, rather they automatically match columns where the lookup column is identified as a key and the input link has a column with an exact matching name and metadata (as pointed out by Ray).