CLI0129E No more handles 2

[Ruvera]

In a high volume transaction environment I have a servlet
read/write operations on a database for each time that it is called. When using a low transaction rate everything works just fine. However, when stressing the system DB2 comes back with the following message:
IBM][CLI Driver] CLI0129E No more handles. SQLSTATE=HY014.

How can I resolve this problem?

 

[MarcLodge]

Ruvera,
I think that there this is a problem with resources and there appears to be a number of possible ways to fix it:

1) If the servlet has a CURSOR WITH HOLD (which is the default) change this to CURSOR WITHOUT HOLD as there is a difference in the number of handles each can hold. This may not be in line with what you application requires tho.

2) Add a counter in the calling program so that when a predefined limit is reached, you issue a COMMIT or Call SQLFreeEnv, SQLFreeConnect, or SQLFreeStmt. Start this predefined limit low and work upwards until you reach a limit that is acceptable to the application. Once again, this may not be possible within the business context of your application.

3) If you are using WebSphere Application Server Version 3.5 then I believe that fix pack 3 addresses a problem where if SelectStatement is called 1500 or more times in a user servlet CLI0129E is returned

Hope some of this is relevant to your problem.

Marc

 

[Ruvera]

How to change CURSOR WITH HOLD on CURSOR WITHOUT HOLD ?

I work with operational system Windows 2000, IBM DB2 7, Patch 7, JDK 1.3.

 

[MarcLodge]

Hi again Ruvera,
I think you'll find the info that yourequire on the CURSOR here:

http://www-3.ibm.com/cgi-bin/db2www...r.d2w/report?target=mainFrame&fn=c0007649.htm

Don't forget, that if you are not using a CURSOR, then this isn't your problem.

Marc

 

[Hattusas]

I believe you don't close your java.sql.Statements do you?
If you don't close your statements you are sure to get that error message.
Salih Sipahi
Software Engineer.
City of Istanbul Turkey
ssipahi@yonbilgi.com