Re: ODPs and locks

Hi Dieter, 
  Thanx for the suggestion. May be the record lock is the one which was
contributing to slow response. However this was my observation:

1. When I do WRKOBJLCK on a file I see that it is held by many QZDASOINIT
jobs in SHRRD mode. Nowhere I see that it is exclusively help with a lock.
Is there a way of finding out which record it is locking?? 

2. Now sometimes when I do even interactive SQL, I get the message that
that file is locked by some other job. I  also know for a fact that there
is no web request running at that time. Again when I do WRKOBJLCK I see
only QZDASOINIT jobs using that file. 

3. In fact, I got the lock out situation which happened on a file which we
use inquiry only through out the app. It does not get updated by anybodt
else either (no other AS400 app). When I bring down the QSERVER subsystem
everything is cool; no locks on that file.

So, where could be the problem??
Suresh

----- Original Message -----
From:Dieter.Bender@xxxxxxxxxxx (Dieter Bender)
To:Java Programming on and around the iSeries / AS400
<java400-l@xxxxxxxxxxxx>
Subject:Re: ODPs and locks

> Hi Suresh,
> 
> On Thursday 24 April 2003 00:54, you wrote:
> > Hi All,
> >   We are developing a webproduct with the servlets running on PC
> server
> > under a web app server accessing all the data from AS400 DB2 (V5R1).
We
> use
> > JT400 as the AS400 driver. Also note that all our applications are
> > developed in Java with heavy usage of SQL and prepared statements.
> >
> >   We see that QZDASOINT jobs running in QSERVER basically serves the
> web
> > requests. We also noted that several instances of this job runs based
> on
> > threshhold values etc.. 
> 
> That's one of the points to use Connection Pooling. In this case, you'll
> have 
> as many Jobs (= connections) as you've configured for the pool.
> 
> > Now, when we look at open ODPs of some of these
> > jobs, there are several (40-50) ODPs open and ODPs of the same file
> (PF/LF)
> > also exist several times within the same job. 
> 
> Thats an issue of caching. There are diffrent caching mechanism. The
> driver 
> caches, extended dynamic (you should use this driver propertie) caches,
> the 
> pool caches preparedStatements (if it could) and the SQL engine cachs.
> 
> > This probably is contributing
> > to the slowness 
> 
> making some load tests, 1000 connections with tenthousends of ODPs,
> didn't 
> cause any problem. Normally caching majkes it faster.
> 
> > and also when several users use this app it might crash
> > because of opening of too many ODPs.
> 
> Thats one of the tasks of a good connection pool implementation. The
pool
> 
> closes the Connection (= frees all ressources) and reopens another.
> 
> >
> >   Another observation is that files are getting locked out and this
> makes
> > other servlets hang trying to access that file. 
> 
> If you don't use isolation level serializable, a file lock, caused by a
> SQL 
> ODP can't bring another SQL access to a wait. 
> What do you mean exactly with makes other jobs hanging???  this seems to
> be 
> the problem, you are searching since some weeks!!!
> I would recommend to have a look, if there are any record locks to this 
> tables and I would have a look to the JVM of the java Client job, if
> there 
> might be a loop condition.
> Further on I would have a look to the QZDASOINIT Jobs, what thea are 
> currently doing, just wait? using CPU?, sitting in an error Condition?
> 
> > When we looked at WRKOBJLCK
> > on one of the file we saw several QZDASOINIT jobs holding it (locked
> status
> > is SHRRD and status is HELD). Question is if it is shared read, why
> the
> > file is locked out? Also, how do we reduce this ODP overhead?
Currently
> we
> > are testing with just 3-4 users using the system. Also, the tables we
> are
> > accesing are not journalled and hence comit control is OFF.
> >
> > Any suggestion is greatly appreciated,
> > Suresh
> > Antares Systems Ltd.
> > # 24, Sudha Complex, 3rd Stage, 4th Block,
> > Basaveshwaranagar,
> > Bangalore - 560 079
> > Telefax : 91-80-3284311, 3284313
> 
> -- 
> mfG
> 
> Dieter Bender
> 
> 
> DV-Beratung Dieter Bender
> Wetzlarerstr. 25
> 35435 Wettenberg
> Tel. +49 641 9805855
> Fax +49 641 9805856
> www.bender-dv.de
> _______________________________________________
> 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.cgi/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.
> 

Antares Systems Ltd.
# 24, Sudha Complex, 3rd Stage, 4th Block,
Basaveshwaranagar,
Bangalore - 560 079
Telefax : 91-80-3284311, 3284313