March 11, 2013

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.

1 comment:

  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)?

    ReplyDelete