Hot reset sequence without restarting Maximo

One error we often have to deal with is incorrect sequence when adding new data to Maximo. There are many situations when it might come up, 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 duplicated key value such as “BMXAA4211E - Database error number 2601 has occurred…”

The solution for this 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 to 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 down time.
• A location data hierarchy update requiring me to insert a few thousand new records to the LOCANCESTOR table. I needed 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 sequence cache without restarting Maximo is by calling the reset sequence Java function via 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 REST API: [MAXIMO_URL]//maximo/oslc/script/runtask?_lid=maxadmin&_lpwd=maxadmin

  
  

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

UPDATED: 

- 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)