In previous 2 posts we have seen what are the different steps to increase the API performance of Maximo APIs and how Performance Tuning of Maximo Attachment can be achieved. In this post we'll see what are additional steps that can be taken as part of Database Tuning to not only increase the performance of APIs but also Maximo UI.
Below are some key steps to consider-
1. Keep tables and indexes on two different tablespaces. OOB Maximo provides two default tablespaces MAXDATA and MAXINDEX.
- If you have kept all your tables and indexes on the same tablespace then for sure you will be facing performance problems.
- Your DBA needs to move indexes to different tablespace MAXINDEX and you need to update tablespace names on all tables from Database Configuration and then perform ConfigDB.
2. Index Rebuilding and Gather Statistics is another important task that must happen on a periodic frequency of 1 week on all database tables over weekend.Database Jobs can be used to schedule these queries.
- If you have an API Object Structure which has multiple objects and each object involves very high amount of data operations Read/Insert/Update/Delete happening example- Work Order or WFASSIGNMENT or Reorder, then you should run Gather Stats and Index Rebuild at earlier frequency like once in a year over nightly.
One of the best hybrid approach taken can be running scheduled jobs for Gather Stats and Index rebuild is - A. Run every night which can be only for tables where data amount and no. of database operations and really high + B. Run weekly job for Gather Stats and Index Rebuild on weekly basis for remaining tables.
3. Monitor database performance - Database Connection State, Database Health, Long Running, Locking and Wait state of Database table on weekly basis. Following reports/utilities should be used to monitor these details for different databases-
AWR - Oracle
Configure Data Collector - Microsoft SQL Server
Utilize MONREPORT module - DB2
4. Use PerfMon - Maximo Performance Monitor or Maximo Management Interface (MMI) to see the long running queries and MBO states in Maximo memory. Enabling PerfMon was not recommended on Production Instance due to its performance impact on Maximo. In recent versions of Maximo 7.6.x, Maximo Management Interface (MMI) has also been added which can also be used to monitor the Maximo system performance.set of REST APIs that enable HTTP-based clients to access information that is related to the current state of a Maximo deployment. In the next post, I will describe the syntax of these jobs to be used for different databases supported - Oracle, MS SQL Server and IBM DB2.I hope this article will help you to troubleshoot and navigate through API performance issues in the future.
Author:
Prashant Sharma
Delivery Head- IBM Maximo & EAM360 Mobile App
