teradata table lookup

keshav0307 · Post by **keshav0307** » Mon Jan 16, 2006 10:38 pm

my job requires lookup from teradata table for each row of source, the teradata table has 200+ millions records. how can i pass the input column name in the where clause of select statement in the teradata table.

Teradata Table
|
|
|
source file--------->Lookup stage ------------>target

keshav0307 · Post by **keshav0307** » Mon Jan 16, 2006 10:41 pm

..........................Teradata Table
..................................|
..................................|
..................................|
source file--------->Lookup stage ------------>target

vmcburney · Post by **vmcburney** » Mon Jan 16, 2006 11:11 pm

If you are using an early 7.x release your key lookup column and your stream columns need to have exactly the same name before you can select them for a lookup key field. If you are using 7.5.1 then a normal lookup stage will let you drag and drop the stream field to the lookup key field. With 200 million records it will provide poor performance as it is too large to load into memory. A join stage may work better.

If your stream data is quite small then a sparse lookup is possible as a row is retrieved from the database for each input row instead of the full 200 million being returned. In this case your stream lookup key field needs to have exactly the same name as your joining stream field and they will be automatically linked together, you will not see the link within the Lookup stage.

keshav0307 · Post by **keshav0307** » Mon Jan 16, 2006 11:34 pm

i didn't find any sparse or normal lookup type for Teradata Table stage.
how can i pass input column in the where clause of the select query in teradat stage.
"SELECT
PRTY_ID, CDBOR_CIDN APP_KEY_VAL_1
FROM
RTW4B_TDW_D01_Data.TR_PRTY
where CDBOR_CIDN=ORCHESTRATE.APP_KEY_VAL_1"
this query doesn't work

ray.wurlod · Post by **ray.wurlod** » Tue Jan 17, 2006 2:44 am

Sparse lookup is only available for DB2 and Oracle.

You need either to pre-load a Lookup File Set with the data in your lookup table, or involve a virtual Data Set. In the latter case you really need to have enough memory available such that the entire reference data set can be resident.

keshav0307 · Post by **keshav0307** » Wed Jan 18, 2006 2:47 am

what will be the best method to resolve my issue.
i have a file of 200000 records, for each record i need to get a value from a Teardata table(having 200000000 records).

ray.wurlod · Post by **ray.wurlod** » Wed Jan 18, 2006 3:28 am

If both sources are Teradata, can you perform the join within Teradata?

If not, can you reverse the logic, so that your lookup table only contains 200000 rows?

Otherwise, do consider a server job, which can perform lookups directly against the (large) Teradata table. With only 200000 records to process this may be an adequate solution.