📚 Changing Maximo’s long Description and log default font sizes (For All Apps) 📚 If…
Maximo attributes such as the Description field are natively configured to use TEXT search instead of WILDCARD. A text search will use the database’s text search engine. This is important because text searches are more efficient due to the database using indexes to locate records. In addition, a text search is case insensitive so users typically prefer this type of search over WILDCARD.
I recently had an experience where a Maximo environment using a Microsoft SQL Server database was unable to locate item records using the search term ‘WELL PUMP’ on DESCRIPTION. A search for ‘WELL’, ‘%WELL%’, or ‘WELL PUMP’ brought back zero records. The pump could only be found by searching for ‘PUMP’.
Since ITEM.DESCRIPTION is a text search enabled attribute, I knew it had to be full text search related. However I was unsure how it was related. I first rebuilt the text catalog located under Databases > [database_name] > Storage > Full Text Catalogs > [text_catalog_name].
A rebuild of the catalog had no effect. I next increased logging on the ITEM object so I could get the exact query Maximo was sending to SQL Server. This produced the following query:
select * from item where ((status != 'OBSOLETE' and contains(description , 'FORMSOF(INFLECTIONAL,"WELL")') and itemsetid = ITEMSET1)) and (itemtype in (select value from synonymdomain where domainid='ITEMTYPE' and maxvalue = 'ITEM'))
Running the above query in SQL Server Management Studio produces zero records. I was getting closer!
The downside is that there is no shortage of articles on the Web discussing SQL Servers Full Text search capabilities. I found myself down quite the rabbit hole. My esteemed coworker, Kelly Nimmo, dropped in to set me down the correct path as she so often does. She suggested I look into something called a Stop List.
A Stop List in SQL Server is a list of commonly occurring words that SQL Server will discard while searching. These words are omitted from the full-text index. A query of the SYS.FULLTEXT_SYSTEM_STOPWORDS table
SELECT * FROM sys.fulltext_system_stopwords where stopword = 'WELL'
for the word ‘WELL’ reveals the following entries:
Bingo! I now had a decision to make. I could look at removing these entries from the table. Instead, I chose to omit the ITEM table from using the Stop Word list via the following statement:
ALTER FULLTEXT INDEX ON dbo.item SET STOPLIST = OFF
I will roll this out to other tables where WELL may be used to prevent this behavior in the future.