I have db2 9 installation and I think our application isn't handling
locking optimally.

I have Maxlocks(96) and locklist (104832) setup to be auto tuned. The
value of these parameters in the parenthesis are the auto tuned values
after running my application for a while.
I notice values in db2diag.log that these values are being increased
to prevent lock escalation.

How do I determine the sql that is causing these high escalations?
Would it be better from a performance standpoint to specifically lock
the table in question instead?
On a machine with 4G of memory - is there a rule of thumb of about how
much memory should be allocated for locking? - locklist and maxlocks?
If I set locklist and maxlocks to a lower value and monitor
db2diag.log will I get the offending sql that is causing the lock
escalation?

Thanks.

"dunleav1" <jmdunleavy@comcast.net> wrote in message
news:a0ef62fb-530d-411a-b92f-f67be383a5dd@e25g2000prg.googlegroups.com...
>I have db2 9 installation and I think our application isn't handling
> locking optimally.
>
> I have Maxlocks(96) and locklist (104832) setup to be auto tuned. The
> value of these parameters in the parenthesis are the auto tuned values
> after running my application for a while.
> I notice values in db2diag.log that these values are being increased
> to prevent lock escalation.
>
> How do I determine the sql that is causing these high escalations?
> Would it be better from a performance standpoint to specifically lock
> the table in question instead?
> On a machine with 4G of memory - is there a rule of thumb of about how
> much memory should be allocated for locking? - locklist and maxlocks?
> If I set locklist and maxlocks to a lower value and monitor
> db2diag.log will I get the offending sql that is causing the lock
> escalation?
>
> Thanks.

That is a huge amount of locklist. Do you have a data warehouse application,
or an OLTP application (with lots of updates, inserts, and deletes)? If you
have almost all selects, you may NOT want to avoid lock escalation since
multiple share locks on table coexist fine, or maybe even use UR isolation
level.

On Dec 10, 8:27 pm, dunleav1 <jmdunle...@comcast.net> wrote:
> I have db2 9 installation and I think our application isn't handling
> locking optimally.
>

What isolation level is your application using?

> I have Maxlocks(96) and locklist (104832) setup to be auto tuned. The
> value of these parameters in the parenthesis are the auto tuned values
> after running my application for a while.
> I notice values in db2diag.log that these values are being increased
> to prevent lock escalation.
>
> How do I determine the sql that is causing these high escalations?

get snapshot for applications on <db>

and

get snapshot for locks on <db>

will give you a start

/Lennart

On Dec 10, 5:48 pm, "Mark A" <nob...@nowhere.com> wrote:
> "dunleav1" <jmdunle...@comcast.net> wrote in message
>
> news:a0ef62fb-530d-411a-b92f-f67be383a5dd@e25g2000prg.googlegroups.com...
>
>
>
> >I have db2 9 installation and I think our application isn't handling
> > locking optimally.
>
> > I have Maxlocks(96) and locklist (104832) setup to be auto tuned. The
> > value of these parameters in the parenthesis are the auto tuned values
> > after running my application for a while.
> > I notice values in db2diag.log that these values are being increased
> > to prevent lock escalation.
>
> > How do I determine the sql that is causing these high escalations?
> > Would it be better from a performance standpoint to specifically lock
> > the table in question instead?
> > On a machine with 4G of memory - is there a rule of thumb of about how
> > much memory should be allocated for locking? - locklist and maxlocks?
> > If I set locklist and maxlocks to a lower value and monitor
> > db2diag.log will I get the offending sql that is causing the lock
> > escalation?
>
> > Thanks.
>
> That is a huge amount of locklist. Do you have a data warehouse application,
> or an OLTP application (with lots of updates, inserts, and deletes)? If you
> have almost all selects, you may NOT want to avoid lock escalation since
> multiple share locks on table coexist fine, or maybe even use UR isolation
> level.

It's both. This application is a oltp application but it moves data
into a reporting schema.
Basically, I move transactions into a star schema based reporting
schema.

The isolation level would be whatever DB2 chooses when using the DB2
jdbc driver.

On Dec 11, 12:30 am, Lennart <Erik.Lennart.Jons...@gmail.com> wrote:
> On Dec 10, 8:27 pm, dunleav1 <jmdunle...@comcast.net> wrote:
>
> > I have db2 9 installation and I think our application isn't handling
> > locking optimally.
>
> What isolation level is your application using?

The isolation level would be whatever DB2 chooses when using the DB2
jdbc driver.

