Rick,
Based on your description, you may want to consider a trigger program to
search for the words in the list on new rows as they are added. Then the
mass search will only be necessary when new words are added to the word
list.
Brian May
Project Lead
Management Information Systems
Garan, Incorporated
Starkville, Mississippi
<Rick.Chevalier@xxxxxxxxxxxxxxx>
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
07/19/2010 08:10 AM
Please respond to
RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx>
To
<rpg400-l@xxxxxxxxxxxx>
cc
Subject
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.
midrange.com
