[DB2 v8 ] Dynamic SQL locks with UR

Bala

[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.

_____________________________________________________________________

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

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

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 Bala)
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.

_____________________________________________________________________

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

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

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

Dave Nance

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




_____________________________________________________________________

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

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

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 Dave Nance)
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 >
>

_____________________________________________________________________

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

_____________________________________________________________________

* 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