>
> > I have Maxlocks(96) and locklist (104832) setup to be auto tuned. The
> > value of these parameters in the parenthesis are the auto tuned values
> > after running my application for a while.
> > I notice values in db2diag.log that these values are being increased
> > to prevent lock escalation.
>
> > How do I determine the sql that is causing these high escalations?
>
> get snapshot for applications on <db>
>
> and
>
> get snapshot for locks on <db>
>
> will give you a start
>
> /Lennart

I have a process that runs approximately 500 transactions for my test.
I'll have to watch the db2diag.log file for an alert than map it back
to db2diag.log file. That's kind of a bother. db2diag already knows
the table that it is escalating on, I should be able to log the
offending statement shouldn't I?

I'm pretty sure it's REPEATABLE_READ by default.

"dunleav1" <jmdunleavy@comcast.net> wrote in message
news:853ff41e-5d23-4030-8d3e-
> It's both. This application is a oltp application but it moves data
> into a reporting schema.
> Basically, I move transactions into a star schema based reporting
> schema.
>
> The isolation level would be whatever DB2 chooses when using the DB2
> jdbc driver.

On any select statement, you can alter the default isolation level using the
WITH UR (or CS, RR, RS) clause. Using with UR "might" be a good idea when
you are pulling data from the tables to load the datawarehouse, especially
if it is historical data and you know it is not being changed at the exact
moment you pull it..

"dunleav1" <jmdunleavy@comcast.net> wrote in message
news:f5345f2c-df49-477f-bc33-aa5c5733a187@i29g2000prf.googlegroups.com...
> I'm pretty sure it's REPEATABLE_READ by default.

The DB2 default is CS by default. If you are using RR, try changing it to CS
or UR using the WITH XX clause.

On Dec 11, 7:53 pm, "Mark A" <nob...@nowhere.com> wrote:
> "dunleav1" <jmdunle...@comcast.net> wrote in message
>
> news:f5345f2c-df49-477f-bc33-aa5c5733a187@i29g2000prf.googlegroups.com...
>
> > I'm pretty sure it's REPEATABLE_READ by default.
>
> The DB2 default is CS by default. If you are using RR, try changing it to CS
> or UR using the WITH XX clause.

CS is not one of the isolation levels listed in the JDBC standard?

On Dec 12, 12:00 pm, dunleav1 <jmdunle...@comcast.net> wrote:
> On Dec 11, 12:57 pm, dunleav1 <jmdunle...@comcast.net> wrote:
>
>
>
> > On Dec 11, 12:30 am, Lennart <Erik.Lennart.Jons...@gmail.com> wrote:
>
> > > On Dec 10, 8:27 pm, dunleav1 <jmdunle...@comcast.net> wrote:
>
> > > > I have db2 9 installation and I think our application isn't handling
> > > > locking optimally.
>
> > > What isolation level is your application using?
>
> > The isolation level would be whatever DB2 chooses when using the DB2
> > jdbc driver.
>
> > > > I have Maxlocks(96) and locklist (104832) setup to be auto tuned. The
> > > > value of these parameters in the parenthesis are the auto tuned values
> > > > after running my application for a while.
> > > > I notice values in db2diag.log that these values are being increased
> > > > to prevent lock escalation.
>
> > > > How do I determine the sql that is causing these high escalations?
>
> > > get snapshot for applications on <db>
>
> > > and
>
> > > get snapshot for locks on <db>
>
> > > will give you a start
>
> > > /Lennart
>
> > I have a process that runs approximately 500 transactions for my test.
> > I'll have to watch the db2diag.log file for an alert than map it back
> > to db2diag.log file. That's kind of a bother. db2diag already knows
> > the table that it is escalating on, I should be able to log the
> > offending statement shouldn't I?
>
> I figured out the offending statements by syncing a sql "trace" and
> the db2diag.log file.
> I have 7 insert statements that do a a join between the insert table
> (T1) and a second table (T2) that look like this:
> (ie) insert into T1 (val1,val2,val3) (select val1,val2,val3) from T2
> where not exists (select 1 from T2 where T2.val1 = T1.val1) and val2 =
> 1 and val3 >10000000)
>
> The lock escalation happens on the table being inserted into if I set
> maxlocks and locklist to a permanant lower value. (ie) 50, 20000 for
> example.
> Now T1 may be selected or inserted into from different processes so I
> want the default isolation level of RR from jdbc.
>
> Will I see any significant performance gain by issuing a specific lock
> table prior to the insert statement?
> Or is it better to just let DB2 automatically escalate the sql to a
> table lock?

The 7 inserts take - 43 seconds, 35 seconds, 369 seconds, 8 seconds,
29 seconds, 29 seconds, 29 seconds.
Based on these numbers I'm really wondering just how much of
performance hit a lock escalation really is. Because it doesn't look
like much to worry about.