Page 1 of 1
NOTFOUND constraint
Posted: Fri Dec 03, 2004 8:34 am
by Mark_E
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
Posted: Fri Dec 03, 2004 8:46 am
by chucksmith
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.
Posted: Fri Dec 03, 2004 9:57 am
by Mike
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
Posted: Fri Dec 03, 2004 10:28 am
by datastage
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?
Posted: Fri Dec 03, 2004 10:47 am
by Mike
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
Posted: Fri Dec 03, 2004 11:24 am
by chulett
I'm in the same boat as Mike. Got burned by it "back in the day" and haven't used it since.
Posted: Fri Dec 03, 2004 12:06 pm
by throbinson
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.