RE: Use of regular expression

Charles,

Thanks for the OmniFind tip. I had forgotten about that. We are currently at 5.4 but are getting our ducks in a row for the move to 6.1. Hopefully it isn't too far in the future.

We currently use SQL for phone number searches in text fields against this table now. Searches take a while but what can we expect with that many rows. As I understand the project this will be a recurring task. More specifically, there will be a word list generated and any rows containing one of those words will be identified in a separate table. Once a row has been searched for a word it won't be searched a second time for that same word, but new records added will. I think this is what is pushing us toward an RPG solution; the possibility of searching a subset of the table. The entire table will be searched for any new words added to the list.

Rick

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Monday, July 19, 2010 7:39 AM
To: RPG programming on the IBM i / System i
Subject: Re: Use of regular expression

Rick,

neither Reg-ex or standard SQL are going to give you decent performance....as either way 1 billion records would have to be read.
I do agree with Jon that SQL should have the edge.

Your best option, if at 6.1 or 7.1, is the OmniFind text search product; which is a no-charge option at those releases.
http://www-304.ibm.com/partnerworld/wps/servlet/ContentHandler/whitepaper/i/omnifind/search
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/rzash/rzash.pdf

If you're on v5r4 (or earlier), OmniFind's predecessor is the chargeable 5722-DE1 Text Extender product.
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/books/sh126720.pdf

Lastly, OmniFind and Text Extender work by building special indexes.
You could conceivably roll your own version....but I'd be willing to bet you'd wish you'd have just bought the software! :)


Charles

Privileged and Confidential. This e-mail, and any attachments there to, is intended only for use by the addressee(s) named herein and may contain privileged or confidential information. If you have received this e-mail in error, please notify me immediately by a return e-mail and delete this e-mail. You are hereby notified that any dissemination, distribution or copying of this e-mail and/or any attachments thereto, is strictly prohibited.