Quick points for DBAs – Maximo and index creation

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:

1. Rename the table to a temporary name
2. Create a new table with the new table structure
3. Insert the data from the temporary table into the new table
4. Rebuild the indexes – based on the details visible in the indexes tab
5. 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.

Vetasi offering

We have people with DBA knowledge so we can discuss things with your DBAs.

Our team have specialist tools and experience at debugging query related performance problems. We have been called on a number of occasions to analyse systems implemented by others.

Maximo user group meeting – 6th June – Environment Management round table

I’ll be hosting a round table discussion about Environment Management at the Maximo User Group meeting. If you can’t make the meeting then watch this blog for a future posting with instructions on how to download the associated hints and tips sheet.

As a support person I’ll be giving advice that you can action straight away without having to buy consultancy services.

This blog series

This article is one of a series of articles to help system administrators understand the Maximo logs and the underlying architecture.

If you like this article then please share or like it.

Whilst I support the wider Maximo community and encourage the spread of knowledge, when republishing content from this blog please include the originating author along with the article or parts of.

If people do find parts of this blog coming up in blogs/newsletters/communications then please contact me directly. I’m happy to connect on LinkedIn to discuss.

Disclaimer

The postings on this blog are my own and don't necessarily represent Vetasi's positions, strategies or opinions.

The materials on this site are provided "AS IS" and the author will not be liable for any direct, indirect or incidental damages arising out or relating to any use or distribution of them. Readers are advised to test any changes/recommendations thoroughly before use