Maximo Consultant's Blog

Category: update

How to run SQL query in Maximo without database access?

10 March 2023 / / 3 Comments

With the introduction of the Maximo Application Suite, I have had to deal with more and more Maximo environments on the cloud. This often means there is no access to the backend such as the database or the Websphere/Openshift console. Sometimes, to troubleshoot issues, it is critical to be able to run queries on the database. In this post, I will introduce a new approach to accessing the database using Automation Script.

From Maximo version 7.6.0.9, we can now build custom API using automation script. This is a powerful new feature yet it looks to be underutilized by the community.

The first obvious use case is it gives us the freedom to build any API we want without being restricted by the limitations of the Maximo Integration Framework. For example, we can create an API that returns data in CSV or binary format. Or we can use it to upload data and bypass the business layer.

Since it allows us to use the browser to interact with Automation Script, and the script framework has access to all Java functions of the MBO layer, We can exploit it to execute all sorts of weird operations.

https://[MAXIMO_ROOT]/maximo/oslc/script/[SCRIPT_NAME]

In an article I posted a few days ago, I used API script to call a Java function to refresh Maximo sequence and avoid a restart. Using the same approach, we can do a database configuration and deployment without downtime. 

In this post, I’ll demonstrate how we can use API script to run SELECT, UPDATE, DELETE SQL statements to Maximo database without direct DB access. This can come in handy when DB access is restricted. Of course, we can use MXLoader to achieve the same result. However, this method is a lot more convenient.

Creating an API script is very simple, we just need to create a script without a launch point. Then we can call it by accessing this URL on the browser:

If you’re already logged in and have a session. That is all it takes. Otherwise, to authenticate the request, you can pass in username and password parameters like you normally do when calling the REST API.

https://[MAXIMO_ROOT]/maximo/oslc/script/[SCRIPT_NAME]?_lid=[USERNAME]&_lpwd=[PASSWORD]

To run a SELECT query on the database, I created a script named RUNSQL with the code below:

To use the script to run a query, I typed the SQL query directly in the URL in the sql parameter as below. 

https://[MAXIMO_URL]/maximo/oslc/script/runsql?method=SELECT&sql=SELECT top 10 assetnum,description,status FROM asset WHERE status = 'OPERATING'

In this case, the data is returned to the browser in the CSV format.

To execute a query that does not return data (INSERT/UPDATE/DELETE):

https://[MAXIMO_URL]/maximo/oslc/script/runsql?method=DELETE&sql=DELETE maxsession WHERE userid = 'maxadmin'

Note: I have tested this against SQL Server. Haven’t got a chance to test it against DB2 and Oracle database.

How to redeploy a single file in Maximo?

10 January 2022 / / 1 Comment

Most Maximo settings or Java code can be deployed by copy/paste the file directly to the installed folder in Websphere without having to rebuild and redeploy the application. However, with web.xml, it doesn’t work that way. Sometimes, we need to update this file to increase the timeout setting or enable/disable LDAP integration

Sure, we can directly modify the file in Websphere without redeployment, but we will also have to update the file in a few temporary folders for which, I find the process quite tedious.

To avoid having to rebuild and redeploy the whole maximo.ear file, which can take a lot of time, we can just redeploy the single web.xml file instead. Below is the process:

  • Update the web.xml file with new settings
  • Log in to the Websphere console, open Applications > Application Types > WebSphere Enterprise Applications: select “MAXIMO” application by ticking on the checkbox next to it, click on the Update button
  • In “Application update options“, select “Replace or add a single file” option
  • In the textbox below “Specify the relative path….“, specify: maximouiweb.war/WEB-INF/web.xml
  • In the “Specify the path to the file“, choose “Local file system“, and click on “Choose file” to browse and select the updated web.xml file, click Next. 
  • Click OK on the next screen to deploy. Click Save when the deployment process is completed.
  • Wait for a minute for the new settings to be propagated to all nodes, then restart Maximo.

Deploy Maximo 7.6.1 on Websphere 8.5

15 August 2018 / / 7 Comments

Out of curiosity, I installed Maximo 7.6.1 on a VM to explore the new features. Then I found that I kind of like it, so I wanted to deploy it on my host OS to get rid of the VM. The trouble is I have Websphere 8.5.5.3 which comes with Maximo 7.6.0.0. I can run maxinst.bat to create demo data with Oracle 11g without a hiccup, but the deployment of the EAR file to Websphere didn’t go well.

It turned out Maximo 7.6.1 requires Java 1.8 and Websphere 9. It’s not possible to upgrade Websphere from version 8 to 9. We will have to install a new Websphere environment and migrate all of the settings which sounds like a lot of work. I decided to tweak it a little bit to make Maximo 7.6.1 runs on Websphere 8.5 using the following process:

1 – Update Installation Manager: Open IM, then open File > Preference, on the left menu, choose Update, check the Search for Installation Manager updates. Then click Ok. Then back to the main Installation Manager screen, click on “Install”, a wizard will pop up to download and update Installation Manager to the latest version (I got 1.8.9 by the time of writing this)

