Connection not getting closed in websphere

[Aniket Pathak]

Hi,

In my application I have some scheduled job which runs at a perticular time. In this I am dong some order processing. When I execute this job I get "SQL Exception: ORA-00020: maximum> number of processes (150) exceeded" exception.

I monitored the connection pool from PMI and found that connections are getting created but not getting closed. In the code I am properly closing the connection , I also confirmed that the code is getting executed. But in PMI CloseCount does not show any cloed connection.

Following is the code for closing the connection

public boolean cleanup()
throws SQLException
{
boolean conClosed = false;
if(resSet != null)
resSet.close();
if(stmt != null)
stmt.close();
if(prepStmt != null)
prepStmt.close();
if(conn != null)
{
conn.close();
conClosed = true;
}
return conClosed;
}

this code works fine in webslogic. There no such error there.

Please help me in this. Do I need to add any Websphere specific configuration or code to close the connection?

Thankx

[Jaikiran Pai]

When and from where will this cleanup method be called?

[Aniket Pathak]

Hi,

As I mentioned I do some order processing in the threads. When ever I open any connection in a perticular method I call this cleanup method in after the execution gets complete.

my most calls to cleanup method is in "finally block".

Thankx.

[Jaikiran Pai]

SQL Exception: ORA-00020: maximum> number of processes (150) exceeded

This is a different problem than keeping the Connections open. A little bit of googling led me to a link which mentioned that you need to do changes to Oracle initialization to increase the number of processes to some specific value. By default, it is 150. I guess, you have to change the setting in init.ora file.

[Jaikiran Pai]

From the Oracle error message documentation:

ORA-00020 maximum number of processes (string) exceeded
Cause: All process state objects are in use.
Action: Increase the value of the PROCESSES initialization parameter.

[Aniket Pathak]

Hi,

yes I have made changes in the Oracle file and changed processes from 150 to 250 but still the exception remains same.

Actualy I migrated my application from weblogic to websphere and the same code is working fine in weblogic with same database. When I monitor PMI its gives connections more than 150. When the Oracle exception occurs all connections get closed.

I am closing all the connection after their execution gets over, but still in PMI i do not see any closecount increase. I am just wondering why its working fine in Weblogic but gives exception in Websphere ..........

Thankx

[Jaikiran Pai]

Are you creating connections through Datasources. If yes, the connections are pooled and the actual connection may not be closed when you invoke the connection.close(). They will just be returned to the pool. What's the max pool size that you have specified on your datasource, if at all you are using one?

[Aniket Pathak]

Hi,

I am using connection pool. When I set pool size to anysize like 30 / 40/70 etc. It gives "Connection waiting: timed out" exception. So I set the size 0 which is unlimited.

Max: = 0
Min: = 0 (which has to be equal or greater than MAX size)

I am suspecting the same that when I am calling .close() method connections are actualy not getting closed. Is there anything else I need to add which may be websphere specifig so that connection will get closed. I search a lot on this problem but did not got any solutions.

Thankx

[Jaikiran Pai]

When I set pool size to anysize like 30 / 40/70 etc. It gives "Connection waiting: timed out" exception.

That means that your process is using connections more than 30/40/70 at a given time.

What is your process doing? Why does it require around 150 or more connections when it is running. I believe you will have to fix this issue of using too many connections, rather than trying to set the max pool size to a high value. Does your process actually need so many connections?

[Aniket Pathak]

Hi,

no, I think things are getting clubs together. Following is the step wise description

1) I have a thread on execution of which websphere is giving exception
2) There are multiple processes included into the thread which uses DB connections.
3) In all the processes I get/create a new connection from connection pool of websphere and I also close it when that processes gets complete. As we do normaly.

So in the complete execution connection gets created and get closed. So Pool size (alive connections) should not get increaed so much. But it is getting increased which is the worry.

For debugging I increased the connection pool size to 30/40/70 etc. normaly I required around MAX = 30 Pool size.

When i doing proper management of connection i.e. closing connection when process is completed, why it should get exceeded upto 150.
Weblogic manages it very well and poll size in weblogic does not even cross 20 connection.


Please let me know if you want nay further information.
Thankx

[Jaikiran Pai]

1) I have a thread on execution of which websphere is giving exception
2) There are multiple processes included into the thread which uses DB connections.
3) In all the processes I get/create a new connection from connection pool of websphere and I also close it when that processes gets complete. As we do normaly.

Can you post the code of the Thread and the process that create and release the connections(Remember to wrap the code contents in a code tag, using the 'Code' button provided). That might help in figuring out if there are any issues with the code.

Weblogic manages it very well and poll size in weblogic does not even cross 20 connection.

Interesting. But i havent used Weblogic, so have no idea as to what might be different over there.