Unable to Find Records in IBM Maximo

If you are searching for items and your descriptions do not present any search returns then you may need to read this article…

IBM 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 not case-sensitive so users typically prefer this type of search over WILDCARD.

I recently had an experience where an IBM Maximo environment using a Microsoft SQL Server database was unable to locate item records using the search term ‘WELL PUMP’ on the Description field.  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.  Next I increased logging on the ITEM object so I could get the exact query IBM Maximo was sending to the Microsoft 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 complexity of some of IBM Maximo enterprise asset management system requires much patience, and thoroughness, especially when troubleshooting…

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.