limit on no. of statements exceeded

[shri...@my-deja.com]

Hi ,
Has anyone ever encounter this problem. I am running WAS 3.02 AE on
Win NT with SP6 on IBM HTTP server . I am using the Connection pooling
provided by WAS with min connection 2 and max connection 600 to connect
to database on AS400.

The application runs fine from morning to evening but ib the evening
starts giving problems ie. i get

"limit on number of statements exceeded ". I am releasing the
connection every time the transaction is over.

Any help will be greatly appreciated.

-Shrikant M

Sent via Deja.com http://www.deja.com/
Before you buy.

[Ken Hygh]

I'm not sure about your specific problem, but a max of 600 connections in the
pool is 'way higher than you need. We have cases of supporting several hundred
accesses per second with _5_ max connections.

You could be killing your database by opening too many connections to it.

Ken

[Michael Burke]

This is a problem with one of the WebSphere 3.0.2 internal statement
caches. Basically, statements in the cache are not being reused; hence, new
statement objects are created. Eventually, you are reaching the limit
imposed by the DB2/400 database for the number of open statements; hence,
the "limit on number of statements exceeded" error.

This problem is fixed in WebSphere 3.0.2.1; hence, I recommend that you
install the fixpack on your NT machine.

- Mike

[shri...@my-deja.com]

Hi Michael,

I am already at IM WAS 3.0.2.1 AE ??? As Ken said is 600 max
connections too much should i reduce it to half ? or is there anyway
which forces the statements in the cache to be reused ??

-Shrikant M

In article <393F9F94...@NOSPAM.us.ibm.com>,

[Michael Burke]

Shrikant,

If you are at 3.0.2.1, then the statement cache should be working
properly. To verify the WebSphere version you are running under, simply
look in your administrative server log:

<WASROOT>/logs/tracefile

if you are starting WebSphere from the NT service. The WebSphere version
will be listed in the first line of the log file.

If you are indeed at version 3.0.2.1, then I would consider reducing the
max number of connections. The Toolbox JDBC driver on the AS/400 allows a
maximum of 255 open statements. That is, 255 statements can be open at any
given time.

[shri...@my-deja.com]

Hi Michael,

As you said I went ahead to look into tracefile for the version but
I found this exception ...

002.187 120fad AdminServer A Initializing WebSphere Administration
server
002.187 120efd DrAdminServer A DrAdmin available on port 1,784
053.094 120fad AdminServer A WebSphere Administration server open for
e-business
690.172 120fad AdminServer F Encountered an exception in the main
method java.lang.NullPointerException

FATAL Encountered an exception in the main method {0}
java.lang.NullPointerException

002.157 120fad AdminServer A Initializing WebSphere Administration
server
002.172 120efd DrAdminServer A DrAdmin available on port 1,864
051.969 120fad AdminServer A WebSphere Administration server open for
e-business
55484.000 120efd DBMgr W Exception on database query (read by
primary key):
55484.157 23cc5a ConnectionSyn W Failed to roll back
COM.ibm.db2.jdbc.app.DB2Exception: [IBM][CLI Driver] CLI0106E
Connection is closed. SQLSTATE=08003
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException
(Compiled Code)
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_return_code
(SQLExceptionGenerator.java:353)
at COM.ibm.db2.jdbc.app.DB2Connection.rollback
(DB2Connection.java:565)

This looks like something serious to me can you give me any lead on
this.

Thanks a lot , I appreciate your help.
-Shrikant M

In article <393FD207...@NOSPAM.us.ibm.com>,

[Ken Hygh]

Make sure you've got the correct fixpack for the DB2 level (see Getting
Started), and that you do have 3.02.1.

Ken

[Greg Dinning]

This may have nothing to do with the problem, but are you invoking the
close()
method on your Statement objects when you're done with them?

--
Greg Dinning
Department of Software
gr...@departmentofsoftware.com
http://www.departmentofsoftware.com

<shri...@my-deja.com> wrote in message news:8i8i7n$8na$1...@nnrp1.deja.com...