Maximo

 View Only

  • 1.  Tips and tricks: SQL queries in Maximo?



  • 2.  RE: Tips and tricks: SQL queries in Maximo?

    Posted Tue December 01, 2020 05:10 AM
    Edited by System Test Wed March 22, 2023 11:54 AM

    #AssetandFacilitiesManagement
    #Maximo


  • 3.  RE: Tips and tricks: SQL queries in Maximo?

    Posted Tue December 01, 2020 02:18 PM
    Edited by System Test Wed March 22, 2023 11:45 AM
    It is always good to see posts that bring together useful links.
    Thank you for including one of my articles.

    One of the most useful changes is to improve the performance of the doclinks queries.
    These queries use OR statements to bring together the rows from multiple tables. Switching these to UNION ALL can improve the query performance.

    This technote shows how the workorder tracking doclinks query can be improved:
    https://www.ibm.com/support/pages/performance-issue-work-order-tracking-application

    The changes can be implemented on other applications.

    Vetasi offer specialist training to help people understand how to tune SQL statements.
    We also have a number of tools to help system administrators identify problem queries and which indexes could be relevant.

    ------------------------------
    Mark Robbins
    Support Lead/Technical Design Authority / IBM Champion 2017 & 2018 & 2019 & 2020
    Vetasi Limited
    Bristol
    https://www.linkedin.com/pulse/maximo-support-advice-from-non-ibm-engineer-article-mark-robbins/
    ------------------------------

    Original Message


  • 4.  RE: Tips and tricks: SQL queries in Maximo?

    Posted Sun December 06, 2020 11:08 AM
    Thanks for your input Mark.
    Original Message


  • 5.  RE: Tips and tricks: SQL queries in Maximo?

    Posted Tue December 08, 2020 09:39 AM
    Edited by System Test Wed March 22, 2023 11:55 AM
    What I appreciate the most is the possibility to use of RelationShips in the SearchMore dialog box.
    Maximo automatically replaces in the RelationShip any :bindingvariable with its counterpart in the Main MBo of your App.
    A very useful example is for retrieving ALL PMs related to one Location Ancestor, whatever the PM is related to an asset and/or to a location. Here is the recipe :
    1) Add a Relation From PM to LocAncestor, say cust_primsyslocpm, with this WHERE CLAUSE :
    systemid=(select systemid from locsystem where primarysystem='1' and siteid=:siteid) and siteid=:siteid and ((location=:location) or location=(select location from asset where siteid=:siteid and assetnum=:assetnum))
    2) Add this in the SearchMore dialog box of PM.XML
    <textbox dataattribute="cust_primsyslocpm.ancestor" id="qbe_grid6_1_1_11_custo" inputmode="query" lookup="locations" menutype="locations" applink="location" label="Search REALLY by ancestor"/>
    3) Now, you are able to search for ALL PMS related to one ancestor (any PMs with asset related to this ancestor plus any PM related to this ancestor).
    The RelationShip is indeed used and adapted "on the fly" by the Qbe.class with 2 actions :
    - Replacement of any :bindingvariable with its counterpart in the Main MBo of your App,
    - Addition of the criteria included in the SearchMore.
    So this RelationShip,
    systemid=(select systemid from locsystem where primarysystem='1' and siteid=:siteid) and siteid=:siteid and ((location=:location) or location=(select location from asset where siteid=:siteid and assetnum=:assetnum))
    enable the easy building of this WhereClause :
    (siteid = 'BEDFORD') and (exists (select 1 from maximo.locancestor where ((ancestor like '%BOILER%')) and (systemid=(select systemid from locsystem where primarysystem='1' and siteid=pm.siteid) and siteid=pm.siteid and ((location=pm.location) or location=(select location from asset where siteid=pm.siteid and assetnum=pm.assetnum)))))
    search result including all PM with asset and location related to one ancestor


    ------------------------------
    Hugues LOMBARD
    ------------------------------

    Original Message