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