[DB2-L] AW: [DB2-L] [DB2 v8 ] Dynamic SQL locks with UR

Walter Janißen

[DB2-L] AW: [DB2-L] [DB2 v8 ] Dynamic SQL locks with UR
Hi Bala

I still try to understand, why you conclude, that the S-lock is a mass-delete lock. My understanding is, and as Mike also mentioned, that a mass delete lock is a special lock, which does not compete with other intent locks on that table(space).

But if the S-lock is a gross lock on that tablespace, the only way I know how achieve that, is issuing the LOCK TABLE IN SHARE MODE statement. Have you checked that?

Mit freundlichen Grüßen
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Laufzeitarchitektur
Victoriaplatz 2
40477 Düsseldorf
mailto:[login to unmask email]

Vorsitzender des Aufsichtsrates: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön.
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996


________________________________
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von DB2DBAzOS
Gesendet: Donnerstag, 4. Februar 2010 08:15
An: [login to unmask email]
Betreff: Re: [DB2-L] AW: [DB2-L] [DB2 v8 ] Dynamic SQL locks with UR

Hi Walter, That is what I looked at immediately. They are all defined as LOCKRULE ANY and LOCKMAX SYSTEM. I even tried changing LOCKRULE to PAGE and ROW to see if the "S" locks (MASS DELETE) go away. The SQL runs against an join of 4 or 5 tables, one or two tables have "S" locks while the others just have "IS" locks. The "S" lock table is accessed via index and accessed as 3rd table in the join. It is partitioned.

The batch job that times out is an qualified UPDATE. The UPDATE affects only one row (no mass updates ! ; runs against primary key). The batch program makes little over 4K rows and runs pretty swiftly (6 mins !). But, distributed threads run longer and does make commit when the job is done.

thanks for your time.



2010/2/3 Walter Janißen <[login to unmask email]<mailto:[login to unmask email]>>
Hi Bala

The one tablespace, where you have special problems with, do it differ from others in LOCKRULE and LOCKMAX?

regards
Walter

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Laufzeitarchitektur
Victoriaplatz 2
40477 Düsseldorf
mailto:[login to unmask email]

Vorsitzender des Aufsichtsrates: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön.
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996



________________________________
Von: IDUG DB2-L [mailto:[login to unmask email]<mailto:[login to unmask email]>] Im Auftrag von DB2DBAzOS
Gesendet: Mittwoch, 3. Februar 2010 04:57
An: [login to unmask email]<mailto:[login to unmask email]>
Betreff: Re: [DB2-L] [DB2 v8 ] Dynamic SQL locks with UR

Hi David,

Yes, the batch programs make several hundred updates/deletes. The batch job fails due to active threads that read with ISO(UR). MQTs are our permanent way out to solve this problem. But, the distributed SQLs hit the views (joins on tables) which I thought would work on files and not against base tables. Batch programs take top priority so, there is no way I can tell legacy programs to change the way they were written to allow some distributed queries. (In our shop, there was lot of hassle in allowing distributed SQLs ! But, I told them we are living in 2010 !).

However what I noticed when I changed the SQLs to have FOR FETCH ONLY instead of WITH Ur, the locks were "IS" instead of "S". IS must let concurrent users to make updates. Still there is ONE tablespace that has "S" when all others have "IS". Looked at the access paths etc.. still unable to determine why just one TS has "S" locks while others are doing "IS".

Thanks, Bala.

On Wed, Feb 3, 2010 at 4:45 AM, Dave Nance <[login to unmask email]<mailto:[login to unmask email]>> wrote:
There is no way to run an SQL statement with "no locks". ISO 'WITH UR' allows you to take minimal locks of the data you are accessing. You do not actually state what kind of problem you are having though. Are the locks being taken keeping your batch cycle from completing properly? If that is the case, what are you attempting to do with your batch cycle? As long as you are not trying to take an exclusive lock on an object and you are committing frequently enough, you can 'normally' run batch, online and dynamic reporting all at the same time.

David Nance



________________________________
From: DB2DBAzOS <[login to unmask email]<mailto:[login to unmask email]>>

To: [login to unmask email]<mailto:[login to unmask email]>
Sent: Tue, February 2, 2010 9:35:31 AM

Subject: [DB2-L] [DB2 v8 ] Dynamic SQL locks with UR

Dear list,

We are having few dynamic SQLs accessing a view that is a join of 3 to 5 tables. These SQLs are coded "WITH UR" to avoid locks and let batch updates run w/o contention. But, what I noticed today was locks were still taken by these dynamic SQLs (we have caching ON). When I displayed tablespaces for locks, it shows NO locks however.

From the SMF records, I could see that "S" locks were taken by these dynamic SQLs ISO(UR). Then, I read that the cursors having the dynamic SQLs are always of ambiguous type irrespective of the SQL contained in it.