2 – Update Webshere: Open IM after it is updated, click on Update, choose Websphere Application Server 8.5, then follow the wizard. It will download and update Websphere with the latest fix packs. (I got 8.5.5.13)

3 – Install JDK 1.8: Open IM again, choose Install, then select ‘IBM SDK Java version 8’ to install it to the existing Websphere. After that, I got both Java 1.7 and Java 1.8 in my Websphere folder:

4 – Switch SDK version in Websphere: I used the [WAS_home]/bin/managesdk.cmd tool to change Websphere to use SDK 1.8 following the instruction here. To verify that MXServer uses the correct SDK version, I started MXServer and look at the SystemOut.log file to see the startup log:

5 – Upgrade deploytool: After the above step, I tried to deploy maximo.ear file and it failed. Turned out that the deployment tool of Websphere 8.5 doesn’t work with this new Maximo version. So I cheated by replacing the ‘deploytool’ folder of my Websphere 8.5 environment with the ‘deploytool’ folder I copied from the Websphere 9 environment I got when installing a fresh Maximo 7.6.1 instance in my VM. After that, the deployment process ran without any issues (and it is super fast compared to the previous version, yay!)

6 – Add new JMS queues: after maximo.ear is deployed, I tried to start MXServer but it failed, turned out there are new queues added to Maximo 7.6.1 which are not there with my existing Websphere environment, so I have to manually add those new queues and activation specifications to Websphere.

In summary for this step, I had to add new queues and activation specifications to Websphere. I basically mimic the settings from the Websphere environment I got in my VM. In the end, I added two queues: NOTF and NOTFERR, and two activation specifications: notfact, and notfacterr. Since I use Websphere MQ, I have to added two bus destinations in Websphere MQ as well. If you use the standard WAS default messaging provider, you simply add the two destinations to the ‘intjmsbus‘. I struggled with this step a little bit as the name of these artefacts are case-sensitive but I didn’t pay attention the first time I created those.

After this step, MXServer starts up without any issue and it works like a charm.

How to modify (almost) any Maximo data with no database access

22 July 2018 / / 5 Comments
Being Maximo consultants, we often come into a scene where the client gives us MAXADMIN access to the system but access to the database is an absolute No-No. This is usually the case with companies that have a clear separation of the App Admin and DB Admin roles. This is also one of the key restrictions with Maximo as a Service.
 
If you have been doing a bit of admin and config activities, you will surely understand the limitation of having no database access. It’s like having to work with tied hands. Luckily, we can use MXLoader to query/update almost any data tables in Maximo. Below is an example on how to do it.
 
Let’s say we’re working with a Maximo SaaS and IBM only gives us front-end admin access, but no access to the back-end. Sometimes, Maximo is stuck while applying DB config, and we cannot turn off Admin Mode. This is usually not a big problem when we have full DB access. To fix this, we simply have to update the CONFIGURING flag in MAXVARS table to 0. However, with Maximo on the cloud, under standard procedure, we’ll need to raise a support ticket asking for IBM to update the flag. This could take a few days to resolve. It’s a horrible situation since we cannot do anything while Maximo is in Admin Mode.
 
Without DB access, we can update the MAXVARS table using MXLoader using the steps below:
 
1 – Create an Object Structure for the MAXVARS table:
  • OS Name: DBMAXVARS
  • Consume By: Integration
  • Add a new row, set Object Name: MAXVARS
 
 2 – Use MXLoader, add
a new ‘PEOPLE’ sheet:
 
 
 
With the new ‘People’ sheet added, replace ‘MXPERSON’ object structure with ‘DBMAXVARS’ and ‘PERSON’ object with ‘MAXVARS’ object. To identify what columns are available MAXVARS table we can open the ‘DBMAXVARS’ object
structure, and open the ‘Exclude/Include fields’ dialog from the Select Action
menu. In this case, I only need two fields: VARNAME and VARVALUE
 
3 – Set operation method to ‘Query’ and where clause to VARNAME
= ‘CONFIGURING’, then execute the query by clicking on the ‘Run’ button to retrieve
the record.
 
 
 
4 – After we get the record for ‘CONFIGURING’ variable, set VARVALUE to 0, then change the operation method to ‘Sync-Change’ then execute the operation. We will now be able to turn off Admin Mode.
 
 
 
Some other examples of using MXLoader to update the system
tables include:
 
  • MAXMENU – To move/add an app to a different module menu
  • APPFIELDDEFAULTS – To set a default value when inserting new
    record
  • MAXSESSION – To release user sessions that got stuck in Admin
    mode and the user cannot log in
  • MAXDOMAIN – To create a new Synonym Domain
In case we do not have permission to use the Object Structure application, another method is to create a new single-page application using Application Designer to expose data of the object on GUI. It would enable us to achieve the same result and only take a few minutes to do. However, I like MXLoader a lot more as we can use it to bulk insert/update normal app data, and it doesn’t leave a bunch of junk apps in the system which is quite difficult to remove.
 
The policy to restrict DB access is usually there for a good reason. And MXLoader is an extremely powerful tool. I find it a lot easier to make mistakes updating the wrong data or to the wrong environment. So please be very careful when using it and you should always have a backup of the before and after versions of the data set that you work on.

