Category: Maximo (Page 2 of 12)

How to reset sequence without restarting Maximo?

One error we often have to deal with is an incorrect sequence when adding new data to Maximo. There are many situations which can cause this issue, such as:

  • When loading data using MXLoader, or inserting data directly via SQL
  • Sequence corruption due to unknown cause in Production, probably due to errors caused by cancelled/terminated job
  • Restoring database from a copy or after an upgrade.

When this happens, the user sees an error with a duplicated key value such as

BMXAA4211E - Database error number 2601 has occurred…

The solution is well-documented and straightforward, we just need to find the current maximum ID value used in the table and update the corresponding sequence to use the next value.

For example, if the error occurs with the WORKORDERID field of the WORKORDER table, we can do this SQL update and restart Maximo.

UPDATE maxsequence SET maxreserved = (SELECT max(workorderid) + 1 FROM workorder) WHERE tbname = 'WORKORDER' and name = 'WORKORDERID'

However, I like to avoid restarting Maximo if possible due to some obvious problems such as:

  • I recently had to do a quick deployment which involves uploading some data. For some unknown reasons, loading the data via MXLoader causes random sequence corruption a few times. For this client which has a large cluster, restarting Maximo will require an additional 30-60 minutes downtime.
  • A location data hierarchy update requires me to insert a few thousand new records into the LOCANCESTOR table. I needed to update the sequence to a new value for subsequent data upload via MIF to work. Since it is a cloud environment, if I can avoid a restart, we won’t need to be dependent on the availability of the cloud provider.

To address that problem, the simplest solution I found to hot reset the sequence cache without restarting Maximo is by calling the reset sequence Java function via an automation script. The steps are as follows

  • Create a new script with no launch point:

Whenever we update maxsequence table with a new value and need to reset the cache, just execute the script by calling it via the REST API: 

[MAXIMO_URL]//maximo/oslc/script/runtask?_lid=maxadmin&_lpwd=maxadmin

If it works correctly, you should see something like below.

Executing the reset sequence script by calling it via REST API

No restart during a deployment means we can all go to bed earlier. Best of luck.

UPDATE:  On a clustered environment, I find it doesn’t seem to refresh all the JVMs. Thus, to be sure, we might need to run it on each JVM separately (by accessing the script from the JVM 908x port)

Use Maximo webservice with JSON content

While the JSON API in newer version of Maximo is quite useful, for many integration scenarios, I still prefer to use the old API infrastructure with Publish Channel and Web Service. However, the native format for this feature is XML.

To send or receive JSON with Publish Channel or Enterprise Service, we can translate the default to JSON format before it goes out / into the system. Below is a simple example to set it up.

Setup standard Publish Channel to send XML message

  • Create a new Publish Channel: 
    • Name: ZZSR
    • Object Structure: MXSR
  • Create a new Enterprise Service:
    • Name: ZZSR
    • Object Structure: MXSR
  • Create a new HTTP End Point:  ZZWEBHOOK (To keep it simple, we use webhook.site. See more details here)
  • Create a new External System:
    • Name: ZZTEST
    • End Point: ZZWEBHOOK
    • Set standard queues.
    • In Publish Channels tab, add the ZZSR channel created above.
    • Enable the channel, enable the external system, and enable event listener on the channel
  • To test and verify our Publish channel is working:
    • Create a new Service Request, then save.
    • After less than a minute, Webhook should show a new message received in XML format

To send JSON instead of XML:

  • Update publish channel ZZSR, set Processing Class with value com.ibm.tivoli.maximo.fdmbo.JSONMapperExit
  • Go to the JSON Mapping application, create a new mapping:
    • Name: ZZTEST.ZZSR.OUT (it must follow this exact format <ExtSys>.<PublishChannel>.OUT)
    • Object Structure: MXSR
    • JSON Data: {  “summary”: “Test SR”,  “details”: “Sample Details”,  “assetnum”: “11430” }
  • Save the record. Then go to Properties tab. Enter mapping as follows
  • To test the mapping works, create another SR. And check in Webhook to ensure it now receive a new message in JSON format

To receive JSON on Webservice, use similar step like above, the only key difference is, we have to name the JSON mapping as <ExtSys>.<EntService>.IN. And use a mapping like the example below

Message Engine doesn’t start after setting up cluster

This issue hit me a few times and always took me some time to figure out what happened. So I thought it’s a good idea to note it down.

Symptom:

