Re: ODPs and locks

Hi Suresh,

On Friday 25 April 2003 03:36, you wrote:
> 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??
>

- pressing F6, you will see the member locks: please post a hardcopy of this
- DSPRCDLCK will show you the record locks 

> 2. Now sometimes when I do even interactive SQL, I get the message that
> that file is locked by some other job. 

what did you try in interactive SQL and wich messages did you get exactly, 
please give me just one example.

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??

just to be complete, are there any operations, wich need the file 
exclusive??? ALTER TABLE, DROP TABLE? SAVOBJ or SAVLIB? RGZPFM STRJRNPF or 
something like this?

> 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

-- 
mfG

Dieter Bender


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