Terdata to DB2 lookup

iwin · Post by **iwin** » Wed Aug 31, 2005 7:14 pm

Hi ,
I have a job, where Data from Teradata goes to Hashfile and then to transformer. And doing a lookup with DB2 , in Transformer, where file data goes to flatfile.
TD-----HS

DB2----- Trans------Flatfile

When i run the job, i am getting some 10000's of warinings like below:

Transformer_50: Reference lookup generated 2 rows. Only the last row was used.

Please help me how to get rid of these warnings.

Thanks in advance.

rasi · Post by **rasi** » Wed Aug 31, 2005 7:56 pm

Hi

Is Teradata is your source or lookup?. This error occurs when the lookup returns more than one match. Try to remove duplicates when creating references.

Regards

ray.wurlod · Post by **ray.wurlod** » Wed Aug 31, 2005 8:49 pm

Because you are performing a lookup against a partial key or secondary key, it is possible that more than one row might be returned. The DB2 stage is not equipped to return multiple rows on a reference lookup, so generates the message.

You have three choices.

Use an ODBC stage, which does have multi-row return capability, to access the DB2 table.

Change the SQL so that the query can only deliver one row (for example limit the rownumber to 1, or get the first or last row selected).

Change the operation so that you are performing a lookup against the primary key (which, by definition, is unique).

iwin · Post by **iwin** » Thu Sep 01, 2005 8:37 am

Hi,
I have taken the 3rd Option.
Thanks

iwin · Post by **iwin** » Thu Sep 01, 2005 4:56 pm

Hi ray,
Actually my refercence database DB2 contains no unique columns.
Thanks

ray.wurlod · Post by **ray.wurlod** » Thu Sep 01, 2005 5:23 pm

Then option 3 is not for you. Try option 2.

Seems to me that a table with no unique columns is a strange choice for a lookup table.