[Update 2023]: an easier and more powerful way to achieve this task is using API Automation script

Solving problems with Update DB process when install or upgrade Maximo

12 July 2018 / / 0 Comments
When upgrading Maximo or installing new add-ons or fix packs, new source files will be copied to SMP folder which include Java classes and DBC (database configuration) script files. After that, the installer will run the UpdateDB process to update the database, run the BuidMaximoEar process to build the EAR file, and then deploy the EAR file to Websphere.
 
The DBC script files contain incremental changes to the Maximo database which add changes to GUI, update data, and modify DB configuration objects. Most of the problems you get when installing fix packs or upgrading Maximo come from the UpdateDB process which execute these DBC files in a set order.

Thus, if you have a problem with this process, you can follow the below general steps to troubleshoot and fix the problem:

 
  • When you have an error with UpdateDB process, note down the name of the log file that contains the error message. It has this format: [Process Name][YYYYMMDDhhmiss].log. 
  • If you have a problem with the UpdateDB process when running the installation program and don’t know the log file name, locate the [SMP]/maximo/tools/maximo folder and run the updatedb.bat (.sh) tool
    again. It will produce the same error
  • Open the log file which is located under
    [SMP]/maximo/tools/maximo/log folder to get the detailed error message.
  • Fix the problem then run the UpdateDB tool again from the command line. It will continue from the last success point.
 
Following is an example on how I got a problem when trying to create a demo database from an SMP copy I received from my client just a few hours ago. When installing demo instance by executing maxinst.bat, Maximo will create a standard initial  database by running a bunch of SQL statement from [SMP]/maximo/tools/maximo/en/maxdemo.ora for Oracle (or maxdemo.db2, or maxdemo.sqs for DB2 and SQL Server respectively). Then it will call the UpdateDB process to apply incremental changes to this database to install add-ons/fix packs which are already installed in this SMP folder).
 
During this process, I got the follow error:
 
 
To get more details about the error, I locate the Updatedb20180712173523.log in folder: [SMP]/maximo/tools/maximo/log. It gives me more details about the problem:
 
 
The problem occurred while it tried to execute the file V7110_03 script for the Oil & Gas add-on. In this case, if I scrolled up several lines, I can quickly see the problem, which is caused by an ORA-01000 – maximum open cursors exceeded. Basically, I forgot to set open_cursors parameter to 1000 as recommended by IBM, leaving it with default value = 300 when creating a new database. Thus, all I have to do in this case is increase it to 1000, then run the UpdateDB tool again. With real production database, sometimes I have to increase it to 5000 or 10000 in order to apply some fix packs.
 
In many cases, the problem is not easily identified by just looking at this log file. So we have to scroll up to a bit further, to identify which statement is causing the issue. Then, we will have to open the DBC script file which can be found under [SMP]/maximo/tools/maximo/en/[add-on folder]/[dbc script file]. For example, in this case, it is …/en/oilandgas/V7110_03.ora
 
 
 
There are a number of script file types:
 
  • Files with .ora, .sqs, .db2 extensions: are just normal SQL files applicable for Oracle, SQL Server, and DB2 respectively. You can run the SQL statement from this file against the database to check for problems directly.
  • Files with .msg extension: are used to
    add/modify existing messages. You normally don’t have a problem with these.
  • Files with .mxs, and .dbc extensions: are used to
    modify application GUI design, and apply data updates or database
    configuration changes.
 
Most of the problems come from .dbc files. To understand DBC script and identify what each statement does, you can refer to this DBC XML Format Technical Reference 
 
Some common problems we have when running UpdateDB include:
 
  • Error when creating a unique index: in this
    case, we have to query and update the data to remove or fix records with
    duplicated keys
  • Error when creating existing objects: most of the
    time, we can drop the existing object as a new object will be created by the
    script anyway
  • Missing script files, or script file doesn’t follow naming convention, which have ordered number. If there’s a missing script file, you can create an empty file by copy/paste from an existing dbc file, and rename it to have a missing ordered number. For files which don’t follow naming convention, I got get this java.lang.ClassCastExeption a few times when updatedb with Linear add-on, turns out there is a file named V7500_linear.dbc which updatedb expects to have a number after V7500, so all I have to do is change it to V7500_02.dbc and it doesn’t complain any more
  • DB operation fails due to maximum open_cursors or processes exceeded (in Oracle): increase the open_cursors, processes parameter and try to run UpdateDB again.
  • Java NullPointerException: this one is quite difficult to figure out. We have to look at the DBC file to understand what it’s trying to do. This is often caused by bad data, such as orphan records referencing a non-existence parent.
  • Error with certain DB operations like when it is unable to create a new Workorder table after Drop the table (when there’s a change to the table
    structure): I’m not exactly sure what mechanism causes this, but I found the
    problem goes way if I run the UpdateDB process again (and again). I only
    attempt to fix it if the problem persists after the 3    rd run.
 
I hope this helps you when the going gets tough.
 
Error Message

© 2024 Maximo Consultant's Blog

Theme by Anders Noren — Up ↑