Hi Dirk,
As Witold says partitioning a table isn't supported. I know some enterprise customers have implemented this using custom scripts / processes.
Organisations tend to try this because they have huge tables and they can see benefits.
However there are several elements to remember:
- Manage manages the Maximo table structure and that it can drop tables and recreate them, without the partitioning - altering a table can lead to the table being renamed, a new table being created and the data being copied
- Recreating the partitioning requires custom scripts / operations and Manage doesn't currently support execute custom scripts - there is an idea in place for this
- If the partitioning isn't recreated then the performance problem returns - this is also seen when unsupported index types are used
- IBM Support don't support non-standard implementations so there is a risk that they may ask you to demonstrate the problem on a database where partitioning has not been implemented
Systems which have huge numbers of rows can often benefit from other supported solutions:
- Moving old records to a reporting database - BIRT reports can be designed to query both the standard Maximo database and an archive database simultaneously
- Changing working practices to reduce the number of "active" records - One major chokepoint is the number of active workorders. See below for a quick example
- Tuning queries to ensure that they work as efficiently as possible - Indexing custom columns used in where clauses can deliver substantial performance improvements
- Check what is actually using the database resources - A major customer found that changing a single escalation's schedule from every 10 seconds to every minute dramatically improved their database's performance
If you try these and you still have problems then Cohesive offer consultancy that involves evaluating your system and the associated logs/components to see where the pain points are / how to fix them.
How large numbers of active workorders can cause problems
==
A large customer kept years worth of workorders in the main Maximo system.
Typically when I review a system I find that the number of active/open workorders is a small percentage of the records and this means that the query engines can make efficient use of indexes.
Given the size of the organisation I expected the proportion of a open workorders to be small in comparison to the closed workorders.
When the data was checked it was apparent that there was a large proportion of open workorders that had never been touched and weren't going to be touched for at least 6 months.
The customer's working practices meant that PMs were used to generate workorders - conceptually there is nothing wrong with this and IBM encourage the use of PMs to automate workorder generation
The problem was that the PMs were generating workorders for work for the next year.... so some workorders would stay open for over 50 weeks before they were ever actioned, this meant that the indexes were not being selected because they weren't deemed to be efficiently enough.
The recommended solution was to change how PMs were scheduled so that only workorders required in the near future were generated - this would massively reduce the number of open workorders and so increase index usage
------------------------------
Mark Robbins
Technical Design Authority / IBM Champion 2017 - 2025
Cohesive (previously Vetasi Limited)
https://www.linkedin.com/pulse/maximo-support-advice-from-non-ibm-engineer-article-mark-robbins/------------------------------