Quick Maximo assessment

My job brings me across Europe to meet many different customers and work with a vast variety of Maximo environments. One of the most important tasks is to be able to quickly assess a Maximo environment that I have never seen before.

How much data?

One of the tools I use is a set of SQL queries that simply counts the rows of the most important Maximo tables. With the result of these script I can quickly spot unused features, large tables, and other useful insights.
Here is the SQL script. Feel free to use it and to suggest improvements.

SELECT count(*) FROM asset;
SELECT count(*) FROM assetattribute;

SELECT count(*) FROM assettrans;
SELECT count(*) FROM ci;
SELECT count(*) FROM cirelation;
SELECT count(*) FROM doclinks;
SELECT count(*) FROM invbalances;
SELECT count(*) FROM inventory;
SELECT count(*) FROM item;

SELECT count(*) FROM locations;
SELECT count(*) FROM site;

SELECT count(*) FROM pr;
SELECT count(*) FROM prline;
SELECT count(*) FROM po;
SELECT count(*) FROM poline;
SELECT count(*) FROM invoice;
SELECT count(*) FROM invoiceline;
SELECT count(*) FROM matrectrans;
SELECT count(*) FROM servrectrans;

SELECT count(*) FROM ticket;
SELECT count(*) FROM workorder;
SELECT count(*) FROM jobplan;
SELECT count(*) FROM pm;

SELECT count(*) FROM measurement;
SELECT count(*) FROM meter;
SELECT count(*) FROM meterreading;

SELECT count(*) FROM wfaction;
SELECT count(*) FROM wfassignment;
SELECT count(*) FROM wfinstance;
SELECT count(*) FROM wfnode;
SELECT count(*) FROM wftask;

SELECT count(*) FROM person;
SELECT count(*) FROM maxuser;
SELECT count(*) FROM maxgroup;

SELECT siteid, count(*) FROM asset GROUP BY siteid;
SELECT siteid, count(*) FROM workorder GROUP BY siteid;

In this other two articles are described two queries to list big tables in Maximo database schema:

Large tables can affect Maximo performances. In such cases a good solution is to archive old data from such large tables to maintain an acceptable system performances. IBM has a dedicated solution called Maximo Archiving with Optim Data Growth Solution that may worth evaluating.

Maximo Health Check report

There is now a better and easier way to have the same information and much more details about potential performance issues in Maximo just by running a BIRT report.
Check out the Maximo Health Check report on developerWorks.

How much customized?

Checkout this post for listing all Maximo customizations.

Quick Maximo assessment

One thought on “Quick Maximo assessment

  1. For tables such as WORKORDER, shouldn't your SQL also filter "WHERE istask=0" so that you get a true count of work orders and not work orders PLUS work order tasks (which is what your query retrieves)?

Leave a Reply

Your email address will not be published. Required fields are marked *

Scroll to top