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

[darshansheth]

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

[agentry_src · Accepted solution]

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

[darshansheth]

Thanks Mike. I changed

Pool Max.: 15

Pool Size: 15

and it has been working fine