Database connection timeout

, ,
21

Hi Sonam,

Is it necessary for me to go for Connection pooiling if I need to use the directive you have proposed.

Neelima

22

I am not sure Neelima - that directive should work with or without Connection pooling.

This problem does not affect either of the two types of database connections I use at work:

  1. TN<–>Merant<–>Oracle
    This does not seem at all affected by firewall timeouts.

  2. IS<–>Oracle
    This connection isn’t affected either.
    I assume that is because this connection is low volume
    and the flow open and close a new connection for each request.

Other applications in the same network have been affected.

23

Neelima/Sonam/Srini,
We are experiencing a new problem with our database connections. We are using connection pooling and we have ‘watt.server.db.testSQL’ directive specified in server.cnf. Once we enabled connection pooling and enabled the directive mentioned before, our null pointer exceptions were gone and all the SQL services started running fine. The new problem that we are facing is that after 1 or 2 hours of continuous testing, none of the services are able to get connections. The number of active connections/available connections (using wm.server.db.getConnectionInfo method) is always greater than one. number of active connections are not even close to max pool size.
We double checked to make sure that we are explicitly closing the connections in our code.
To describe about the services, we open the connection using connect first, then we have try catch sequence blocks, in the try block we do some processing which might take more than 1 or 2 minutes in some cases and there might be some execSQL statements in between. once all the processing is done we close the connection. we also use close method in the catch block if dbConnection object is not null.

during processing, there are chances that firewall might timeout our initial connection object. but i guess since we are using watt.server.db.testSQL directive we get a new connection in our try block. what do u think might happen to the connection that was dropped by the firewall? Would IS return the connection to the pool?

any thots on this will be very helpful

Thanks in advance
Hari

24

Hari

<my2cents>

Maybe not very relevant to your problem, but I wish to point out two things that you should try experimenting

  1. we had noticed that connect and close connection when used with execSql made it very slow. when the connect and close connection was removed the execution time dropped to fraction of what it took before.

  2. If its inserting 10000’s of records that you do in a loop it can be done without a loop. You just need to create a document list that exactly matches the table structure with all the records and just call pub.db:insert once to insert all the records. I had only tried “inserting” using this technic and am not sure if it would work with other things as well.

</my2cents>

Thahir

25

Hari - If you are using connection pooling, you need to be very careful. Every time you use wm.db:call or pub.db:execSQL you must make an explicit call to pub.db:call. If you do not the connection will never be made availible to the DB Pool. Hence, you will eventually run out of connection in the pool, despite what your active connections say. You may want to check all code that is running on that server to make sure you are closing all connection. Even on failures.