How to wait a JDBC Adapter Connection in the pool?

[gordon dong]

Hello,

When there is no available connections in the JDBC Adapter Connection pool, the error "The pool doesn't have a connection available for the request" will be gotten. And is there a way to let the service waiting for some minutes to get a connection from the pool instead of throwning an exception? Thanks.

[reamon]

Yes. Set the block timeout on the connection configuration. If after waiting the time specified the thread still isn't given a connection, the error will be thrown.

[gordon dong]

Thanks Rob.

[vkram]

Hi,

These are my JDBC Adapter connection properties.
Enable Connection true
Minimum Pool 0
Maximum Pool 20
Pool Increment 1
Block Timeout (msec) 1000
Expire Timeout 1000
Startup Retry Count 0
Startup Backoff Timeout 10



I have 20 connections and there is no way(that i can think of) that all the connections get busy at the same time but still I have received the error " The pool doesn't have a connection available for the request".And this happens very rare.

Is there a way to know why exactly this is happening.

Thanks,
Ram

[reamon]

The block timeout of 1000 msec (1 sec) is very short. You might consider increasing that.

You can get stats for the pool to see if you really are bumping into max pool size.

[sonam]

"You can get stats for the pool to see if you really are bumping into max pool size."

Hi Rob - I've run into a similar "The pool doesn't have a connection available for the request" issue, but with a custom adapter's thread pool.

Do you know if IS has built-in capabilities (in WmART or something) to check if a custom adapter's thread pool has hit the max. pool size?

Regards,
Sonam

[reamon]

The pub.art.connection:getConnectionStatistics could potentially be leveraged for this. I'm not sure but I think if TotalMisses is > 0 then either there was a connection timeout or a block timeout encountered in the past.

Of course the TotalConnections output will tell you the current number of connections if you want to programmatically check what's happening right now.

In the past I've set up a scheduled task to run each minute to call this service and log the results to a stats file, much like the stats.log IS does. This can provide some insight into connection usage.

[sonam]

Thanks Rob for the very insightful post. I've already gotten some insight based on your reply. I hope eventually to have logs written as you suggested. Using tools like Splunk, it could even be possible to correlate the adapter TotalMisses entries with server/ error log entries.

[sonam]

Based on Rob's comments, I wrote a service with the structure below. It gathers connection statistics for all WmART adapter connections on an IS instance, and presents them on an 'adapter statistics' webpage monitored by an external system monitoring tool.

This lets us monitor connectivity from webMethods to all adapter-connected systems (including webMethods -> wM system DBs)

Thanks again Rob!

INVOKE pub.art:listRegisteredAdapters
LOOP over /registeredAdapterList
   INVOKE pub.art.connection:listAdapterConnections
   LOOP over /connectionDataList
        INVOKE pub.art.connection:getConnectionStatistics
        Add info to 'adapter statistics' page