Performance Tuning of Maximo Attachment -
DOCLINKS for IBM Maximo

In the last part of this series, we have seen what are the different steps which 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. 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 which 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 what ever 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)
)
  
• When there is an attachment created on Asset or Location or Job Plan or PM or any other object, Maximo copies that Attachment to related Work Order and hence out of the box DOCLINKs relationship for Work Order contains different objects inside relationship. So, if you see new relationship using UNION ALL also has all those objects available. If your organization don’t attach document on some related objects, then those clauses can be removed from the relationship. For example- if requirement is to have attachments only on ASSET, LOCATION, JOBPLAN and PM along with Work Order to be given in API Response and inner queries related with them should be available along with Work Order inner query and all other inner queries should be removed from relationship resulting in 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 ))
)
• 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.

Author: 

PRASHANT SHARMA

Principal Solutions Architect