March 22, 2013

How to improve DocLinks performances in Maximo/TPAE applications

During a performance assessment of a production customer environment I have set the mxe.db.logSQLTimeLimit system property to 2000 to log all long-running SQL statements (see this TechNote for details). Looking at the system logs I have noticed many entries like this.

select * from doclinks where (ownertable='WORKORDER' and ownerid=1065680) or (ownertable='WORKORDER' and ownerid in (select workorderid from workorder where ...) execution took 4653 milliseconds

Five seconds each time you view a work order in Work Order Tracking application just to check if the there are any attached document is reeeaaally baaad!

Those long and heavy SQL queries are executed by Maximo whenever an application is opened or a new record is inserted into an application to determine if there are any documents attached to the object or one of its related records. This slow down significantly Maximo UI.
After having googled a little I discovered that the IBM System Performance Whitepaper suggests two approaches to solve this issue.
  1. Set the enabledoclinkonload system property to false to turn off the doclinks queries.
  2. Simplify the doclinks queries by removing unnecessary checks in unused child tables as described in this post.
Both approaches have their drawbacks.
  1. By disabling the docklinks check the paperclick icon will always turned on as described in this TechNote. Users will loose a useful information and needs to manually check if there are attached documents. Furthermore, this setting will disable the query only when opening objects and not for saves.
  2. Removing part of the SQL query will only slightly improve performances and can be practically a non effective solution when your users use Maximo at its best.
That's why I have decided to follow another path... optimize the SQL statement.

Doclinks queries is composed by several subqueries. You can find the docklink query for work orders in WORKORDER.DOCKLINK relationship definition in Database Configuration. Here is how it looks like after a little restyling.

(ownertable='WORKORDER' and ownerid=:workorderid) or
(ownertable='WORKORDER' and ownerid in (select workorderid from workorder where parent=:wonum and istask=1 and siteid=:siteid)) or
(ownertable='ASSET' and ownerid in (select assetuid from asset where assetnum=:assetnum and siteid=:siteid)) or
(ownertable='LOCATIONS' and ownerid in (select locationsid from locations where location=:location and siteid=:siteid)) or
(ownertable='JOBPLAN' and ownerid in (select jobplanid from jobplan where jpnum=:jpnum and (siteid is null or siteid=:siteid))) or
(ownertable='PM' and ownerid in (select pmuid from pm where pmnum=:pmnum and siteid=:siteid)) or
(ownertable='SAFETYPLAN' and ownerid in (select safetyplanuid from safetyplan,wosafetyplan where safetyplan.safetyplanid=wosafetyplan.safetyplanid and wosafetyplan.wonum=:wonum and wosafetyplan.siteid=:siteid)) or
(ownertable in ('SR','INCIDENT','PROBLEM') and ownerid in (select ticketuid from ticket,relatedrecord where ticketid=recordkey and ticket.class = relatedrecord.class and relatedrecclass=:woclass and relatedreckey=:wonum and relatedrecsiteid=:siteid)) or
(ownertable in ('WOCHANGE','WORELEASE','WOACTIVITY') and ownerid in (select workorderid from workorder,relatedrecord where wonum=recordkey and workorder.woclass = relatedrecord.class and relatedrecclass=:woclass and relatedreckey=:wonum and relatedrecsiteid=:siteid)) or
(ownertable='COMMLOG' and ownerid in (select commloguid from commlog where ownerid=:workorderid and ownertable='WORKORDER')) or
(ownertable='SLA' and ownerid in (select slaid from sla,slarecords,workorder where sla.slanum=slarecords.slanum and slarecords.ownerid=workorder.workorderid and sla.objectname='WORKORDER' and slarecords.ownertable='WORKORDER' and workorder.wonum=:wonum))


Looking at the SQL access plan I discovered I much inefficient is this way of searching records in the DOCKLINK table.

I have rewritten the previous where clause using a UNION clause and I ended up with this SQL.

doclinksid in
(
select doclinksid from doclinks where (ownertable='WORKORDER' and ownerid in (select workorderid from workorder where ownerid=:workorderid or (parent=:wonum and istask=1 and siteid=:siteid))) union 
select doclinksid from doclinks where (ownertable='ASSET' and ownerid in (select assetuid from asset where assetnum=:assetnum and siteid=:siteid)) union 
select doclinksid from doclinks where (ownertable='LOCATIONS' and ownerid in (select locationsid from locations where location=:location and siteid=:siteid)) union 
select doclinksid from doclinks where (ownertable='JOBPLAN' and ownerid in (select jobplanid from jobplan where jpnum=:jpnum and (siteid is null or siteid=:siteid))) union 
select doclinksid from doclinks where (ownertable='PM' and ownerid in (select pmuid from pm where pmnum=:pmnum and siteid=:siteid)) union 
select doclinksid from doclinks where (ownertable='SAFETYPLAN' and ownerid in (select safetyplanuid from safetyplan,wosafetyplan where safetyplan.safetyplanid=wosafetyplan.safetyplanid and wosafetyplan.wonum=:wonum and wosafetyplan.siteid=:siteid)) union 
select doclinksid from doclinks where (ownertable in ('SR','INCIDENT','PROBLEM') and ownerid in (select ticketuid from ticket,relatedrecord where ticketid=recordkey and ticket.class = relatedrecord.class and relatedrecclass=:woclass and relatedreckey=:wonum and relatedrecsiteid=:siteid)) union 
select doclinksid from doclinks where (ownertable in ('WOCHANGE','WORELEASE','WOACTIVITY') and ownerid in (select workorderid from workorder,relatedrecord where wonum=recordkey and workorder.woclass = relatedrecord.class and relatedrecclass=:woclass and relatedreckey=:wonum and relatedrecsiteid=:siteid)) union 
select doclinksid from doclinks where (ownertable='COMMLOG' and ownerid in (select commloguid from commlog where ownerid=:workorderid and ownertable='WORKORDER')) union 
select doclinksid from doclinks where (ownertable='SLA' and ownerid in (select slaid from sla,slarecords,workorder where sla.slanum=slarecords.slanum and slarecords.ownerid=workorder.workorderid and sla.objectname='WORKORDER' and slarecords.ownertable='WORKORDER' and workorder.wonum=:wonum))
)

With this piece of code I went to Database Configuration and opened the DOCLINK relationship of WORKORDER object and I have pasted it in the 'where' field. So what were the results?

The average docklink check went from an average execution time of 4.5 seconds to 0.6 seconds with a 750% performance improvement. That's great!

If the above query is not giving the desired result you may also try using UNION ALL instead of UNION statements - thanks Elliot for the tip.

I have modified several docklinks queries defined in the Database Configuration with a similar approach. Many applications are now more responsive when opening and saving records and customer is now more than happy!

March 21, 2013

Is an Object in a workflow?

This entry is part of the Conditional Expressions HowTo.

If you need to make a field or an object readonly if it has an active and running workflow you are in right place!
All you need is a conditional expression that can be used in conditional UI or data restriction.
The following conditional expression evaluate to true if a Work Order is in a workflow.
  • Name: WOINWF
  • Description: True if WO is in WF
  • Expression: exists (select 1 from wfassignment where assignstatus='ACTIVE' and ownertable='WORKORDER' and ownerid=:workorderid )
If you need the opposite behaviour use this other conditional expression.
  • Name: WONOTINWF
  • Description: False if WO is in WF
  • Expression: not exists (select 1 from wfassignment where assignstatus='ACTIVE' and ownertable='WORKORDER' and ownerid=:workorderid )
Those expressions can be easily be modified to work with any object.

Now just use this conditional expression in the Application Designer or in Security Groups to change the behavior of such objects.

March 15, 2013

Understand and Audit your Maximo Licenses

Maximo licensing model is not simple. In this article I will try to present it the most simple way and to provide some useful tips to perform a licensing audit in your environment.

NOTE: The simplification does not consider all the possible versions of Maximo, add-ons, industry solutions, etc.

Maximo licensing model
IBM Maximo products are typically licensed by users. Currently, the IBM Maximo Asset Management suite offers several levels of licensing options:
  1. Authorized Users: Power users that can access all applications.
  2. Limited Use Users: Standard users that can access only three modules. They also inherit the Express Use grants.
  3. Express Use User: Basic users that can run and view reports, view and approve records, and make updates to work orders that are assigned to them. Read-only access to all application is allowed. This option is available starting from Maximo 7.5.
  4. Unlimited use users: Self Service Requestors, Desktop Requisitions and Health, Safety Environment Self Service Users entitlements are included with core license for unlimited users.

 
To support this categorization Maximo offers a 'User Type' field available in the Users application.


The set of allowed values is defined in the USERTYPE domain.


User Types Best Practice
I suggest to change the description of the first 4 domain values in agreement with the available user types. Alternatively you can create your own entries: AUTH, LIMITED, EXPRESS, UNLIMITED.

License compliance check

Now let's go to the technical side of the problem. The best way of auditing your Maximo environment's licenses is through SQL queries.
First of all you have to define a new database view.

DROP VIEW checkusers;

CREATE VIEW checkusers AS
SELECT maxuser.type, maxuser.userid, maxmenu.moduleapp, applicationauth.app
FROM applicationauth
INNER JOIN maxmenu ON applicationauth.app=maxmenu.keyvalue
INNER JOIN groupuser ON applicationauth.groupname=groupuser.groupname
INNER JOIN maxuser ON groupuser.userid=maxuser.userid
WHERE
    maxuser.status='ACTIVE' AND
    maxmenu.menutype='MODULE' AND
    maxmenu.moduleapp NOT IN ('REPORTING','HIDDEN') AND 
    applicationauth.app NOT LIKE '%CONFIG' AND
    applicationauth.app<>'KPI' AND
    applicationauth.optionname='SAVE'
GROUP BY maxuser.type, maxuser.userid, maxmenu.moduleapp, applicationauth.app;

Note that we are focusing only at SAVE grant because read only is allowed on all applications.

Now take a look at how your users are distributed on the different groups.

SELECT type, COUNT(*)
FROM checkusers
GROUP by type
ORDER BY type;

If the select returns an empty resultset you probably have a translated value in the STATUS field so you have to adjust the view definition.

To go deeper in your analysis you have to check if the users allocated to the different user types have the right level of access. The following queries returns how many modules are granted to each user and what these modules are.

SELECT type, userid, COUNT(*)
FROM checkusers
GROUP by type, userid
ORDER BY type, userid;

SELECT type, userid, moduleapp
FROM checkusers
GROUP BY type, userid, moduleapp
ORDER BY type, userid, moduleapp;

These are the main tools for performing the license audit. Compare your results with licensing terms and conditions.

Additional Resources

New licensing options for IBM Maximo
Monitoring license Compliance
Monitoring License Compliance in Maximo 7
New Maximo portfolio pricing adds flexibility to licensing model
Licensing Information and Usage Restrictions
IBM Maximo Licensing Demystified

March 13, 2013

How to enable Axis (Web Services) logging

This entry is part of the Maximo Integration Framework series.

Maximo uses the Apache Axis library to call external Web Services. Sometimes could be useful enable the logging for this library instead of sniffing connections and protocols to figure out what is going on.
Axis uses Apache commons logging for logging which in turn provides a generic interface to different logging tools (simple log, JDK logging, log4J,...). So the first configuration to set is which logging tool AXIS must use.
Such configuration must be done in the commons-logging.properties file which must be placed somewhere in the application server's CLASSPATH. This file is not delivered by Maximo so if not found the simple log is used by default. Otherwise the properties.jar file could be the right place. In the commons-logging.properties the property org.apache.commons.logging.Log must be set to the class implementation which can be:
  • Simple log: org.apache.commons.logging.impl.SimpleLog
  • JDK logger: org.apache.commons.logging.impl.Jdk14Logger
  • Avalon logger: org.apache.commons.logging.impl.AvalonLogger
  • Log Kit logger: org.apache.commons.logging.impl.LogKitLogger

Assuming we want to configure the simple logger (which logs into the standard error) the following configuration must be put into the commons-logging.properties.
org.apache.commons.logging.Log=org.apache.commons.logging.impl.SimpleLog

The simple log use in turn its own configuration file simplelog.properties that is located in the axis.jar file under [SMPDIR]\maximo\applications\maximo\lib. If you change this file you have to redeploy the Maximo EAR or just simply copy the modified axis.jar file to [WASDIR]\AppServer\profiles\ctgAppSrv01\installedApps\ctgCell01\MAXIMO.ear\lib and restart the server.

A special thank to my fellow Maximo consultant Marco Lerro for this article.

March 12, 2013

Checkpoints for fixpack and addon installation

Many customers I work with use a procedure like this one to clone Maximo environments between production, test and development environments. When you use such techniques, you may quickly screw up some settings used during installation of Maximo fixpacks and upgrades.
Here are some important things to check before installing any release, fixpack or add-on.

Maximo properties
First of all check the famous maximo.properties file under [SMPDIR]\maximo\applications\maximo\properties folder.
The installation shouldn't use the settings stored in this file but it's worth checking.

Database properties
Many properties are stored in the MAXPROPVALUE table. You can use the System Properties application to view and edit them. Some of the properties that should be checked are:
  • mxe.db.schemaowner
  • mxe.hostname
  • mxe.int.globaldir
  • mxe.workflow.admin
  • mxe.doclink.path01
Check also MAXVARS table.

Pending database updates
Check any pending database change in Database Configuration application or executing the following query:
SELECT count(*) FROM maxobjectcfg WHERE changed!='N';
If the result is empty than is OK.

Install properties
Installation properties are stored in [SMPDIR]\etc\install.properties.
In particular, check database connection, WebSphere hosts and ports.

WAS Thin Client
The WebSphere thin client is used to connect to the WebSphere server and deploy the Maximo web application.
Open [SMPDIR]\wasclient\ThinWsadmin.bat file and check the hostname for wsadminHost property.

Backup everything!!!
Last but not least remember to backup the following things:
  1. Maximo database
  2. SMP directory
  3. Deployment Engine database (see this technote for details)
  4. Reports: [SMPDIR]\maximo\reports\birt\reports

References

Updating environment data.

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.


How much customized?

Checkout this post for listing all Maximo customizations.

March 10, 2013

Show current logged users in MAXADMIN's Start Center

A useful portlet (result set) that I typically configure on MAXADMIN's Start Center is the list of users logged to Maximo server. I use this to perform unplanned system maintenance that requires setting admin mode or restarting the server.

To configure this portlet you first have to create the related query. Open Users application, input the following where clause (under Advanced Search menu) and press OK button.
userid in (select userid from maxsession where active=1 and issystem!=1)

Save the query with the following properties:
  • Query Name: LOGGEDUSERS
  • Description: Currently logged in users
  • Public: No
  • Default?: No
Open the MAXADMIN Start Center and add a Result Set portlet.
Edit the portlet and select the LOGGEDUSERS query. In the Column Display tab select the following columns: USERID, TYPE, PERSONID.