Re: SQL stored procedures and activation groups via JDBC

Building off Dan's reply....

Mark,

You may want to ask if there are settings you can configure that
control the connection pool used by the app. In particular, see if
the app offers anything to control min/max number of idle connections
in the pool along with perhaps a max reuse type of parameter.

HTH,
Charles

On Fri, May 15, 2009 at 12:30 PM, Dan Kimmel <dkimmel@xxxxxxxxxxxxxxx> wrote:

Badly managed connection pooling can get real ugly on the i. Connection
pools are designed to have a base number of connections, but they can
expand that number if all the base connections are "busy". If an
application doesn't close a connection properly and return it to the
pool, it remains busy. More and more connections are added to the pool
which results in more and more QZDASOINIT jobs running on the i. Some of
the other database engines will drop these idle connections fairly
silently while the i tends to be a lot more tenacious. This, of course,
affects perceived database performance rather badly. The i is really
doing what it is supposed to, while the others are actually behaving
rather badly in order to keep performance up.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Elvis Budimlic
Sent: Friday, May 15, 2009 10:57 AM
To: 'Midrange Systems Technical Discussion'
Subject: RE: SQL stored procedures and activation groups via JDBC

I've read all of the replies and still don't know what the mystical
resource issue is.

So, going off my experience with multi-tiered applications, common
complaint from the non-i Java developers is that System i doesn't
perform well.  I hear this time and time again.  Most often case is that
System i does not have a DBA to do performance database and SQL
performance tuning and make their newly rolled out (cross-platform)
application perform.  They expect it to just run the same way as it does
on their (dedicated) workstation.
Someone has to take on the performance tuning task, but who's going to
volunteer?  System administrator?  And then go in the defense mode
right-away as Java developers start denigrating the System i platform.
Fact that SQL performance tuning is not easy doesn't make system admin
jump at this "opportunity" so this new app is often 'orphaned'.

Now, as to this situation... there are two possible connection pools you
may be talking about.  Some Java middleware (i.e. Websphere, but others
do as
well) allows for application server connection pooling.  This would
override database connection pooling you're used to seeing on the System
i (where QZDASOINIT jobs are recycled 200 times, each time connection is
closed).
The application server pooling would instead maintain the connection
open (QZDASOINIT would stay connected, in RUN or TIMW state) and it
would service many different (Java) users. This is the situation where
reusable ODPs may accumulate within the job, since each user may be
running a different type of workload.  If you're unhappy with this type
of connection pooling, tell your Java developers to turn off connection
pooling in the application server and rely on System i database host
server connection pooling instead (meaning when they run the Close()
method on the connection, QZDASOINIT would indeed be recycled and all
resources cleaned up).

Again, based on my experience, connection pooling is not a problem.  In
fact, application server connection pooling is designed to enhance
performance (by keeping the connection open). Problem is more often with
SQL and database performance tuning that was never done with System i in
mind.
While it'll run new workloads without complaint, query optimizer needs
help to run it well. Your lowest hanging fruit here is building
appropriate set of SQL indexes, but it doesn't necessarily stop there.

Sorry it this is totally of mark, but I think it may help a bit.

Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400 www.centerfieldtechnology.com


-----Original Message-----
Subject: Re: SQL stored procedures and activation groups via JDBC


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.



--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.