lookup with the target!!

suryadev · Post by **suryadev** » Fri Sep 30, 2011 8:48 am

I have a requirement to look up with the target as it gets loaded.
i.e the records should get loaded in the target and then this should
act as reference to the source to pull the key from target to update an other field in target.

Now the job gets loaded with the empty field as their is no look up with the target.

please provide any input for this requirement.

Thanks

ray.wurlod · Post by **ray.wurlod** » Fri Sep 30, 2011 3:54 pm

You seem to have stated the requirement quite well. I am presuming, of course, that your mapping specification correctly identifies the lookup key(s).

What you don't seem to have done is thought through the rule to be followed when the lookup does not succeed and when the lookup succeeds.

suryadev · Post by **suryadev** » Fri Sep 30, 2011 8:44 pm

Thank you

so do you mean do the lookup where it suceeds?

actually this is a kind of dynamic lookup.

The target needs to be loaded with an extra field which comes when we do lookup with the target itself.

So records should be loaded and also look up has to be done as they get loaded and the records which succeed look up should be loaded.

suryadev · Post by **suryadev** » Mon Oct 03, 2011 8:29 am

Is dynamic look up possible in data stage 7.5 parallel jobs?

Made a search but could not find a scenario where dynamic look up is done in parallel jobs of 7.5 version.

please suggest any design to implement that in a parallel job?

Thank you

ray.wurlod · Post by **ray.wurlod** » Mon Oct 03, 2011 12:50 pm

What do you mean by "dynamic"?

If you want the previous row to be available then you must set transaction size and array size to 1.

suryadev · Post by **suryadev** » Mon Oct 03, 2011 12:59 pm

What I mean by dynamic is a row should be inserted and then lookup with the source so that the primary key assigned to the inserted row is used as a parentkey to other records.
so that the table gets updated with the parentkey for all the records in the hierarchyexcept the top level.

I set array size to 1 in the oracle enterprise stage and also environmental variable
ORA_UPSERT_COMMITROWINTERVAL TO 1 but still the records are not
loaded with the parentkey.

Thank you

chulett · Post by **chulett** » Mon Oct 03, 2011 1:41 pm

What kind of a lookup are you doing - sparse?

suryadev · Post by **suryadev** » Mon Oct 03, 2011 2:00 pm

Yes I am using sparse look up for which the parentkey
gets '0' in all the records.

and when I use normal look up the parentkey field is empty.

Second time I ran the same job with an other update link then all the records were updated with the right parent key.

Thank you