Maximo Integration and Scripting

  • 1.  Closing db connection created via automation scripts

    Posted Mon March 21, 2022 10:53 AM
    Hello,

    We are trying to identify the open db connections that were not closed by the programs (auto script in our example). Given below is a small auto script that we developed to update the workorder table for costs. Can you please tell me whether am I closing the connections properly?

    ##################
    mxServer = MXServer.getMXServer()
    runAsUserInfo1 = mxServer.getUserInfo("IT-BS-MXINTADM")

    conKey = mxServer.getSystemUserInfo().getConnectionKey()
    con = mxServer.getDBManager().getConnection(conKey)


    strUpdateStat1 =" update workorder a "
    strUpdateStat1 +="  set a.actlabcost=(select sum(c.linecost)      from labtrans  c where c.siteid='FAC' and c.refwo=a.wonum), "
    strUpdateStat1 +="  a.actlabhrs=(select sum(d.regularhrs)         from labtrans  d where d.siteid='FAC' and d.refwo=a.wonum), "
    strUpdateStat1 +="  a.ACTINTLABCOST=(select sum(c.linecost)       from labtrans  c where c.siteid='FAC' and c.refwo=a.wonum), "
    strUpdateStat1 +="  a.ACTINTLABHRS=(select sum(d.regularhrs)      from labtrans  d where d.siteid='FAC' and d.refwo=a.wonum)"
    strUpdateStat1 +="  where a.orgid='LBNL' and a.siteid='FAC' and a.wonum in "
    strUpdateStat1 +="  ( select distinct y.refwo from labtrans y   where y.siteid='FAC' and y.transdate between (sysdate-50) and sysdate and y.lt1 is not null)"


    stmtUpdateStat1=con.prepareStatement(strUpdateStat1)
    stmtUpdateStat1.executeUpdate()

    try:
        stmtUpdateStat1.close()
        con.commit()

    except:
        con.rollback()

    strUpdateStat1 ="  update workorder a "
    strUpdateStat1 +="  set a.acttoolcost=(select sum(b.linecost) from tooltrans b where b.siteid='FAC' and b.refwo=a.wonum) "
    strUpdateStat1 +="  where a.orgid='LBNL' and a.siteid='FAC' and a.wonum in "
    strUpdateStat1 +=" (select distinct x.refwo from tooltrans x where x.siteid='FAC' and x.transdate between (sysdate-50) and sysdate and x.tt1 is not null)"

    stmtUpdateStat1=con.prepareStatement(strUpdateStat1)
    stmtUpdateStat1.executeUpdate()

    try:
        stmtUpdateStat1.close()
        con.commit()

    except:
        con.rollback()

     # At the end of the program release the db connection to the pool
    mxServer.getDBManager().freeConnection(conKey)
    con=None

    ##############
    Secondly, if any exception is thrown from the automation script, how to find which automation script is throwing that exception?

    [3/21/22 7:04:09:925 PDT] 000000ea SystemOut O 21 Mar 2022 07:04:09:925 [INFO] [MXServerFac01] [] Connection spid=:1039 is cleaned up when conkey is released. Missing close connection call.
    Create stack trace:
    psdi.server.DbConnectionWatchDog$ConnectionData.<init>(DbConnectionWatchDog.java:60)
    psdi.server.DbConnectionWatchDog.addConnection(DbConnectionWatchDog.java:252)
    psdi.server.ConRef.notifyDBConnectionWatchDog(ConRef.java:238)
    psdi.server.DBManager.getConnectionDetail(DBManager.java:1713)
    psdi.server.DBManager.getConnection(DBManager.java:1460)
    psdi.server.AppService.getDBConnection(AppService.java:572)
    sun.reflect.GeneratedMethodAccessor688.invoke(Unknown Source)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:55)
    java.lang.reflect.Method.invoke(Method.java:508)
    org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:186)
    org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:204)
    org.python.core.PyObject.__call__(PyObject.java:478)
    org.python.core.PyObject.__call__(PyObject.java:482)
    org.python.core.PyMethod.__call__(PyMethod.java:141)
    org.python.pycode._pyx134.f$0(<script>:53)
    org.python.pycode._pyx134.call_function(<script>)
    org.python.core.PyTableCode.call(PyTableCode.java:167)
    org.python.core.PyCode.call(PyCode.java:18)
    org.python.core.Py.runCode(Py.java:1386)
    org.python.core.__builtin__.eval(__builtin__.java:497)
    org.python.core.__builtin__.eval(__builtin__.java:501)
    org.python.util.PythonInterpreter.eval(PythonInterpreter.java:259)
    org.python.jsr223.PyScriptEngine.eval(PyScriptEngine.java:40)
    org.python.jsr223.PyScriptEngine.access$300(PyScriptEngine.java:20)
    org.python.jsr223.PyScriptEngine$PyCompiledScript.eval(PyScriptEngine.java:231)
    com.ibm.tivoli.maximo.script.JSR223ScriptDriver.evalScript(JSR223ScriptDriver.java:275)
    com.ibm.tivoli.maximo.script.AbstractScriptDriver.runScript(AbstractScriptDriver.java:155)
    com.ibm.tivoli.maximo.script.ScriptAction.applyCustomAction(ScriptAction.java:106)
    psdi.common.action.Action.executeCustomClass(Action.java:399)
    psdi.common.action.Action.executeAction(Action.java:311)
    psdi.common.action.Action.executeAction(Action.java:344)
    psdi.webclient.system.controller.SystemEventHandler.WFACTION(SystemEventHandler.java:1609)
    sun.reflect.GeneratedMethodAccessor684.invoke(Unknown Source)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:55)
    java.lang.reflect.Method.invoke(Method.java:508)
    psdi.webclient.system.controller.BaseInstance.handleEvent(BaseInstance.java:431)
    psdi.webclient.system.controller.BaseInstance.handleEvent(BaseInstance.java:425)
    psdi.webclient.system.session.WebClientSession.handleEvent(WebClientSession.java:1345)
    psdi.webclient.system.session.WebClientSession.processRequestEvent(WebClientSession.java:6160)
    psdi.webclient.system.session.WebClientSession.handleClientSideQueueRequest(WebClientSession.java:6116)
    psdi.webclient.system.session.WebClientSession.handleRequest(WebClientSession.java:889)
    psdi.webclient.system.session.AsyncRequestManager.handleRequest(AsyncRequestManager.java:555)
    psdi.webclient.system.session.AsyncRequestManager.manageRequest(AsyncRequestManager.java:253)
    psdi.webclient.system.controller.RequestManager.manageRequest(RequestManager.java:290)
    psdi.webclient.servlet.WebClientServlet.handleRequest(WebClientServlet.java:141)
    psdi.webclient.servlet.WebClientServlet.doPost(WebClientServlet.java:52)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
    com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1235)
    com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:779)
    com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:478)
    com.ibm.ws.webcontainer.servlet.ServletWrapperImpl.handleRequest(ServletWrapperImpl.java:179)
    com.ibm.ws.webcontainer.filter.WebAppFilterChain.invokeTarget(WebAppFilterChain.java:143)
    com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:96)
    psdi.webclient.system.filter.HttpCrossSiteScriptingSecurity.doFilter(HttpCrossSiteScriptingSecurity.java:66)
    com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:197)
    com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:90)
    psdi.webclient.system.filter.MXCorrelationFilter.doFilter(MXCorrelationFilter.java:137)
    com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:197)
    com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:90)
    psdi.webclient.system.filter.PerformanceMonitor.doFilter(PerformanceMonitor.java:126)
    com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:197)
    com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:90)
    psdi.webclient.system.filter.HttpXFrameOptionsFilter.doFilter(HttpXFrameOptionsFilter.java:38)
    com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:197)
    com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:90)
    psdi.webclient.system.filter.MTContextFilter.doFilter(MTContextFilter.java:53)
    com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:197)
    com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:90)
    com.ibm.ws.webcontainer.filter.WebAppFilterManager.doFilter(WebAppFilterManager.java:969)
    com.ibm.ws.webcontainer.filter.WebAppFilterManager.invokeFilters(WebAppFilterManager.java:1109)
    com.ibm.ws.webcontainer.servlet.CacheServletWrapper.handleRequest(CacheServletWrapper.java:82)
    com.ibm.ws.webcontainer.WebContainer.handleRequest(WebContainer.java:963)
    com.ibm.ws.webcontainer.WSWebContainer.handleRequest(WSWebContainer.java:1817)
    com.ibm.ws.webcontainer.channel.WCChannelLink.ready(WCChannelLink.java:382)
    com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleDiscrimination(HttpInboundLink.java:465)
    com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleNewRequest(HttpInboundLink.java:532)
    com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.processRequest(HttpInboundLink.java:318)
    com.ibm.ws.http.channel.inbound.impl.HttpICLReadCallback.complete(HttpICLReadCallback.java:88)
    com.ibm.ws.ssl.channel.impl.SSLReadServiceContext$SSLReadCompletedCallback.complete(SSLReadServiceContext.java:1833)
    com.ibm.ws.tcp.channel.impl.AioReadCompletionListener.futureCompleted(AioReadCompletionListener.java:175)
    com.ibm.io.async.AbstractAsyncFuture.invokeCallback(AbstractAsyncFuture.java:217)
    com.ibm.io.async.AsyncChannelFuture.fireCompletionActions(AsyncChannelFuture.java:161)
    com.ibm.io.async.AsyncFuture.completed(AsyncFuture.java:138)
    com.ibm.io.async.ResultHandler.complete(ResultHandler.java:204)
    com.ibm.io.async.ResultHandler.runEventProcessingLoop(ResultHandler.java:775)
    com.ibm.io.async.ResultHandler$2.run(ResultHandler.java:905)
    com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:1909)


    ​​

    ------------------------------
    Pankaj Bhide
    Computer Systems Engineer
    Berkeley National Laboratory
    Berkeley CA
    ------------------------------



    #AssetandFacilitiesManagement
    #Maximo
    #MaximoIntegrationandScripting


  • 2.  RE: Closing db connection created via automation scripts

    IBM Champion
    Posted Tue March 22, 2022 09:02 AM
    Edited by System Tue August 22, 2023 04:43 PM
    In this case, you need to make use of the mxServer.getDBManager().freeConnection(conKey) method.

    Something like this:

    con = None
    try:
        con = mxServer.getDBManager().getConnection(conKey)
        strUpdateStat1 = " some sql statement "
        stmtUpdateStat1 = con.prepareStatement(strUpdateStat1)
        stmtUpdateStat1.executeUpdate()
        con.commit()
    except:
        if con:
            con.rollback()
    finally:
        if con:
            try:
                mxServer.getDBManager().freeConnection(conKey)
            except:
                service.log("Failed to close connection")


    Hope this helps,

    ------------------------------
    Alex Walter
    A3J Group
    ------------------------------

    Original Message


  • 3.  RE: Closing db connection created via automation scripts

    Posted Tue March 22, 2022 10:34 AM
    Thanks Alex for your help.



    Original Message


  • 4.  RE: Closing db connection created via automation scripts

    Posted Tue March 22, 2022 09:12 AM
    It's a bit personal preference, but I prefer to utilize DBShortcut. There's a lot of good error handling, logging, and connection management in DBShortcut. I documented running a SELECT statement utilizing DBShortcut here: MORE Maximo DBShortcut. In addition to query, you can perform updates/inserts/deletes by calling .execute(SqlFormat) instead of .executeQuery(String). In that scenario you need to provide a SqlFormat object (so you wouldn't call the .format() like I did in the example). And then you can call the .commit() and/or .rollback() in DBShortcut. The .close() in the finally releases the connection back to the pool and in the query scenario ensures the result set is closed. 

    As for triaging where this occurs, my best recommendation is to look to see if you can see what the last query was for that connection in Oracle for that SPID. Sometimes the query is unique enough that you can pinpoint the script from that. The stack trace here indicates that it's an ACTION launch point automation script invoked in the UI so it helps narrow the scripts down some but it's hard to get more specific. Because the dbconnection isn't an exception in the traditional sense, you can't see the automation script that caused the exception. Now if your automation script was actually failing (throwing an exception outside of the try catch) then that should log the automation script name.

    ------------------------------
    Steven Shull
    ------------------------------

    Original Message


  • 5.  RE: Closing db connection created via automation scripts

    Posted Tue March 22, 2022 10:36 AM
    Hello Steve, I agree. DBShortcut seems simple and straightforward. I will implement it that way. Thanks a lot!



    Original Message


  • 6.  RE: Closing db connection created via automation scripts

    Posted Wed March 23, 2022 10:58 AM
    Hello, Quick clarification.

    We have now mxServer.getDBManager().freeConnection(conKey) in the finally clause, so it is guaranteed to return the conkey to the pool.

    However the DbConnectionWatchDog class shows following message:

    [3/21/22 6:22:38:929 PDT] 0000c31c SystemOut O W0278299 ... 0
    [3/21/22 6:22:39:756 PDT] 000000ea SystemOut O 21 Mar 2022 06:22:39:756 [INFO] [MXServerFac01] [] Connection spid=:936 is cleaned up when conkey is released. Missing close connection call.
    Create stack trace:
    psdi.server.DbConnectionWatchDog$ConnectionData.<init>(DbConnectionWatchDog.java:60)
    psdi.server.DbConnectionWatchDog.addConnection(DbConnectionWatchDog.java:252)
    psdi.server.ConRef.notifyDBConnectionWatchDog(ConRef.java:238)
    psdi.server.DBManager.getConnectionDetail(DBManager.java:1713)
    psdi.server.DBManager.getConnection(DBManager.java:1460)
    psdi.server.AppService.getDBConnection(AppService.java:572)
    sun.reflect.GeneratedMethodAccessor688.invoke(Unknown Source)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:55)
    java.lang.reflect.Method.invoke(Method.java:508)
    org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:186)
    org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:204)
    org.python.core.PyObject.__call__(PyObject.java:478)
    org.python.core.PyObject.__call__(PyObject.java:482)
    org.python.core.PyMethod.__call__(PyMethod.java:141)
    org.python.pycode._pyx134.f$0(<script>:53)
    org.python.pycode._pyx134.call_function(<script>)
    org.python.core.PyTableCode.call(PyTableCode.java:167)
    org.python.core.PyCode.call(PyCode.java:18)
    org.python.core.Py.runCode(Py.java:1386)
    org.python.core.__builtin__.eval(__builtin__.java:497)
    org.python.core.__builtin__.eval(__builtin__.java:501)
    org.python.util.PythonInterpreter.eval(PythonInterpreter.java:259)
    org.python.jsr223.PyScriptEngine.eval(PyScriptEngine.java:40)
    org.python.jsr223.PyScriptEngine.access$300(PyScriptEngine.java:20)
    org.python.jsr223.PyScriptEngine$PyCompiledScript.eval(PyScriptEngine.java:231)
    com.ibm.tivoli.maximo.script.JSR223ScriptDriver.evalScript(JSR223ScriptDriver.java:275)
    com.ibm.tivoli.maximo.script.AbstractScriptDriver.runScript(AbstractScriptDriver.java:155)
    com.ibm.tivoli.maximo.script.ScriptAction.applyCustomAction(ScriptAction.java:106)
    psdi.common.action.Action.executeCustomClass(Action.java:399)
    psdi.common.action.Action.executeAction(Action.java:311)
    psdi.common.action.Action.executeAction(Action.java:344)
    psdi.webclient.system.controller.SystemEventHandler.WFACTION(SystemEventHandler.java:1609)
    sun.reflect.GeneratedMethodAccessor684.invoke(Unknown Source)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:55)
    java.lang.reflect.Method.invoke(Method.java:508)
    psdi.webclient.system.controller.BaseInstance.handleEvent(BaseInstance.java:431)
    psdi.webclient.system.controller.BaseInstance.handleEvent(BaseInstance.java:425)
    psdi.webclient.system.session.WebClientSession.handleEvent(WebClientSession.java:1345)
    psdi.webclient.system.session.WebClientSession.processRequestEvent(WebClientSession.java:6160)
    psdi.webclient.system.session.WebClientSession.handleClientSideQueueRequest(WebClientSession.java:6116)

    I guess it is okay. Right? Does it mean that it has returned the connection to the pool and since can't close that connection it may be complaining about it. 

    The line Connection spid=:936 is cleaned up when conkey is released. Missing close connection call.

    ------------------------------
    Pankaj Bhide
    Computer Systems Engineer
    Berkeley National Laboratory
    Berkeley CA
    ------------------------------

    Original Message