In the last part of this series, we have seen what the different steps are that can be taken to improve the performance of OSLC APIs in Maximo.
In this post we’ll talk about the next step of performance tuning of DOCLINKS—Maximo Attachments. The steps given below are not only applicable to APIs, but they can also be taken to improve the Maximo UI performance.
Following are the steps that we can take to improve the performance for DOCLINKS. Let’s understand this by taking example of Work Order attachment relationship—
- Disable attachments on UI or remove DOCLINKS from object structure if attachment-related functionality is not required.
- If DOCLINKS – Maximo Attachment functionality is required, then rewrite the out of the box SQL Query from whatever IBM provides out of the box
Out of the Box Relationship for DOCLINKS for WORKORDER Object-
(ownertable='WORKORDER' and ownerid=:workorderid)
or
(ownertable='WORKORDER' and ownerid in (select workorderid from workorder where parent=:wonum and istask=:yes 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='WORKORDER' 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='WORKORDER' and relatedreckey=:wonum and relatedrecsiteid=:siteid))
or
(ownertable='COMMLOG' and ownerid in (select commloguid from workorder,commlog where workorderid=ownerid and ownertable='WORKORDER'))
or
(ownertable='SLA' and ownerid in (select PLUSPSERVAGREEID 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))
We can rewrite this relationship using UNION ALL clause as-
doclinksid in
(
select doclinksid from doclinks where (ownertable='WORKORDER' and ownerid=:workorderid)
UNION ALL
select doclinksid from doclinks where (ownertable='WORKORDER' and ownerid in (select workorderid from workorder where parent=:wonum and istask=1 and siteid=:siteid))
UNION ALL
select doclinksid from doclinks where (ownertable='ASSET' and ownerid in (select assetuid from asset where assetnum=:assetnum and siteid=:siteid))
UNION ALL
select doclinksid from doclinks where (ownertable='LOCATIONS' and ownerid in (select locationsid from locations where location=:location and siteid=:siteid))
UNION ALL
select doclinksid from doclinks where (ownertable='JOBPLAN' and ownerid in (select jobplanid from jobplan where jpnum=:jpnum and (siteid is null or siteid=:siteid) and pluscrevnum =:pluscjprevnum) )
UNION ALL
select doclinksid from doclinks where (ownertable='PM' and ownerid in (select pmuid from pm where pmnum=:pmnum and siteid=:siteid))
UNION ALL
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 ALL
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 ALL
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 ALL
select doclinksid from doclinks where (ownertable='COMMLOG' and ownerid in (select commloguid from commlog where ownerid=:workorderid and ownertable in (:&synonymlist&_WOCLASS[ACTIVITY,CHANGE,RELEASE,WORKORDER])))
UNION ALL
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)
)
3.When there is an attachment created on an asset, location, job plan, PM, or any other object, Maximo copies that attachment to the related work order, and hence, out of the box, the DOCLINKs relationship for the work order contains different objects inside the relationship. So, if you see a new relationship using UNION ALL, it also has all those objects available. If your organization don’t attach a document to some related objects, then those clauses can be removed from the relationship. For example, if the requirement is to have attachments only on ASSET, LOCATION, JOBPLAN, and PM along with the work order to be given in the API response, and inner queries related to them should be available along with the work order inner query, and all other inner queries should be removed from the relationship, resulting in the below relationship with much better performance.
doclinksid in
(
SELECT doclinksid FROM workorder INNER JOIN doclinks ON (ownertable = 'WORKORDER' AND ownerid = :workorderid ) WHERE wonum = :wonum
UNION ALL
SELECT doclinksid FROM workorder INNER JOIN doclinks ON (ownertable = 'ASSET' AND EXISTS (SELECT 1 FROM asset WHERE assetnum = :assetnum AND siteid = :siteid AND ownerid = assetuid))
UNION ALL
SELECT doclinksid FROM workorder INNER JOIN doclinks ON (ownertable = 'LOCATIONS' AND EXISTS (SELECT 1 FROM locations WHERE location = :location AND siteid = :siteid AND ownerid = locationsid))
UNION ALL
SELECT doclinksid FROM workorder INNER JOIN doclinks ON (ownertable = 'JOBPLAN' AND EXISTS (SELECT 1 FROM jobplan WHERE jpnum = :jpnum AND ( siteid IS NULL OR siteid = :siteid ) AND pluscrevnum = :pluscjprevnum AND ownerid = jobplanid))
UNION ALL
SELECT doclinksid FROM workorder INNER JOIN doclinks ON (ownertable = 'PM' AND EXISTS (SELECT 1 FROM pm WHERE pmnum = :pmnum AND siteid = :siteid AND ownerid = pmuid ))
)
4.Database Tuning of Maximo attachment tables primarily DOCLINKS and DOCINFO. In the next part of this port, we’ll see steps to be considered for database table tuning on tables involved in APIs.
Hope this article will not only help you to troubleshoot and navigate through performance issue on OSLC APIs but also for slowness issue in Work Order Tracking application on Maximo UI when Maximo Attachments are involved.
