Updating lookup file sets

rmrama · Post by **rmrama** » Wed Mar 22, 2006 5:38 am

Hello,

I'm curious to know if this is possible. I have a parallel job which does lookup using a Lookup stage. This stage take data from a Lookup Fileset, which is populated in a prior job.

I want to make a lookup, and move forward the following way:

1 - If matching key values are found, return value from another column (column X) available in the Lookup Fileset and carry on to the output stream of the Lookup stage.

2 - If matching key values are not found, take the rejected records and generate a column X value for each record and then carry on to do other things. At the same time, update the Lookup Fileset so that subsequent runs will not recreate the column X value.

Is this possible, and if yes how can i do it? I did this by inserting a routine into a Transformer to update hash files in Datastage Server.

Thanks in advance for your time!

Regards,
M. Ramanathan

kumar_s · Post by **kumar_s** » Wed Mar 22, 2006 6:47 am

If the issue is only for the subsequent run, then the rejected records can be funneled with the current data which populates the lookup fileset and can be made to populate the lookup file set in the subsequent job.

ray.wurlod · Post by **ray.wurlod** » Wed Mar 22, 2006 3:32 pm

To reinforce the point, no passive stage in a parallel job supports an output link AND an input link (not counting reject-handling). That is, you can not update the Lookup File Set in the same job in which you use it to provide reference values. So Kumar's solution, or something like it, is the only available solution.

rmrama · Post by **rmrama** » Wed Mar 22, 2006 6:49 pm

Ray and Kumar, thank you both so much for your views.

If I get your inputs correctly, the best way would be for me to design the job in such a way that successful lookups (existing data) and rejects (new data, with new key values generated in Column X) are collected and re-written into the lookup fileset.

This is ok i guess, but i'm not sure how such a design will be helpful when the incoming data has duplicate rows. If duplicate rows come in within the same data source file and fails lookup, both rows will be rejected and assigned different Column X key values.

This is not what i want. My idea is to use the lookup fileset to return Column X values for records already available in the system, and generate new Column x values for new records coming into the system. For the example above, what would suite me is for the first occurrence of the reject to generate a new Column X value and persist in the lookup fileset and the second record to refer and pickup the newly created Column X value and move on.

Any view?

Regards,
M. Ramanathan.

ray.wurlod · Post by **ray.wurlod** » Wed Mar 22, 2006 7:35 pm

You can do all you like. The Lookup File Set stage has a property where you can elect to allow or disallow duplicate values. If you disallow, it supports another property that lets you specify how to handle "rejected rows".

rmrama · Post by **rmrama** » Thu Mar 23, 2006 7:55 pm

Thanks Ray.

I couldnt read a big part of your replies, which is due to my choice of membership

I'll try and make do with what understanding i got.

Thanks again!