IV37227: DATABASE PERFORMANCE ISSUE CAUSED BY THE BDI TASKS

APAR status

• Closed as program error.

Error description

• Database performance issue caused by the BDI  (ACM)
  
  
  Asset Configuration Manager for MAXIMO: 7.1.1.2-20120824-0604
  Build 20110623-0835 DB Build V7130-05 HFDB Build HF7130-17
  
  
  Database performance improvement needed to the BDI End Item SQL.
  
  Several queries from the BDI adversely affect the overall
  MAXIMO database performance to the point where users cannot work
  quickly enough to get our trains ready for operation.
  
  
  Typical queries:
  
  select wo.TASKID, wp.ITEMNUM, (select i.description from item i
  where i.
  itemnum = wp.itemnum and i.itemsetid=wp.itemsetid) as
  description, wp.
  location, wp.ITEMQTY from workorder wo, wpmaterial wp where
  wp.wonum =
  wo.wonum and wp.siteid = wo.siteid and (wo.wonum =:"SYS_B_0" or
  wo.
  parent=:"SYS_B_1") and wo.ORGID = :"SYS_B_2" and wo.SITEID =
  :"SYS_B_3"
  
  select distinct a.assetuid, a.assetid, a.assetnum, a.siteid,
  a.orgid,
  coalesce((select updatedtime from plusaanbdistatus where assetid
  = a.
  assetid and siteid = a.siteid and inactivetime is null and
  currentconfig = :"SYS_B_00"), TO_TIMESTAMP (:"SYS_B_01" ,
  :"SYS_B_02"))
  from asset a inner join plusasaonoff b on a.assetid = b.assetid
  and a.
  siteid = b.siteid inner join plusacmxcat c on a.plusacatid =
  c.catid
  inner join plusacmbuild d on c.builditemid = d.builditemid where
  not
  exists (select :"SYS_B_03" from plusaanbdistatus where
  plusaanbdistatus.
  assetid = a.assetid and plusaanbdistatus.siteid=a.siteid and
  updatedtime >= TO_TIMESTAMP (:"SYS_B_04" , :"SYS_B_05")) and
  a.status
  in (select value from synonymdomain where maxvalue=:"SYS_B_06"
  and
  domainid=:"SYS_B_07") and b.parentassetid is null and b.offtime
  is null
  and not exists (select :"SYS_B_08" from plusaantran t where
  t.assetid=a.
  assetid and t.status= :"SYS_B_09" ) and d.indicator =
  :"SYS_B_10" and d.
  skipbdichecks=:"SYS_B_11" and a.siteid=:"SYS_B_12" and
  a.orgid=(select
  orgid from site where site.siteid=:"SYS_B_13") order by
  :"SYS_B_14" asc
  
  For the End Item instance, in Customers scenario, they currently
  have our 40 sites arranged in three groups to try to spread the
  load out according to their average daily run time.
  
  
  Is there a preferred/best practice regarding this that
  could help?
  
  Is there anything else we could consider to reduce the load the
  BDI generate on the database?
  
  *Environment:
  
  Base Services:  7.1.1.10-LA20120315-1126 Build 20111021-2230 DB
  Build V71110-16
  
  Maximo ACM:  Asset Configuration Manager for MAXIMO  :
  7.1.1.2-20120824-0604 Build 20110623-0835 DB Build V7130-05 HFDB
  Build  HF7130-17
  
  Maximo TR:   Transportation for MAXIMO :  7.1.1.1-20120817-1320
  Build 20110822-0000 DB Build V7121-04
  
  SAP:    SAP ERP 6.0 Adapter 7.1.0.3 Build 20091201-0631 DB Build
  V7100-04
  
  Change PMP   7.1.1.00 Build 20080502D DB Build V710-25
  Config PMP:   7.1.1.00 Build 20080424D2 DB Build V710-27
  Common PMP:  7.1.1.6 Build 20091130D DB Build V7116-05
  
  Other Versions
  
  Server OS: Windows Server 2003 5.2 build 3790 Service Pack 2
  Web Server:  Websphere 6.1.0.35
  Oracle:   10.2.0.4.0
  SAP:   Netweaver  PI  7.1
  

Local fix

• Dev to provide fix
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * Users of End Item BDI Crontask in Asset Configuration        *
  * Manager                                                      *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * The SQL statement used by the BDI End Item crontask to       *
  * identify which ACM managed Assets require processing by the  *
  * BDI can cause a large DB performance overhead.               *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Install the interim fix to resolve this issue.               *
  ****************************************************************
  This problem is especially evident in situations where multiple
  instances of the End Item crontask have been defined (e.g. for
  multiple Sites) or the schedule of the crontask is set to run
  frequently.
  

Problem conclusion

• BDI End Item Crontask SQL modifed to be more efficient.
  
  The fix for this APAR is contained in the following maintenance
  package:
    | release\fix pack | Interim Fix for Release 711 Asset
  Configuration Manager
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Modules/Macros

• MAXIMO
  

Fix information

• Fixed component name

  MX ASSET CONFIG

• Fixed component ID

  5724R4500

Applicable component levels

• R711 PSY

     UP