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
NOTFOUND constraint
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 385
- Joined: Wed Jun 16, 2004 12:43 pm
- Location: Virginia, USA
- Contact:
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.
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.
Chuck Smith
www.anotheritco.com
www.anotheritco.com
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?
Byron Paul
WARNING: DO NOT OPERATE DATASTAGE WITHOUT ADULT SUPERVISION.
"Strange things are afoot in the reject links" - from Bill & Ted's DataStage Adventure
WARNING: DO NOT OPERATE DATASTAGE WITHOUT ADULT SUPERVISION.
"Strange things are afoot in the reject links" - from Bill & Ted's DataStage Adventure
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
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
-
- Charter Member
- Posts: 299
- Joined: Wed Nov 13, 2002 5:38 pm
- Location: USA
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.
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.