A proactive DBA is a real asset to the Maximo support team and a query came in from one such DBA recently.
“I have identified a new index and built it on the database. The query performance has dramatically improved.
Do I need to do anything in the Database Configuration application for this index?”
The answer was Yes and this article will explain why and the consequences of not keeping the DB configuration application in sync with the actual configuration.
The purpose of the DB Configuration application
Maximo uses a set of meta-data tables to record the internal structure used to store its data.
These tables define objects/attributes and the relationships to the underlying database. They also define the indexes on the tables.
The role of the DB Configuration data when changing an object
When an administrator makes substantial changes to an object, e.g. adding a column, then Maximo will:
- Rename the table to a temporary name
- Create a new table with the new table structure
- Insert the data from the temporary table into the new table
- Rebuild the indexes – based on the details visible in the indexes tab
- Drop the temporary table
If the indexes aren’t defined in the indexes tab then the indexes cannot be recreated. Any grants (e.g. select access) that aren’t defined in Maximo will also be lost.
The timebomb of the missing indexes
If the indexes can’t be automatically recreated then this presents a serious long term threat to the installation.
Some database tables may not be updated/changed for months/years and when the change does occur it won’t be obvious that Maximo has dropped the index. Maximo doesn’t warn you that it has dropped an unknown index because it just drops the table and the database drops the associated indexes.
The first hint of a problem will be reports of slow operations and possibly slow SQL statements in the logs.
The initial assumption is often that the index is present – after all “we indexed that column before”. This assumption means that administrators often don’t confirm that the index is still present and query if there is a more serious problem with the database.
The human brain is good at detecting patterns but it is often weak at spotting things that are missing. This means that an administrator glancing at the indexes tab is liable to miss the fact that the expected index is no longer present. The layout of the screen doesn’t help with this and at Vetasi I have a report that shows this information in a better way.
On a system undergoing a lot of changes it can be a lot of work to go through the release notes to identify the exact index definition assuming it was actually defined. This is particularly time consuming if the indexes was implemented a long time ago.
How to resolve this?
Document the indexes in the Database Configuration application so Maximo can recreate them.
Either create the indexes via the application or manually create them and refresh the Maximo configuration using the ”Refresh Index Tables” option
I want to create an index type that Maximo doesn’t support
Maximo supports a limited set of index types and DBAs often want to use more sophisticated ones.
These often provide better performance. A standard index needs to be defined but then manually dropped/created on the database using the desired index type. This operation needs to be performed every time that the object is modified.
This article included information from technotes 1290495 and IZ59798.
#AssetandFacilitiesManagement#Maximo