Problem Summary:
The Database connections are not being released by the adapter even when the service has ended. All the connection are shown as busy connections although the service threads running are only 1. From the operating system process state (ps -eaf|grep oracle) we observed that all the 50 connections are being used. The webMethods service that uses those connections is called via HTTPS (We are using LoadRunner to call HTTPS service. Only 10 request/minutes. In fact we tried with single request). And the service ends properly without any errors. And from webMethods web interface we can see that there is only 1 service thread running. But still the database connection remains active.
The same service is running on a different machine with identical configuration and there is no such problem there.
The adapter is configured as “NO_TRANSACTION” , we also tried to set it as “LOCAL_TRANSACTION” and modified the service to use explicit transaction. But then we get “Transaction is Active” error in the error log.
I have searched thoroughly on advantage forums/knowledge base , but did not find anything. On 18th May goethals posted this message which is related. http://advantage.webmethods.com/cgi-bin/advantage/main.jsp?w=0&targChanId;d=-536880993&tid=161061;18322

Environment:
Product webMethods Integration Server
Version 6.0.1
Updates IS_6-0-1_SP2_Fix86
IS_6-0-1_SP2_Fix73
IS_6-0-1_SP2
IS_6-0-1_SP2_Fix118

Build Number 425

JDBC Adapter:

Description JDBC Adapter
Adapter Version 6.0.2
Vendor Name webMethods

Java Version:
Java Version 1.3.1 (46.0)
Java Vendor IBM Corporation
Java Home /usr/java130/jre

Oracle Version:
Oracle 8i (8.1.7)

OS version:
AIX 5.1

Connection Pool Settings:
Transaction Type NO_TRANSACTION
DataSource Class oracle.jdbc.pool.OracleDataSource
serverName localhost
user xxxx
password ******
databaseName xxxx
portNumber 1521
networkProtocol
Other Properties driverType=thin
Connection Management Properties
Enable Connection Pooling true
Minimum Pool Size 1
Maximum Pool Size 50
Pool Increment Size 1
Block Timeout (msec) 30000
Expire Timeout (msec) 1000

Following exception appears in the Integration Server Error log:

(MemData:exId=117.4012,exBundle=com.wm.pkg.art.i18n.resources.AdapterRuntimeResourceBundle,exError=117,exReason=4012,exDfltMsg=null,exWrapped=com.wm.pkg.art.error.DetailedException: [ART.118.5053] Adapter Runtime (Connection): Unable to get a connection to resource sabbLogging.dbAccess.adapter:eBankAdapter.
The pool doesn’t have a connection available for the request.,exParmcnt=1,exParm=sabbLogging.dbAccess.adapter:eBankAdapter,date=Sun Jul 11 22:05:30 AST 2004,sessionId=SEJEL00001,sessionName=173.1.205.62,sessionId=96zjM9PcC328cpExO2S276vMdsdMU=555596,serviceCallStack=[Ljava.lang.String;@e79ef96,reason=[ART.118.5053] Adapter Runtime (Connection): Unable to get a connection to resource sabbLogging.dbAccess.adapter:eBankAdapter.
The pool doesn’t have a connection available for the request.,exceptionClass=com.wm.pkg.art.error.DetailedException,stack=com.wm.pkg.art.error.DetailedException: [ART.118.5053] Adapter Runtime (Connection): Unable to get a connection to resource sabbLogging.dbAccess.adapter:eBankAdapter.
The pool doesn’t have a connection available for the request.
at com.wm.pkg.art.ns.ConnectionResource.createConnection(ConnectionResource.java:187)
at com.wm.pkg.art.transaction.ConnectionState.getConnection(ConnectionState.java:153)
at com.wm.pkg.art.ns.AdapterServiceNode.invokeService(AdapterServiceNode.java:533)
at com.wm.pkg.art.ns.ARTNSService.baseInvoke(ARTNSService.java:47)
at com.wm.app.b2b.server.ServiceManager.invoke(ServiceManager.java(Compiled Code))
at com.wm.app.b2b.server.BaseService.invoke(BaseService.java(Compiled Code))
at com.wm.lang.flow.FlowInvoke.invoke(FlowInvoke.java(Compiled Code))
at com.wm.lang.flow.FlowState.invokeNode(FlowState.java(Compiled Code))
at com.wm.lang.flow.FlowState.step(FlowState.java(Compiled Code))
at com.wm.lang.flow.FlowState.invoke(FlowState.java(Compiled Code))
at com.wm.app.b2b.server.FlowSvcImpl.baseInvoke(FlowSvcImpl.java(Compiled Code))
at com.wm.app.b2b.server.ServiceManager.invoke(ServiceManage
5951990b2edd9de3fdcf4f7704 NULL 5951990b2edd9de3fdcf4f7704
2004-07-11 22:05:30 sabbLogging.dbAccess.adapter:eBankAdapter [ART.118.5053] Adapter Runtime (Connection): Unable to get a connection to resource sabbLogging.dbAccess.adapter:eBankAdapter. The pool doesn’t have a connection available for the request.

I believe this might be due to the version of oracle you are using… We had some connections hanging on oracle 8.1.7.0 and upgraded to 8.1.7.4, might be something to look into.

We are using JDBC 6.0.3 adapter has not faced such a problem with connection pooling.

Just wanted to pass on the vote of confidence

Hi,

We have faced this issue in both development environment because of the explicit transactions are not commited or rolled back properly by the flow services. Each time for this error the IS needs to be restarted. You may need to check whther your service has a logical commit or rollback which will solve this problem.

I found there is one fix in IS 6.0.1 in SP3 release notes. We are in the process of aplying this fix in production.

1-JBFIB(IS_6-0-1_SP1_Fix43)
Integration Server is not properly destroying JDBC connections
when errors are encountered with them, thus creating resource
problems on the system that supports the database.

This fix corrects the problem by destroying all discarded
connections.

We are currently investigating IS 6.0.1 Fix150 combined with IS 6.0.1 Fix156 that addresses JDBC Adapter leaving currupted transaction threads within the Integration server.

Just wanted to let you guys know. I am able to reproduce currupted transaction by creating empty orphan transactions. Over a period of time this leaves even non-transactional database calls to fail. After applying the Fix 150 and Fix 156, we are able to resolve this issue.

Just wanted to my two cents.