Why is Executing SQL Against the Maximo Database a Bad Move?

It seems tempting… that annoying piece of data could be fixed with a single SQL statement….

This article explains why you should resist the temptation …

This post refers to entries in the SystemOut.log /

Where might you want to use SQL?

To find problem data – e.g. when building reports
Some IBM technotes advise executing SQL e.g. to clean up workflow transactions
To assess data to be archived
Some data migrations under strict conditions e.g. Maximo upgrades

Why I don’t recommend Inserting/Updating/Deleting data via SQL unless based on IBM advice or it is completely understood...

Maximo Business Objects implement validation rules to prevent data corruption and ensure that business practices are followed
IBM Support can close PMRs where custom (i.e. non-IBM provided SQL) has been used – They may politely point you in the direction of the IBM Consultancy service
This is a quick recipe for problems if you are not very familiar with the Maximo DB and how it works e.g. a workorder status change that doesn’t appear in the workorder status history…
The operation itself may work properly but you could find that you then can’t create new records because you didn’t update the sequences correctly
Data is stored in multiple tables so multiple SQL statements are required and it is easy to forget to update a table or to miss a key column
SQL statements executed when Maximo is running can lead to hard to detect row locking issues and performance problems
SQL statements executed when Maximo is running can lead to record refresh errors and the SQL statements updating the rows won’t be visible in the Maximo logs for analysis
DB deadlocks can occur because the code interacts badly with Maximo

Precautions to take if you absolutely have to use SQL

Backup the database
Make the changes at a time when no one else is using the system – otherwise the backup won’t contain the other changes
Make the change in a test database first to confirm the SQL actually works
Test all relevant business processes after the change – not just the desired effect. Does changing that record mean that something else breaks?

The testing is particularly important as fixing one problem can lead to others e.g. changing the status on a record could update the record but failing to update the historyflag means that the record won’t be visible in the list view

Why you shouldn’t give SQL select access to users…

Maximo automatically adds filters to your where clause to hide data that you shouldn’t see e.g. data from other SITEs
Maximo automatically adds filters to hide data you don’t want to see e.g. historic data
If a user executes a long running query then that won’t be logged in the SystemOut.log file so database level performance tools are required to identify them
Once you give out connection details users are free to build their own sub applications using tools such as Microsoft Access and Excel – Often these are better provided using interfaces or BIRT reports
The Maximo database structure is complex with the same data being stored in multiple tables – IBM do publish some information about the structure (7.0 / 7.6). The 7.0 pictures are still the best way to get an overview of the general table structure although they are old and don’t cover some of the newer features/changes. It is worth downloading the 7.0 pictures soon as IBM periodically remove documentation/items relating to old versions that are out of date and 7.0 is now unsupported.

What are the alternatives?

Three alternatives are:

Grant selected user the ability to leverage the Query Based Reports functionality which is aware of the database structure.
Setup a reporting team to allow users to specify reports to provide the information that is required
Develop reports against a secondary reporting database

Each has its pros and cons and require careful thought.

Making Maximo better in the longer term – RFE 83252

The 7.6 table documentation is produced as a set of HTML pages and the structure means that it can only have been produced using an automated tool. Unfortunately this tool is not built into Maximo so as soon as you reconfigure your system (e.g. change a relationship or add a custom object) the pages are out of date.

Please consider voting for RFE 83252 which encourages IBM to build that documentation tool into the core product so you can use it to generate fresh pages as your system is updated.

Maximo

Maximo