Skip to Content

SAP MII + Closed connection error while executing a query to Oracle database

Hello Experts,

We are connecting to an Oracle database (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production) from SAP MII 14.0 SP5 Patch 7 using ojdbc5.jar.

The connection was setup fine and we were able to run queries. But after some time we are getting the below messages in log viewer. We have around 20 queries running separated by one minute. The query generally takes few seconds to return the results.

If I disable the data server It does not allow me to enable the same as well. But if we run the same query with the same user from PL-SQL developer or another SAP MII system it runs fine.

The parameters configured on the data base servers are:

Pool Max.: 200

Pool Size: 150

Timeout:150

Wait Time: 60

Use Count:256

Believe it is to do with some setting on the server as it is the only place where the issue is coming. Any guidance to fix the issue.

Error Messages:

com.sap.xmii.Illuminator.logging.LHException: java.sql.SQLRecoverableException: Closed Connection Unable to run the query [EXCEPTION] com.sap.xmii.Illuminator.logging.LHException: com.sap.xmii.Illuminator.logging.LHException: java.sql.SQLRecoverableException: Closed Connection at com.sap.xmii.Illuminator.connectors.AbstractConnector.processRequest(AbstractConnector.java:140) at com.sap.xmii.Illuminator.server.QueryEngine.run(QueryEngine.java:65) at com.sap.xmii.Illuminator.server.QueryEngine.run(QueryEngine.java:46) at com.sap.xmii.Illuminator.services.handlers.IlluminatorService.processRequest(IlluminatorService.java:154) at com.sap.xmii.Illuminator.services.ServiceManager.run(ServiceManager.java:69) at com.sap.xmii.mdo.core.MDOEngine.executeQueryTemplate(MDOEngine.java:277) at com.sap.xmii.kpi.core.KPIEngine.executeDataProvider(KPIEngine.java:213) at com.sap.xmii.kpi.core.KPIEngine.executeTask(KPIEngine.java:104) at com.sap.xmii.kpi.scheduler.KPIWorker.execute(KPIWorker.java:188) at com.sap.xmii.ejb.JobBean.onMessage(JobBean.java:89) at sun.reflect.GeneratedMethodAccessor285.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at com.sap.engine.services.ejb3.runtime.impl.RequestInvocationContext.proceedFinal(RequestInvocationContext.java:47) at com.sap.engine.services.ejb3.runtime.impl.AbstractInvocationContext.proceed(AbstractInvocationContext.java:166) at com.sap.engine.services.ejb3.runtime.impl.Interceptors_StatesTransition.invoke(Interceptors_StatesTransition.java:19) at com.sap.engine.services.ejb3.runtime.impl.AbstractInvocationContext.proceed(AbstractInvocationContext.java:179) at com.sap.engine.services.ejb3.runtime.impl.Interceptors_Resource.invoke(Interceptors_Resource.java:50) at com.sap.engine.services.ejb3.runtime.impl.AbstractInvocationContext.proceed(AbstractInvocationContext.java:179) at com.sap.engine.services.ejb3.runtime.impl.Interceptors_MessageListenerType.invoke(Interceptors_MessageListenerType.java:111) at com.sap.engine.services.ejb3.runtime.impl.AbstractInvocationContext.proceed(AbstractInvocationContext.java:179) at com.sap.engine.services.ejb3.runtime.impl.AbstractInvocationContext.proceed(AbstractInvocationContext.java:191) at com.sap.engine.services.ejb3.runtime.impl.Interceptors_StatelessInstanceGetter.invoke(Interceptors_StatelessInstanceGetter.java:23) at com.sap.engine.services.ejb3.runtime.impl.AbstractInvocationContext.proceed(AbstractInvocationContext.java:179) at sun.reflect.GeneratedMethodAccessor278.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at com.sap.engine.services.ejb3.util.BeanMethods.executeReflectMethod(BeanMethods.java:314) at com.sap.engine.services.ejb3.jacc.ExecuteMethodPrivilegedAction.run(ExecuteMethodPrivilegedAction.java:38) at java.security.AccessController.doPrivileged(Native Method) at javax.security.auth.Subject.doAs(Subject.java:394) at com.sap.engine.services.ejb3.jacc.ContainerJ2EESecurityProviderBaseImpl.doAs(ContainerJ2EESecurityProviderBaseImpl.java:262) at com.sap.engine.services.ejb3.runtime.impl.Interceptors_RunAs.invoke(Interceptors_RunAs.java:24) at com.sap.engine.services.ejb3.runtime.impl.AbstractInvocationContext.proceed(AbstractInvocationContext.java:179) at com.sap.engine.services.ejb3.runtime.impl.Interceptors_SecurityCheck.invoke(Interceptors_SecurityCheck.java:25) at com.sap.engine.services.ejb3.runtime.impl.AbstractInvocationContext.proceed(AbstractInvocationContext.java:179) at com.sap.engine.services.ejb3.runtime.impl.Interceptors_ExceptionTracer.invoke(Interceptors_ExceptionTracer.java:17) at com.sap.engine.services.ejb3.runtime.impl.AbstractInvocationContext.proceed(AbstractInvocationContext.java:179) at com.sap.engine.services.ejb3.runtime.impl.Interceptors_Lock.invoke(Interceptors_Lock.java:21) at com.sap.engine.services.ejb3.runtime.impl.AbstractInvocationContext.proceed(AbstractInvocationContext.java:179) at com.sap.engine.services.ejb3.runtime.impl.DefaultInvocationChainsManager.startChain(DefaultInvocationChainsManager.java:138) at com.sap.engine.services.ejb3.runtime.impl.DefaultEJBProxyInvocationHandler.invoke(DefaultEJBProxyInvocationHandler.java:164) at com.sap.engine.services.ejb3.runtime.impl.MDBProxyInvocationHandler.invoke(MDBProxyInvocationHandler.java:78) at com.sun.proxy.$Proxy493.onMessage(Unknown Source) at com.sap.jms.client.session.JMSSession.deliverMessage(JMSSession.java:1042) at com.sap.jms.client.session.JMSSession.run(JMSSession.java:886) at com.sap.jms.resourceadapter.RaServerSession.run(RaServerSession.java:139) at com.sap.engine.services.connector.jca15.work.TaskImpl.run(TaskImpl.java:410) at com.sap.engine.core.thread.execution.Executable.run(Executable.java:122) at com.sap.engine.core.thread.execution.Executable.run(Executable.java:101) at com.sap.engine.core.thread.execution.CentralExecutor$SingleThread.run(CentralExecutor.java:328) Caused by: com.sap.xmii.Illuminator.logging.LHException: java.sql.SQLRecoverableException: Closed Connection at com.sap.xmii.Illuminator.connectors.IDBC.IDBC.doProcessRequest(IDBC.java:115) at com.sap.xmii.Illuminator.connectors.AbstractConnector.processRequest(AbstractConnector.java:112) ... 50 more Caused by: java.sql.SQLRecoverableException: Closed Connection at oracle.jdbc.driver.PhysicalConnection.setAutoCommit(PhysicalConnection.java:3790) at com.sap.xmii.Illuminator.connectors.IDBC.FixedQueryProcessor.processRequest(FixedQueryProcessor.java:125) at com.sap.xmii.Illuminator.connectors.IDBC.IDBC.doProcessRequest(IDBC.java:112) ... 51 more

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • Best Answer
    Posted on Oct 08, 2018 at 09:34 PM

    It has been a while, but I remember having problems where the pool size was greater than that configured in Java. A simple check would be to reduce the pool size and max to double digits or even single digits to see if that fixes the problem. Might also want to see how the Java pool settings are configured and make sure that you don't exceed those limits. My memory dates back to 12.2, but I suspect the version is not going to affect the connection allocation process.

    Good luck, Mike Appleby

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Oct 22, 2018 at 05:38 PM

    Thanks Mike. I changed

    Pool Max.: 15

    Pool Size: 15

    and it has been working fine

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.