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].

Maximo-Object-Explorer

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:

Search-feature-Stopword

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.

Enable Logging to Capture Report Data Set Queries in Maximo 7.6

You upload your awesome new report in Maximo, click the Preview button or run the report and…. you get no results.  Is the problem in the Maximo Where clause, the report query or …?  Naturally you turn to the Maximo Logging application, crank up the SQL and Report loggers to DEBUG then sit back and comb through all those juicy log statements that pinpoint exactly where the problem resides.  Sound familiar?  

Of course not!  You crank up all that logging and comb through a whole bunch of nothing.   

This article will detail one way to coax Maximo into giving up all the information you need to properly diagnose your report running in Maximo 7.6.  You may not need to perform all these steps; however, I was not able to see exactly what I needed until I performed the last step.  

The report I was developing was based on the ASSET application and was rather complicated.  Consequently I wanted a lot of information ouof Maximo.  You may not need or want all these loggers running to diagnose your report so feel free to adjust these steps to suit your specific requirements. Keep in mind that if you do not see what you want in the log execute all these steps and get it working.  Then you can pare it down to be more efficient. 

Set appropriate loggers to DEBUG using the Maximo Logging application. 

 

As I mentioned, the report I was working on was based on ASSET. I wanted to see both ASSET object queries and report queries.  Navigate to the Logging application Maximo and filter the Logger list to show sql. 

 

 

Change the log level to Debug anApply Settings so the changes take effect. 

If you execute the report now all you will see is some SQL statements as you navigate around the ASSET object filtering the records for your eventual report.  While potentially useful in providing a complete picture of the data being requested it is most likely not what we need to figure out why our report is not displaying any records.  In order to see Birt and report related log statements we need to adjust the log level of the report logger. 

Filter the Logging application by report and adjust the log level to DEBUG for the report logger as well as the birt and directprint loggers.  Do not forget to Apply Settings. 

 

Setting additional Report loggers to DEBUG. 

 

If you, like me, run your report you still will not see what you want in the log.   

In order to see the report parameters, where clauseactual DataSet query being used and a whole bunch of other report information I had to execute the following sql statement against my Maximo database. 

update maxlogger set loglevel = ‘DEBUG’ where logkey like ‘log4j.logger.maximo.report%; 

Be sure to commit the update if not using SQL Server.   

You will need to restart the Maximo application server after this update.  The logger update will not take effect until the Maximo application is restarted.   

Now if you run the report you will see the actual queries being executed from within Maximo.       

Top 6 Things To Check When Your Integration Is Not Working

In the spirit of all those top however many number lists we see populate the internet these days I have complied a top 6 list of things to check if your integration is not working in Maximo. So, no, this is not an article on how to set it up and if you follow these steps it all works great! This is the uncommon article that addresses what to do when something might have gone wrong.

Number 1: Logs, Logs, Logs, Logs, Logs

The very first thing you should check when anything in Maximo is not working are the Logs.  Specifically, the WebSphere SystemOut.log and the SystemErr.log logs.  These files are a running tally of what is going on in Maximo and a running list of what is erroring in Maximo.  They are the first source to find out what is actually going on in the system.  Also, if you need something more specific, go to Maximo’s logging application and look up the Integration Root Logger.  Set it to DEBUG (remember to go back and set it to a lower level) then get those logs ready and test your integration!

Number 2: Admin Mode is on

I know, everyone knows this one. It’s that old saying that is redundantly used and we have heard any number of times in life.  “Did you do/check this?”  Just double check it, especially if the integration was worked on recently.  You and your colleagues may have been working in the system as well as on the integration and it could have been turned on.  As you know the Cron Tasks are not going to run with Admin Mode on.

Number 3:  Cron Tasks are not running

Many, many working parts go into the creation of an integration.  Cron Tasks are an integral part in that.  They control the processing of queues or the processing of flat file transfers.  Checking the JMSQSEQCONSUMER Cron Task if you are using WebSphere messaging queues or one of the file consumer (FLATFILECONSUMER and XMLFILECONSUMER) Cron Tasks is one of the first places to check if things are not processing.  If you are in a clustered environment check to make sure the Cron Task is not stuck on a specific server.  Do not runs may be set up and the server the Cron Task is running on may not be doing it.  One of the very reasons I wrote this article stemmed from a general discussion we had where an integration was not running.  A question was asked “Could the Do Not Run system properties be in the maximo.properties file?”  That’s right folks, those can be passed to the system through the properties file and it is quite possibly the trickiest place to find something stopping an integration from working.  Cron Tasks can get stuck and can sometimes stop running at their designated time.  Stopping and starting them is the first thing to try, but sometimes you need to check that server to see why the Cron Task may not be running.

Number 4: WebSphere messaging queues

The WebSphere messaging queues go hand in hand with the Cron Tasks for processing or checking the queue traffic.  Many times, your Cron Tasks are running, but you see no changes to the data in the system.  The queues may be receiving data but not pushing them out of the queue itself and they are piling up messages.  Checking your queues can be done in two places.  First and foremost, the queues can be checked under the intjmsbus in WebSphere (you remember those buses you set up right.)  If you built out your integration with more than one intjmsbus then you will have to check the other ones you have created to confirm where the messages may be sticking in the queues.  If you just created one intjmsbus then you will only need to check one place.  In trouble shooting a tricky integration that does not want to work this is where creating more than one intjmsbus can be tremendously helpful in pinpointing where a breakdown may be as well as limiting the effect of a poorly performing integration.  I would highly recommend this strategy in a Maximo environment that would be clustered and built to handle integrations.  When you navigate into WebSphere and look at the queues for your bus or buses if you see one (whether its inbound or outbound) with the numbers piling up that is where the “bottleneck” is.

That is not the only place to check the queues.  One can check the queues that have been used in an integration in Maximo under the Integration’s module.  This leads me into the last section which is a kind of an add on from WebSphere’s queues, but these things tend to build from Cron Tasks to Queues, to application message tracking and processing.

Number 5: Message Tracking and Reprocessing

In the Integrations module whether you set up a Published channel or an Enterprise Service under the More Actions menu you will see a choice for Message Tracking.  Once you have opened the dialog box and checked off the Enable Message Tracking option Maximo will retain a record in the database.  This is very useful option for tracking what it is going on with your integration.  In the External Systems app of the Integration module if you build out an External System with you Published Channels or Enterprise Services under the More Actions menu you have the option of choosing Add/Modify Queues.  This will show you what kind of queues have been built out in WebSphere and can show you the same data that is buried in the intjmsbus of the WebSphere administration site.  This menu choice has the ability to allow you to see message data as well as clear queue data.  So, you can operate your message queues from Maximo much the same way you can in WebSphere.  Message Tracking goes hand in hand with Message Reprocessing, which will work in with your queues the way you set them up.  If you built the queue to try to process the message 5 times it will try to process a message that many times before dropping it, and you can see the messages awaiting reprocessing in the Message Reprocessing application.

Number 6: Endpoints

Endpoints and their handlers are what actually deliver the messages to the destination side of an integration.  One of the things that trips people up is managing the different Endpoints between the environments.  Meaning DEV, TEST, and PROD environments usually will have different systems they are bound to and when creating your integrations each environment points to a unique server acting as the Endpoint for an integration.  Getting your Endpoint set up can be a challenge but pointing one system to the other system’s Endpoint will just result in questions of why this integration is not working.

 

Each one of these topics can get much more in depth and I encourage everyone to dive right into that depth, but the next time you’ve got an integration not working think about these points and see if that may be affecting your integration.