When setting up cluster environment for Maximo, I will need to setup an integration bus with a message engine for each cluster (IF, UI, Cron etc.)

Each message engine will require its own individual schema (and thus individual user if the Oracle DB is used)

After integration bus are setup and Maximo cluster started, we see a lot of errors in the log file, usually in the Cron or MIF cluster due to message engine is not available.

When restarting the cluster, we can see that the message engine for that cluster has a “partial started” status. But a few minutes after the whole cluster is started, the message engine would show an “unavailable” status.

 

Troubleshoot:

  • Check ffdc log under [Websphere_Home] AppServerprofilesctgAppSrv01logsffdc, check for [MXServer_Name]_exception.log to see if there are any exception related to integration bus or message engine such as:
com.ibm.ws.sib.msgstore.persistence.DatasourceWrapperStoppedException com.ibm.ws.sib.msgstore.persistence.impl.PersistentMessageStoreImpl.start 1:206:1.47.1.53 D:IBMWebSphereAppServerprofilesctgAppSrv01logsffdcMAXUI-N1-4_58bec328_22.10.24_15.50.24.7956943157914483024680.txt

com.ibm.ws.sib.msgstore.PersistenceException com.ibm.ws.sib.msgstore.impl.MessageStoreImpl.start 755 D:IBMWebSphereAppServerprofilesctgAppSrv01logsffdcMAXUI-N1-4_58bec328_22.10.24_15.50.24.8271423565797649410081.txt
  • For each of the above exception, open the file it referenced. We might see some detailed error message which could help us to solve the issue.
  • In this case, I have this vague error message below:
CWSIS1501E: The data source has produced an unexpected exception: com.ibm.ws.sib.msgstore.persistence.DatasourceWrapperStoppedException: New connections cannot be provided because the persistence layer has been stopped

Solution:

  • For this specific case, it is caused by a failed setup process in previous setup which left a bunch of tables created under different message engine details. Thus, the current message engine doesn’t like to reuse it. To solve this issue, we can simply change the schema name of for the message store so that the next time the cluster starts, it will create a whole new bunch of tables for it own use.
  • If Oracle database is used, schema is linked with the DB account used for login, and is more difficult to change. Thus, I’ll just drop all the tables used by that schema. One quick way to identify and delete those tables is running this query:

select ‘drop table ‘ || table_name || ‘;’ from user_tables;

Consider Time Zone with automation script

It took me some time to get to this piece of code, but requirement changed and I needed to ditch it. But I’m sure I’ll need to use it at some point in the future, so why not put a note here just in case.

Requirement

– A client has many hotels in Sydney and Brisbane which are in two different time zones. Sydney has daylight saving while Brisbane doesn’t have. In other words, there is one hour difference for half of the year, and no time difference for the other half.

– Client wants to display a warning message to the user that when he/she raised a service request out of normal working hours (7AM to 7PM), if it’s a high priority item, they should make a phone call instead.

Finding:

– User profile has a time zone setting. We can also associate a time zone to a specific site or location

– Time stored in the database is server time. In this case, the server is in Sydney, as such all date/time values are Sydney time. The time zone associated to site or location won’t affect the time values in Work Order (e.g. reporteddate)

– Time displayed on the screen is converted to user’s time zone.

– Therefore, if we use a conditional expression with a standard SQL where clause, or create a saved query using the time part, it can be inaccurate due to the values are all Sydney time

Example:

– During summer time when there is daylight saving in Sydney, there is 1h difference between Brisbane and Sydney. A user in Brisbane creates a ticket at 6:30 PM for a location in Sydney, that would be 7:30 PM. 

– If we consider user’s time zone, that’s still within working hours. If we consider the site’s time zone, that’s off hours. Based on that, we might want to display our warning accordingly.

– The custom conditional expression automation script below works for me:

– In the case we just want to convert time to one specific zone, we can use the java.util.TimeZone class:

from java.util import TimeZone

timeZone = TimeZone.getTimeZone("Australia/Sydney")

Issues with Maximo attachment (DOCLINKS)

