Re: Problem with jdbc based application -- JAVA400-L

Hang on here everyone... you are all getting mixed terminology and
issues... lets see if we can get to the bottom of some of this...

1) Shahar is not using the Native JDBC driver (which is the only one that
uses the CLI) so taking a CLI trace is not going to produce anything.  He
will be using 0 CLI handles always.

2) I don't know that the 'reached maximum number of statements' message has
anything to do with the ODPs being created.  (Meaning exactly that... I
don't *know* - I don't know a great deal about the Toolbox's internal
workings personally).  Then the native JDBC driver issues a similar
message, it means you tried to get too many statement handles all at once.
Now, if you have 2000 ODPs sitting around, you are not seeing just a
statement handle leak.  The total number of Statement handles the Toolbox
can have under a conection is something like 255.  Clearly, there is not a
1-1 mapping going on here, and I suspect that he is seeing an actual
problem from either the failure to reuse or the failure to close ODPs
(again, I am not the expert on that).

3) The advise to always close your statements in a finally block is good
advise for everyone, but because of my concerns in number 2 above, I don't
know that it will help in this case.

4) Alex, please send me personally an example of the behavior you are
describing if you can.  I will see to it that it gets resolved (Native JDBC
is my baby). Currently, I am not aware of any problems at all with
statement handle resources.

5) Just a tidbit:  The Native JDBC driver is going to increase the number
of statements that can be active under a connection from 500 (v4r4) to
40,000 (v4r5).  This feature is done, so I feel confident that I can
mention that it is going to ship.  This will allow people to seriously
consider not only pooling connection, but pooling statements as well.
Pooling prepared statements can make an application *really* perform well.
I will cover the concept a bit in my COMMON presentation in a week, so if
you are going to be there and are interested, try to check it out.
(Shameless plug - I offer my apologies)

6) Just a tidbit:  In the past in this group, I have explained a little bit
about why users need to explicitly close their Statements for handles to be
freed up (it had to do with the driver having a reference to the object  as
well as the user app, so if the user just throws away their reference, the
garbage collector still can't reclaim the object because the driver keeps
it accessible).  In v4r5, the Native JDBC driver will be able to recover
from handle leaks in user applications as long as you are using JDK 1.2 or
later (JDK 1.1.x will work as it does today).  The trick is to use a new
type of reference called a WeakReference.  This allows the garbage
collector to free the resources even if the Driver still has a (weak)
reference to it.  Check out the Javadoc for JDK 1.2 (java.lang.ref.
package) for more info if interested.

Now, I don't tell you about the WeakReference support because it means you
should never close a statement again once we get to v4r5.  The cleanup will
slow you down for short periods of time while the system handles the work
you could have done.  You should still plan to code what is considered
standard JDBC practices.  However, if your application misses a close or
two, the system will just periodically clean things up for you and you will
continue on your way instead of the system just periodically crashing for
you (or require rebooting) .

Regards,

Richard D. Dettinger
AS/400 Java Data Access Team

"TRUE! nervous, very, very dreadfully nervous I had been and am; but why
WILL you say that I am mad?
The disease had sharpened my senses, not destroyed, not dulled them. "

- Edgar Allan Poe
"The Tell-Tale Heart"




"Alex Garrison" <agarrison@logtech.com> on 03/07/2000 06:47:41 AM

Please respond to JAVA400-L@midrange.com

To:   JAVA400-L@midrange.com
cc:
Subject:  Re: Problem with jdbc based application




I dont know if this is related to your problem or not, but we have are
still
having problems with sql handle leaks.  We have verified that we always
close our statements, but the sql cli traces still show handle leaks when
we
have sql statements with inner joins.  I see up to 3 handles opened on some
of our complicated sql statements.  The close then only frees up 1 of the
handles.  The other 2 remain open and never get reused.  Right now our only
workaround is to restart all web instances nightly.

Shahar, you could see for yourself if your problem is a handle leak by
turning on the cli traces.  You turn on the traces by:
1. ADDENVVAR QIBM_USRTRC_LEVEL 'INFO'
2. CHGUSRTRC JOB(XXXXXX/QTMHHTTP/YYYYYY) MAXSTG(16382) CLEAR(*YES)
    where xxxxxx = jobnumber of the web instance bch job
                yyyyyy = jobname of the web instance bch job

Let the traces run for a while.  Do some sql.  Then create a dump of the
traces with the DMPUSRTRC command (specify the same job info from step 2
above).  The dump will create a file called QTEMP/QAP0ZDMP.  If you look at
that file you can see if the file handle numbers are growing without being
reused.  IBM manual SC41-5806-01 has the info on the cli commands you will
see in the dump and will help you interpret the info.

Alex Garrison

----- Original Message -----
From: "Luther Ananda Miller" <luther.miller@HYPERE.COM>
To: <JAVA400-L@midrange.com>
Sent: Tuesday, March 07, 2000 2:30 AM
Subject: Re: Problem with jdbc based application


> Sounds like the statements are not always being closed (even know you
said
> they are!).
> To be 100% sure, make sure you close them in a finally clause:
> stmt = connection.xxx();
> try {
>  ...
> } finally {stmt.close(); }
>
> ----- Original Message -----
> From: shahar mor <shahar_mor@yahoo.com>
> To: <JAVA400-L@midrange.com>
> Sent: Monday, 6 March 2000 22:21
> Subject: Problem with jdbc based application
>
>
> > Hi all,
> >
> > We developed client/server application using java on
> > the client connecting with the java toolbox jdbc
> > driver to data base on the as400.
> >
> > It work fine using stored procedures when needed and
> > prepared statements for the sql request.
> >
> > BUT, sometimes upon heavy use of the system the
> > QZDASOINIT joblog contains some 2000 odp in option 14,
> > The joblog tells me that odp's are not deleted in
> > order to "reuse" it later and in the end, on rare
> > occasions i get 'reached maximum number of
> > statements'.
> >
> > We are quite sure that statements are being closed by
> > the application however, we cant get rid of this
> > situation.
> >
> > Any suggestions/comments are most welcomed.
> >
> > TIA
> >
> > Shahar
> >
> > =====
> > Shahar mor
> > consultant
> > __________________________________________________
> > Do You Yahoo!?
> > Talk to your friends online with Yahoo! Messenger.
> > http://im.yahoo.com
> > +---
> > | This is the JAVA/400 Mailing List!
> > | To submit a new message, send your mail to JAVA400-L@midrange.com.
> > | To subscribe to this list send email to JAVA400-L-SUB@midrange.com.
> > | To unsubscribe from this list send email to
> JAVA400-L-UNSUB@midrange.com.
> > | Questions should be directed to the list owner: joe@zappie.net
> > +---
>
> +---
> | This is the JAVA/400 Mailing List!
> | To submit a new message, send your mail to JAVA400-L@midrange.com.
> | To subscribe to this list send email to JAVA400-L-SUB@midrange.com.
> | To unsubscribe from this list send email to
JAVA400-L-UNSUB@midrange.com.
> | Questions should be directed to the list owner: joe@zappie.net
> +---
>

+---
| This is the JAVA/400 Mailing List!
| To submit a new message, send your mail to JAVA400-L@midrange.com.
| To subscribe to this list send email to JAVA400-L-SUB@midrange.com.
| To unsubscribe from this list send email to JAVA400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner: joe@zappie.net
+---



+---
| This is the JAVA/400 Mailing List!
| To submit a new message, send your mail to JAVA400-L@midrange.com.
| To subscribe to this list send email to JAVA400-L-SUB@midrange.com.
| To unsubscribe from this list send email to JAVA400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner: joe@zappie.net
+---