Re: JT400 / JTOpen Statement Pooling -- JAVA400-L

Hi Etienne,
just some remarks to a view points:

On Wednesday 19 March 2003 04:43, you wrote:
> Joe,
>
> 1. Statements Pool (Caching)
> When creating a prepared statement (with default settings) the JT400 jdbc
> driver communicates with the AS/400. When I then execute the prepared
> statement the jdbc driver will again communicate with the AS/400. If I were
> to prepare the same statement on the same connection I see the jdbc driver
> communicating for the prepared and for the execute (The statement has
> already been prepared on the connection and there is no need to communicate
> with the AS/400 when preparing the same statement on the same connection).
> http://www-106.ibm.com/developerworks/java/library/j-jdbcnew/  (Prepared
> statement pooling)

thats the point where caching of prepared statements starts. the prepare 
won't happen, if an already prepared statement is available.

>
> When I have extended dynamic enabled I do not see the jdbc driver
> communicating with the AS/400 if I were to prepare a statement for a second
> time (if it was already prepared on the connection). For this to be work
> properly you must use parameter markers in the sql statement.
>
> 2. JT400 Settings
> The specific JT400 properties that I was referring to are "extended
> dynamic", "package", "package cache" and "package library".
>

normally I'm using these 3 properties by default and when the application is 
finished we evaluate that by measurment (DB Monitor).

> The troubling aspect of the conversation was that they indicated that JT400
> does not do statement pooling. In WebSphere when you define a connection
> pool you can set the statement cache size, but the Java code is not running
> on WebSphere. They suggested that we write our own statement pooling code
> (They indicated that JT400 does do caching of access paths when you use
> extended dynamic).
>
> The web application is performing in excess of 240,000 queries per hour.
> Reducing overhead of the jdbc driver is very important for us at this time.
> If each prepare is communicating with the AS/400 you have at least 240,000
> interactions with the AS/400 that could have been avoided.
>
> Is there a difference between "extended dynamic" and prepared statement
> pooling (lets use the prepared statement pooling description as explained
> in the article on developer works)?

extended dynamic: is working for all statements, that the database assumes to 
be preparable and writes its data to a package. you can only configure to 
write them all or none. you don't need changes to your application, just a 
driver property. the package is persistent and has to be deleted by hand, to 
remove garbage, which is producing overhead. is driver dependent.

prepared statement pooling: works on application level with every driver. 
caches only really prepared statements. for diffrent pools there are diffrent 
levels of caching: some cache on connection levele, some on connection pool 
level, second version is preferred. the statement pool is transient.

I would recommend: first step start with Database Monitor, there you will see 
if it is worth the afford to implement statement caching.

There are some open source solutions implementing statement caching, but I 
don't have experience with it, we've done it at application level by our own, 
because we did it pre JDBC 3.

Dieter

>
> Etienne
>
> -----Original Message-----
> From: java400-l-bounces@xxxxxxxxxxxx
> [mailto:java400-l-bounces@xxxxxxxxxxxx]On Behalf Of Joe Pluta
> Sent: Tuesday, March 18, 2003 10:14 PM
> To: Java Programming on and around the iSeries / AS400
> Subject: RE: JT400 / JTOpen Statement Pooling
>
>
> Etienne, why are you troubled?  IBM owns JT400 (and JTOpen), so I would
> have thought you would have asked them these questions.
>
> Let me try to address a few things, though:
>
> 1. What do you think prepared statement pooling is?  Remember, JDBC 3.0
> doesn't say anything about the implementation of PSP, simply that there is
> an interface for it.  Now, do you expect PSP to work across sessions,
> across connections, or only within a single connection?
>
> 2. Which settings?
>
> 3. I don't know that they relate at all.
>
> 4. I recommend connection pooling for any application that has an unknown
> but potentially very large number of connections.  This is typically the
> case in an Internet application.  For a smaller number of users, I still
> prefer persistent connections held at the session level.
>
> > -----Original Message-----
> > From: java400-l-bounces@xxxxxxxxxxxx
> > [mailto:java400-l-bounces@xxxxxxxxxxxx]On Behalf Of Etienne Richards
> > Sent: Tuesday, March 18, 2003 8:30 PM
> > To: java400-l@xxxxxxxxxxxx
> > Subject: JT400 / JTOpen Statement Pooling
> >
> >
> > All (and Joe Pluta),
> >
> > Today I had a nice long conference call with IBM regarding JDBC and
> > Connection Pooling, Prepared Statement Pooling and Extended Dynamic
> > settings.
> >
> > I am troubled...
> >
> > 1. Does the latest JT400 jdbc driver do prepared statement pooling?
> >     1.1. If it does, how do I enable it?
> >     1.2. If it does not, when will it?
> >
> > 2. Is it worth using the extended dynamic settings that are available in
> > JT400 if the client is not running on an AS/400?
> >
> > 3. How does caching of Access Paths (Extended Dynamic) and Prepared
> > Statement pooling relate to each other. How are the caching of
> > Access Paths
> > and ODP's different (as viewed from a jdbc client)?
> >
> > 4. Would you recommend using the JT400 connection pool for
> > applications that
> > are not running in WebSphere?
> >
> > Any comments or answers would be appreciated.
> >
> > Thanks,
> > Etienne
>
> _______________________________________________
> This is the Java Programming on and around the iSeries / AS400 (JAVA400-L)
> mailing list
> To post a message email: JAVA400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/java400-l
> or email: JAVA400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/java400-l.
>
>
>
>
> _______________________________________________
> This is the Java Programming on and around the iSeries / AS400 (JAVA400-L)
> mailing list To post a message email: JAVA400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/java400-l
> or email: JAVA400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/java400-l.

-- 
mfG

Dieter Bender


DV-Beratung Dieter Bender
Wetzlarerstr. 25
35435 Wettenberg
Tel. +49 641 9805855
Fax +49 641 9805856
www.bender-dv.de