NOTFOUND constraint

Mark_E · Post by **Mark_E** » Fri Dec 03, 2004 8:34 am

Hi,

If i want to check if records do not exist between a Flat file and a OCI stage in order to report a message will the command 'NOTFOUND' be appropriate.

I am proposing the following if LnkFrmOCICust.NOTFOUND then message else ......

Thanks

chucksmith · Post by **chucksmith** » Fri Dec 03, 2004 8:46 am

I guess you are using the OCI stage for a reference lookup.

If so, the link variable YourLinkName.NOTFOUND will give you the condition you are looking for.

I still try not to do reference lookups directly to a database table. It is always best to get the records required into a hash file first, and then lookup in the hash file.

Mike · Post by **Mike** » Fri Dec 03, 2004 9:57 am

You need to be careful with LinkName.NOTFOUND in conjunction with an OCI lookup (it wouldn't get set properly in my experience). I don't remember if version 6.x had that problem. It was safer to do an IsNull check on the lookup key.

Mike

datastage · Post by **datastage** » Fri Dec 03, 2004 10:28 am

Mike wrote:You need to be careful with LinkName.NOTFOUND in conjunction with an OCI lookup (it wouldn't get set properly in my experience). I don't remember if version 6.x had that problem. It was safer to do an IsNull check on the lookup key.

Mike

Mike, I haven't heard of any issues with that. Is this something you've come across on the 7.x platform or 5.x?

Mike · Post by **Mike** » Fri Dec 03, 2004 10:47 am

Sorry. I don't remember which version I was using when I ran into this. Probably 5.x, and I've been using the IsNull option instead of the NOTFOUND option ever since.

LinkName.NOTFOUND always worked as expected with hash files.

I think it's been covered in the forum previously, so a search should probably turn up something.

Mike

chulett · Post by **chulett** » Fri Dec 03, 2004 11:24 am

I'm in the same boat as Mike. Got burned by it "back in the day" and haven't used it since.

throbinson · Post by **throbinson** » Fri Dec 03, 2004 12:06 pm

Me too. I always use IsNull. This started back in 5.2 and I never checked or changed since going to 7.01. My gut says it is the difference between returning a row and returning the equally successful "No rows selected" from Oracle. I'll bet that confuses DataStage.
Also the performance hit of round tripping to the database for the look-up for every single row needs to be weighed against the volume of data. Naturally if the volume is low or even one at a time as it is for real-time the OCI look-up makes sense. Isn't constraint logic going to be available for DataStage? This too would affect when to use. If you processed ten gazillion rows but only 5 of them needed look-ups then it would make sense to use the OCI if you could constrain on when to do the look-up.