Re: Passing Java Object to iSeries

    Hi Kelly,

> Each method creates it's own callableStatement to use with
> the connection.

    Depending on what you're doing, there may not be a way around doing
this.  But, it's going to make things very slow when performing a large
number of transactions and calling the methods repeatedly.  Apparently
that's the case, since you get the max statements error.

    This may be a distraction, but on another list, a fellow saw lock
escalation when doing many transactions.  That blocked out other users even
though there were no specific row conflicts.  As I responded there, it's
another reason to use optimistic locking.  I don't know what the DB was or
if DB2 exhibits the same behavior.  But your transaction isolation in the
stored procedures is worth checking.  Keep in mind there's a difference
between performance ( raw speed ) and scalability.

    I suspect your previously slow results had to do with not running
CRTJVAPGM.  That should not be an impact since V5R2+ VMs ( and there's a
patch for V5R1 ) use HotSpot ( or HotSpot-like  ) technology.

> Anybody have a suggestion as to why the close() doesn't appear to
> cleaning up the connection?

    Can't tell without seeing the code.  You could be getting and swallowing
Exception/Error/Warnings that cause the closes not to happen.  As with Ron's
issue, TFCTC somewhere is necessary.  Also, drivers can have bugs ( gasp! )
Make sure you're using the latest.  Again, I think there are enough problems
exhibited that you need a code review.


                                                         Joe Sam

Joe Sam Shirah -        http://www.conceptgo.com
conceptGO       -        Consulting/Development/Outsourcing
Java Filter Forum:       http://www.ibm.com/developerworks/java/
Just the JDBC FAQs: http://www.jguru.com/faq/JDBC
Going International?    http://www.jguru.com/faq/I18N
Que Java400?            http://www.jguru.com/faq/Java400


----- Original Message ----- 
From: "Kelly Jones" <kjones@xxxxxxxxxxxxxxxx>
To: "Java Programming on and around the iSeries / AS400"
<java400-l@xxxxxxxxxxxx>
Sent: Friday, April 28, 2006 11:24 AM
Subject: RE: Passing Java Object to iSeries


> Thanks for everyone's input.  The original "can this be done" question
> may not be possible.  I was just posing the question.
>
> As for whether or not the app can be run on the iSeries, yes - it
> certainly could.  That was the original reason for writing it in Java.
> However, the first iteration which was done over a year ago was
> painfully, and I mean painfully, slow when run on the iSeries.  That was
> the reason for moving it to a WinTel box.  The WinTel box easily saw a
> 10,000% (not exaggeration) improvement in speed.  Because of the time
> crunch to get this into production at the time, we didn't investigate
> why it was so slow in the iSeries and so much quicker on a WinTel box,
> it just was.  So for the past year it has been running this way.
>
> I have now had an opportunity to revisit this application and do some
> major overhauling.  I have not tried running the new version on the
> iSeries, although this is something I suspect I may do soon.  It would
> be interesting to see if we still have the slowness over there.
>
> I have also revisited how I am opening/closing the JDBC connections.  I
> have gone from over 4000 down to 8 (big improvement).  However, in doing
> so, I have bumped into the "java.sql.SQLException: Limit on number of
> statements exceeded. at
> com.ibm.as400.access.JDError.throwSQLException(JDError.java:406)" error
> as mentioned in a previous post.  For the life of me I can't figure out
> why I keep getting this.  I open the connection the first time the
> "PersistData" class gets called and the connection gets stored as a
> property of the class.  Each method in the class has access to the
> connection. Each method creates it's own callableStatement to use with
> the connection.  After each cs.execute() I call cs.close() so this
> *should* clear out the statement, but it seems that's not the case.
> According to the docs there can be up to 9999 open statements before she
> blows, but it appears I have executed many more statements than that,
> however, I don't know exactly how many statements have been called
> before it barfs.  As a side note, when I created a new connection each
> time any of the methods in PersistData needed one, I never ran into the
> "max statement" error (just 4000+ open/closes).  So it seems it has to
> do with leaving the connection open and reusing it vs. opening a new
> connection each time.
>
> Anybody have a suggestion as to why the close() doesn't appear to
> cleaning up the connection?
>
> Kelly
>