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