When I changed the WITH UR to "FOR FETCH ONLY", the "IS" locks were taken (from SMF records again) however, there are few application tablespaces where "S" locks are taken.

Could someone explain me the theory behind locks in the case of dynamic SQLs and possibly give me suggestions how I could let dynamic SQLs run w/o locks ?

Thanks in advance.

________________________________

[ http://www.idug.org/images/M_images/idug%20na3.jpg ] < http://www.idug.org/db2-north-america-conference/index.html >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >


________________________________

[ http://www.idug.org/images/M_images/idug%20na3.jpg ] < http://www.idug.org/db2-north-america-conference/index.html >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >


________________________________

[ http://www.idug.org/images/M_images/idug%20org.jpg ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/M_images/idug%20org.jpg ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >


________________________________

[ http://www.idug.org/images/M_images/idug%20na3.jpg ] < http://www.idug.org/db2-north-america-conference/index.html >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.IDUG.org membership is now free.
Do you have people in your office who are not an IDUG member?
Show them how to access the information and help train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Bala

Re: AW: [DB2-L] AW: [DB2-L] [DB2 v8 ] Dynamic SQL locks with UR
(in response to Walter Janißen)
Hi Walter,

The lock type was "Mass delete" and the lock state is "S" as I see on online
monitoring.

Object Locked Type Duration St P/L Count
--------------------------- -------- -------- --- --- ------
mmmmmm .xxxxxxxx MASS DEL COMMIT S L 1
nnnnnn .yyyyyyyy MASS DEL COMMIT S L 1

I'm unable to reproduce the contention successfully yet. May be, as you say,
does "Mass Del" type of locks are special and they don't really lock
resources for concurrent Updates ?

thanks..

2010/2/4 Walter Janißen <[login to unmask email]>

> Hi Bala
>
> I still try to understand, why you conclude, that the S-lock is a
> mass-delete lock. My understanding is, and as Mike also mentioned, that a
> mass delete lock is a special lock, which does not compete with other intent
> locks on that table(space).
>
> But if the S-lock is a gross lock on that tablespace, the only way I know
> how achieve that, is issuing the LOCK TABLE IN SHARE MODE statement. Have
> you checked that?
>
> Mit freundlichen Grüßen
> Walter Janißen
>
> ITERGO Informationstechnologie GmbH
> Anwendungsentwicklung
> Laufzeitarchitektur
> Victoriaplatz 2
> 40477 Düsseldorf
> mailto:[login to unmask email] <[login to unmask email]>
>
> Vorsitzender des Aufsichtsrates: Jürgen Vetter
> Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
> Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön.
> Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996
>
>
> ------------------------------
> *Von:* IDUG DB2-L [mailto:[login to unmask email] *Im Auftrag von *DB2DBAzOS
> *Gesendet:* Donnerstag, 4. Februar 2010 08:15
>
> *An:* [login to unmask email]
> *Betreff:* Re: [DB2-L] AW: [DB2-L] [DB2 v8 ] Dynamic SQL locks with UR
>
> Hi Walter, That is what I looked at immediately. They are all defined as
> LOCKRULE ANY and LOCKMAX SYSTEM. I even tried changing LOCKRULE to PAGE and
> ROW to see if the "S" locks (MASS DELETE) go away. The SQL runs against an
> join of 4 or 5 tables, one or two tables have "S" locks while the others
> just have "IS" locks. The "S" lock table is accessed via index and accessed
> as 3rd table in the join. It is partitioned.
>
> The batch job that times out is an qualified UPDATE. The UPDATE affects
> only one row (no mass updates ! ; runs against primary key). The batch
> program makes little over 4K rows and runs pretty swiftly (6 mins !). But,
> distributed threads run longer and does make commit when the job is done.
>
> thanks for your time.
>
>
>
> 2010/2/3 Walter Janißen <[login to unmask email]>
>
>> Hi Bala
>>
>> The one tablespace, where you have special problems with, do it differ
>> from others in LOCKRULE and LOCKMAX?
>>
>> regards
>> Walter
>>
>> ITERGO Informationstechnologie GmbH
>> Anwendungsentwicklung
>> Laufzeitarchitektur
>> Victoriaplatz 2
>> 40477 Düsseldorf
>> mailto:[login to unmask email] <[login to unmask email]>
>>
>> Vorsitzender des Aufsichtsrates: Jürgen Vetter
>> Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
>> Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön.
>> Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996
>>
>>
>> ------------------------------
>> *Von:* IDUG DB2-L [mailto:[login to unmask email] *Im Auftrag von *DB2DBAzOS
>> *Gesendet:* Mittwoch, 3. Februar 2010 04:57
>> *An:* [login to unmask email]
>> *Betreff:* Re: [DB2-L] [DB2 v8 ] Dynamic SQL locks with UR
>>
>> Hi David,
>>
>> Yes, the batch programs make several hundred updates/deletes. The batch
>> job fails due to active threads that read with ISO(UR). MQTs are our
>> permanent way out to solve this problem. But, the distributed SQLs hit the
>> views (joins on tables) which I thought would work on files and not against
>> base tables. Batch programs take top priority so, there is no way I can
>> tell legacy programs to change the way they were written to allow some
>> distributed queries. (In our shop, there was lot of hassle in allowing
>> distributed SQLs ! But, I told them we are living in 2010 !).
>>
>> However what I noticed when I changed the SQLs to have FOR FETCH ONLY
>> instead of WITH Ur, the locks were "IS" instead of "S". IS must let
>> concurrent users to make updates. Still there is ONE tablespace that has "S"
>> when all others have "IS". Looked at the access paths etc.. still unable to
>> determine why just one TS has "S" locks while others are doing "IS".
>>
>> Thanks, Bala.
>>
>> On Wed, Feb 3, 2010 at 4:45 AM, Dave Nance <[login to unmask email]> wrote:
>>
>>> There is no way to run an SQL statement with "no locks". ISO 'WITH
>>> UR' allows you to take minimal locks of the data you are accessing. You do
>>> not actually state what kind of problem you are having though. Are the locks
>>> being taken keeping your batch cycle from completing properly? If that is
>>> the case, what are you attempting to do with your batch cycle? As long as
>>> you are not trying to take an exclusive lock on an object and you are
>>> committing frequently enough, you can 'normally' run batch, online and
>>> dynamic reporting all at the same time.
>>>
>>> David Nance
>>>
>>>
>>>
>>> ------------------------------
>>> *From:* DB2DBAzOS <[login to unmask email]>
>>>
>>> *To:* [login to unmask email]
>>> *Sent:* Tue, February 2, 2010 9:35:31 AM
>>>
>>> *Subject:* [DB2-L] [DB2 v8 ] Dynamic SQL locks with UR
>>>
>>> Dear list,
>>>
>>> We are having few dynamic SQLs accessing a view that is a join of 3 to 5
>>> tables. These SQLs are coded "WITH UR" to avoid locks and let batch updates
>>> run w/o contention. But, what I noticed today was locks were still taken by
>>> these dynamic SQLs (we have caching ON). When I displayed tablespaces for
>>> locks, it shows NO locks however.
>>>
>>> From the SMF records, I could see that "S" locks were taken by these
>>> dynamic SQLs ISO(UR). Then, I read that the cursors having the dynamic SQLs
>>> are always of ambiguous type irrespective of the SQL contained in it.
>>>
>>> When I changed the WITH UR to "FOR FETCH ONLY", the "IS" locks were taken
>>> (from SMF records again) however, there are few application tablespaces
>>> where "S" locks are taken.
>>>
>>> Could someone explain me the theory behind locks in the case of dynamic
>>> SQLs and possibly give me suggestions how I could let dynamic SQLs run w/o
>>> locks ?
>>>
>>> Thanks in advance.
>>>
>>> ------------------------------
>>>
>>> [image: IDUG - The Worldwide DB2 User Community! ] < http://www.idug.org/db2-north-america-conference/index.html >
>>>
>>> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
>>> are not already an IDUG member, please register here. < http://www.idug.org/register >
>>>
>>>
>>> ------------------------------
>>>
>>> [image: IDUG - The Worldwide DB2 User Community! ] < http://www.idug.org/db2-north-america-conference/index.html >
>>>
>>> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
>>> are not already an IDUG member, please register here. < http://www.idug.org/register >
>>>
>>
>>
>> ------------------------------
>>
>> [image: IDUG - The Worldwide DB2 User Community!] < http://www.idug.org >
>>
>> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
>> are not already an IDUG member, please register here. < http://www.idug.org/register >
>>
>>
>> ------------------------------
>>
>> [image: IDUG - The Worldwide DB2 User Community!] < http://www.idug.org >
>>
>> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
>> are not already an IDUG member, please register here. < http://www.idug.org/register >
>>
>
>
> ------------------------------
>
> [image: IDUG - The Worldwide DB2 User Community! ] < http://www.idug.org/db2-north-america-conference/index.html >
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
> not already an IDUG member, please register here. < http://www.idug.org/register >
>
>
> ------------------------------
>
> [image: IDUG - The Worldwide DB2 User Community! ] < http://www.idug.org/db2-north-america-conference/index.html >
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
> not already an IDUG member, please register here. < http://www.idug.org/register >
>

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.IDUG.org membership is now free.
Do you have people in your office who are not an IDUG member?
Show them how to access the information and help train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L