Tweet
I have to rewrite the search box on our corporate website, and want to use something similar to what the search engines use to find records that "best" match a string.
The existing version of the program (written in Websmart ILE) uses more than 20 SQL statements to try and match various combinations of up to 6 words, and aside from this being horribly inefficient, I really don't want to write it this way. The search is to be on our Product file, which already has an index that contains all the keywords to search on.
I spent hours Googling yesterday, and it seems that only some SQL versions allow a Full Text search to be performed.
One version is MySQL, and this only works on MyISAM tables. So, I could create a table in MySQL, which would contain exactly the same data as the Product Index, but would be easily searchable.
This means extra work to create the database/table, which is then messy to maintain and access from RPG because it's in MySQL. However I do believe this would work.
I did read that IBM have DB2 Text Extenders that allow you to do full text searches, but it seems this is only to allow you to index text documents on the IFS and to search them.
It might be possible to create a single text document that contained all the index data, or maybe I would have to create a text document for each product. Either way it's a lot of overhead.
What I want to do is to take a search phrase (for example "1mm wire strippers and cutters") and to return the best matches.
Any thoughts? I know that very often having searched Google to death, by posting an article you then come across something helpful on your own, so forgive my indulgence in posting! I really would have hoped that IBM had some way of doing a search for a phrase.
----------------
I should maybe add that our web pages are built on the fly, not fixed and stored in a folder. I did come across something called "AS400 Webserver Search Engine" which searches folders, but what we need is just a way of searching for a phrase in an AS400 file.
The existing version of the program (written in Websmart ILE) uses more than 20 SQL statements to try and match various combinations of up to 6 words, and aside from this being horribly inefficient, I really don't want to write it this way. The search is to be on our Product file, which already has an index that contains all the keywords to search on.
I spent hours Googling yesterday, and it seems that only some SQL versions allow a Full Text search to be performed.
One version is MySQL, and this only works on MyISAM tables. So, I could create a table in MySQL, which would contain exactly the same data as the Product Index, but would be easily searchable.
This means extra work to create the database/table, which is then messy to maintain and access from RPG because it's in MySQL. However I do believe this would work.
I did read that IBM have DB2 Text Extenders that allow you to do full text searches, but it seems this is only to allow you to index text documents on the IFS and to search them.
It might be possible to create a single text document that contained all the index data, or maybe I would have to create a text document for each product. Either way it's a lot of overhead.
What I want to do is to take a search phrase (for example "1mm wire strippers and cutters") and to return the best matches.
Any thoughts? I know that very often having searched Google to death, by posting an article you then come across something helpful on your own, so forgive my indulgence in posting! I really would have hoped that IBM had some way of doing a search for a phrase.
----------------
I should maybe add that our web pages are built on the fly, not fixed and stored in a folder. I did come across something called "AS400 Webserver Search Engine" which searches folders, but what we need is just a way of searching for a phrase in an AS400 file.
Comment