IV37227: DATABASE PERFORMANCE ISSUE CAUSED BY THE BDI TASKS

Subscribe to this APAR

By subscribing, you receive periodic emails alerting you to the status of the APAR, along with a link to the fix after it becomes available. You can track this item individually or track all items by product.

Notify me when this APAR changes.

Notify me when an APAR for this component changes.

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 number

    IV37227

  • Reported component name

    MX ASSET CONFIG

  • Reported component ID

    5724R4500

  • Reported release

    711

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-02-27

  • Closed date

    2013-02-28

  • Last modified date

    2013-02-28

  • 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



Rate this page:

(0 users)Average rating

Document information


More support for:

IBM Maximo Asset Configuration Manager
Config Mgr

Software version:

711

Reference #:

IV37227

Modified date:

2013-02-28

Translate my page

Machine Translation

Content navigation