JDBC connection goes stale (CA API 9.4)

[Laurent LANDREAU]

Hello all,
I have created a JDBC connection to a SQLServer 2012 database, I test the connection (let's say CONNECTION1) and it works fine.
The connection name is then used in the different API policies.
After a while, the APIs using that connection ALL fail.

When I retest the JDBC connection, I have: Testing failed: unexpected problem during connection.
Note: the database itself has had no change.

Actions I did: 
a) When I rename CONNECTION1 under another name (CONNECTION2) -> CONNECTION2 works. 
b) I then remove CONNECTION1, wait 30 minutes, and then recreate it using the same name CONNECTION1 -> it continues to fail.
c) I then rename CONNECTION1 as CONNECTION3 -> CONNECTION3 works.

Obviously I do not want to change all the policies using the database connection.

1) Is that a known problem? If yes, is there a software fix to it? 
2) Is there a workaround for this in the meantime? 






------------------------------
Regards, 
Laurent LANDREAU
+61.451.838.336
------------------------------

[Leandro Dantas]

Hi, Laurent.

if you restart gateway CONNECTION1 works again?
If you rename CONNECTION2 or CONNECTION3 to CONNECTION1, it works?

Try look at gateway logs to see if there is any other message related to this issue. You may have to change log level to FINE or FINER in log sink to get some connection pool detailed error.


------------------------------
Sr. Consultant Services
HCL Enterprise Studio
------------------------------

[Laurent LANDREAU]

One way out is the following:
* delete CONNECTION1
* restart the gateway
* recreate CONNECTION1

... not something we can do when we go to production.
So that is somewhat acceptable in development, but not when we reach business critical platforms.

[Ozan Okan]

Dear Laurent,

I guess you don't have much traffic on this JDBC connection. Gateway uses c3p0 database connection pooling and c3p0 closes connections on inactivity.

I believe if you set following additional properties in JDBC connection it might help:

testConnectionOnCheckout   =>   to true  ,  c3p0 checked
testConnectionOnCheckout   => to true,        c3p0  checked
preferredTestQuery  => Select 1                , c3p0 checked

Regards

[Laurent LANDREAU]

Thanks - will try that.