Just a bunch of my personal notes about Maximo attachment (DOCLINKS)

  •  When a file is attached to a record in Maximo, it creates a record in the DOCLINKS and a record in the DOCINFO table to keep the details of the file. The file is copied to a location on disk, usually on a local folder (e.g. D:DOCLINKS) or on a shared network folder. The path for Maximo to read the file is kept in the URLNAME field of the DOCINFO table.
  • To set up this function, refer to this blog post by Bruno
  • When a user uploads an attachment, the location where Maximo puts the file will depend on the Folder selected:
  • In most applications such as Work Order Tracking or Assets, there is a Select Action > Attachment Library/Folders > Manage Folder function, it allows you to specify the location to keep the file for each Folder.
  • If no path is specified for a folder, Maximo uses the default value of the System Properties:  mxe.doclink.doctypes.defpath
  • This setting controls where to keep the file when uploading. After a migration or upgrade in which you moved your file server location, and if new files still end up in the old location. This is where you should update your settings
  • When a user clicks on the link to download an attachment, there are two methods for Maximo to serve the file.
  • If System Property: mxe.doclink.securedAttachment is set to False (default), the HTTP Web Server will read the file from disk, then serve it to the end-users. Maximo application server doesn’t play a role in delivering the file here.
  • If System Property: mxe.doclink.securedAttachment is set to True, when the end-user downloads an attachment, Maximo application server will grab the file, encrypt it, then serve it to the end-user. Thus, in this case, we don’t really need the web server for it to work. I.e. if you accessing Maximo directly on the application server (via port
    908x), it will still work. Thus, in a clustered environment in which a load balancer distributes load directly to Maximo JVMs without a web server in the middle, this is the recommended choice.
  • The link to download an attachment is kept in DOCINFO.WEBURL non-persistent field. The value of this field is generated by replacing the URLNAME field of the DOCINFO record, with the rule set in the mxe.doclink.pathXX property. 
  • For example, if you have the following rule: mxe.doclink.path01=D:DOCLINKS=https://maximo.company.com/attachments/ For a document with URLNAME=D:DOCLINKSdrawingfile01.pdf, the WEBURL would be https://maximo.company.com/attachments/drawing/file01.pdf. If the first part of URLNAME doesn’t match with the left-side of the PATH01 equation, Maximo will look at mxe.doclink.path02,path03 and so on to replace the value and generate a WEBURL. If there’s no match, it will generate WEBURL with an absolute path: file://[URLNAME]. This means that if the user has access to the file on the shared network path, it will still work without the need for a web server.

In summary, I usually hit with issues with doclinks after an upgrade or migration,  I use the following checklist to avoid those issues:

  • If a new Web server is set up, need to update the DocumentRoot, and the Directory settings in the web server’s httpd.conf file
  • If the Web URL to access Maximo changes: need to update mxe.doclink.path01
  • If the file server location was moved:
    • Need to copy the file to the new location
    • Maximo service account should have permission to access the local/network folder
    • Update:
      • mxe.doclink.doctypes.topLevelPaths
      • mxe.doclink.doctypes.defpath
      • mxe.doclink.path01
    • Update the DOCTYPES.DEFAULTFILEPATH in
      the database with a REPLACE SQL command
    • Update all DOCINFO.URLNAME in the database with a REPLACE SQL command

 

How to test SMTP with PowerShell for setting up Maximo email notifications?

In an enterprise IT environment, it is sometimes difficult to set up Maximo to talk with SMTP service due to networking and security restrictions. To troubleshoot SMTP configuration, in the past, we used Telnet from the CMD tool. However, in newer versions of Windows Server, Telnet is often not installed by default. In such cases, we can use PowerShell to test and confirm the SMTP and firewall setting is working before configuring SMTP in Maximo.

The first thing I would do is check whether firewall has been opened so that the port used by SMTP service (e.g. port 25) is reachable from the Maximo server. I’ve provided details on how to do this with PowerShell in this post: Check network firewall status with PowerShell 

Once it is confirmed SMTP service is reachable, the next step I would do is to ensure we can send an email with the provided login details. Thus, from PowerShell running on the Maximo server, I would do the following commands:

Note that if SMTP service doesn’t require authentication, we don’t need to run the first command and use the parameter -Credential $cred in the second command.

Replace placeholder values with actual details required by your SMTP service, and for the -To parameter, enter your email address.

The Send-MailMessage command will send an email using your SMTP
service. If it works, you should receive an email.

Once SMTP service is confirmed working, I’ll go ahead putting the details to System Properties in Maximo and that should be it.

To test that Maximo can send emails, the simplest way I would
do is to open any work order, then use the Select Action > Create >
Communication
, then entering my own email into the “To” address field. If it
works, after clicking on Send, it should show a successful status, and you’ll
receive an email within seconds. Otherwise, you might want to look at
SystemOut.log file to see if there are any details of the error there.

« Older posts Newer posts »