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

Dave Nance

[DB2-L] [DB2 v8 ] Dynamic SQL locks with UR
Bala,
   What issue are the S locks causing to your batch process? What are these batch processes doing that they do not allow an S lock on the table?
 
David Nance
 



----- Forwarded Message ----
From: DB2DBAzOS <[login to unmask email]>
To: [login to unmask email]
Sent: Tue, February 2, 2010 10:56:48 PM
Subject: 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.
>
>
>
________________________________

>The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.
>
>
>________________________________

>The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.

________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.




_____________________________________________________________________

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

http://www.idug.org/rug/index.html - with almost 150 IDUG Regional User Groups,
there is probably one near you!
Regional User Groups are your local connection to the Worldwide DB2 User Community
_____________________________________________________________________

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

Michael Turner

Re: [DB2 v8 ] Dynamic SQL locks with UR
(in response to Dave Nance)
Just to be clear - the Mass Delete S-lock should not block normal update activity by other users. It only blocks a Mass Delete operation (SQL DELETE with no WHERE clause).

Regards
Mike Turner
Email: [login to unmask email]
Tel: +44 (0)1565-873702
Web: www.michael-turner.ltd.uk
Michael Turner Ltd. Registered in England & Wales No. 1433722. Registered Office: 131 King Street, Knutsford, Cheshire, WA16 6EJ. VAT No. GB 338 4159 44.
----- Original Message -----
From: DB2DBAzOS
Newsgroups: bit.listserv.db2-l
To: [login to unmask email]
Sent: Wednesday, February 03, 2010 3:45 AM
Subject: Re: [DB2-L] [DB2 v8 ] Dynamic SQL locks with UR


Hi Mike,

Yes, they are Mass Delete locks. DB2 use them for WITH UR queries. And, the lock type is "S". I'm going to look at SG24-4725 today.

Thanks for your response.


On Tue, Feb 2, 2010 at 8:38 PM, Mike Turner <[login to unmask email]> wrote:

The S mode locks you are seeing may be Mass Delete locks. Isolation UR always takes a Mass Delete S-lock on the tablespace to prevent another task doing a mass delete. I used to think this was only for Segmented tablespaces, but the recent locking Redbook (SG24-4725) seems to imply that it happens for all tablespace types. I can see that might be true for the new UTS in V9, but I am not yet convinced it is true for the old Simple and Partitioned tablespace types.

Regards
Mike Turner
Email: [login to unmask email]
Tel: +44 (0)1565-873702
Web: www.michael-turner.ltd.uk
Michael Turner Ltd. Registered in England & Wales No. 1433722. Registered Office: 131 King Street, Knutsford, Cheshire, WA16 6EJ. VAT No. GB 338 4159 44.
----- Original Message -----
From: DB2DBAzOS
Newsgroups: bit.listserv.db2-l
To: [login to unmask email]
Sent: Tuesday, February 02, 2010 2:35 PM
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.



--------------------------------------------------------------------------



The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.



----------------------------------------------------------------------------



The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.




------------------------------------------------------------------------------



The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.

_____________________________________________________________________

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

http://www.idug.org/rug/index.html - with almost 150 IDUG Regional User Groups,
there is probably one near you!
Regional User Groups are your local connection to the Worldwide DB2 User Community
_____________________________________________________________________

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

Walter Jani&#223;en

AW: [DB2-L] [DB2 v8 ] Dynamic SQL locks with UR
(in response to Michael Turner)
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] 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]<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 >

_____________________________________________________________________

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

http://www.idug.org/rug/index.html - with almost 150 IDUG Regional User Groups,
there is probably one near you!
Regional User Groups are your local connection to the Worldwide DB2 User Community
_____________________________________________________________________

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] [DB2 v8 ] Dynamic SQL locks with UR
(in response to Walter Janißen)
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 >
>

_____________________________________________________________________

* 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: [DB2 v8 ] Dynamic SQL locks with UR
(in response to Bala)
Hi Mike,

The victim here is trying to make an (qualified, just one row) UPDATE. Both
the holder and the victim access the table via index.

thanks, Bala.

On Wed, Feb 3, 2010 at 7:49 PM, Mike Turner <[login to unmask email]> wrote:

> Just to be clear - the Mass Delete S-lock should not block normal update
> activity by other users. It only blocks a Mass Delete operation (SQL DELETE
> with no WHERE clause).
>
> Regards
> Mike Turner
> Email: [login to unmask email]
> Tel: +44 (0)1565-873702
> Web: www.michael-turner.ltd.uk
> Michael Turner Ltd. Registered in England & Wales No. 1433722. Registered
> Office: 131 King Street, Knutsford, Cheshire, WA16 6EJ. VAT No. GB 338 4159
> 44.
>
> ----- Original Message -----
> *From:* DB2DBAzOS <[login to unmask email]>
> *Newsgroups:* bit.listserv.db2-l
> *To:* [login to unmask email]
> *Sent:* Wednesday, February 03, 2010 3:45 AM
> *Subject:* Re: [DB2-L] [DB2 v8 ] Dynamic SQL locks with UR
>
> Hi Mike,
>
> Yes, they are Mass Delete locks. DB2 use them for WITH UR queries. And, the
> lock type is "S". I'm going to look at SG24-4725 today.
>
> Thanks for your response.
>
> On Tue, Feb 2, 2010 at 8:38 PM, Mike Turner <[login to unmask email]> wrote:
>
>> The S mode locks you are seeing may be Mass Delete locks. Isolation UR
>> always takes a Mass Delete S-lock on the tablespace to prevent another task
>> doing a mass delete. I used to think this was only for Segmented
>> tablespaces, but the recent locking Redbook (SG24-4725) seems to imply that
>> it happens for all tablespace types. I can see that might be true for the
>> new UTS in V9, but I am not yet convinced it is true for the old Simple and
>> Partitioned tablespace types.
>>
>> Regards
>> Mike Turner
>> Email: [login to unmask email]
>> Tel: +44 (0)1565-873702
>> Web: www.michael-turner.ltd.uk
>> Michael Turner Ltd. Registered in England & Wales No. 1433722. Registered
>> Office: 131 King Street, Knutsford, Cheshire, WA16 6EJ. VAT No. GB 338 4159
>> 44.
>>
>> ----- Original Message -----
>> *From:* DB2DBAzOS <[login to unmask email]>
>> *Newsgroups:* bit.listserv.db2-l
>> *To:* [login to unmask email]
>> *Sent:* Tuesday, February 02, 2010 2:35 PM
>> *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 >
>

_____________________________________________________________________

* 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: Fw: [DB2-L] [DB2 v8 ] Dynamic SQL locks with UR
(in response to Bala)
Hi David,

batch program is trying to UPDATE while the distributed threads JUST read.
With "S" locks on object, the Update fails because, the program can't
acquire "U" or "X".

On Wed, Feb 3, 2010 at 7:30 PM, Dave Nance <[login to unmask email]> wrote:

> Bala,
> What issue are the S locks causing to your batch process? What are these
> batch processes doing that they do not allow an S lock on the table?
>
> David Nance
>
>
>
> ----- Forwarded Message ----
> *From:* DB2DBAzOS <[login to unmask email]>
> *To:* [login to unmask email]
> *Sent:* Tue, February 2, 2010 10:56:48 PM
> *Subject:* 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 >
>

_____________________________________________________________________

* 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