Question from the latest Idug Solutions Journal issue

Hello

Question from the latest Idug Solutions Journal issue
Hi all,
Just finish to read the latest issue of Idug SJ , and have some
interrogations :
1- About the article : "The impact of UR on appl performance" : The CPU and
elapsed time of the test running with isolation CS is better than isolation
UR (40s CPU time vs 42 ) ??? only seen the difference in lock request
number. So you take less locks , you do the same SQL , the same number of
getpages an the CPU time is worse ?????

2-About the article : Know your isolation levels " : Craig Mullins
said : "An oder by clause does not garantee that rows will be returned in
order if UR isolation level is used" ??? How can you explain that ?

#############################################################################
Welcome to the IDUG DB2-L list. To change your subscription options or subscribe or to cancel your subscription, visit the IDUGDB2-L archives webpage at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG List Admins can be reached at
[login to unmask email]

Phil Grainger

Re: Question from the latest Idug Solutions Journal issue
(in response to Hello)
I can answer the second one

Let's say you are reading rows through an index. This ought to guarantee sequence right? But after you read an index page (or perhaps even whilst you are reading it) the page splits and a new page is created, further "down" the index with half of the keys in it.

This might even conceivably result in you reading the same row more than once!

Phil Grainger
Computer Associates
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Christophe Radier
Sent: Tuesday, November 25, 2003 3:00 PM
To: [login to unmask email]
Subject: Question from the latest Idug Solutions Journal issue


Hi all,
Just finish to read the latest issue of Idug SJ , and have some
interrogations :
1- About the article : "The impact of UR on appl performance" : The CPU and
elapsed time of the test running with isolation CS is better than isolation
UR (40s CPU time vs 42 ) ??? only seen the difference in lock request
number. So you take less locks , you do the same SQL , the same number of
getpages an the CPU time is worse ?????

2-About the article : Know your isolation levels " : Craig Mullins
said : "An oder by clause does not garantee that rows will be returned in
order if UR isolation level is used" ??? How can you explain that ?

#############################################################################
Welcome to the IDUG DB2-L list. To change your subscription options or subscribe or to cancel your subscription, visit the IDUGDB2-L archives webpage at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG List Admins can be reached at
[login to unmask email]

#############################################################################
Welcome to the IDUG DB2-L list. To change your subscription options or subscribe or to cancel your subscription, visit the IDUGDB2-L archives webpage at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG List Admins can be reached at
[login to unmask email]

Michael Ebert

Re: Question from the latest Idug Solutions Journal issue
(in response to Phil Grainger)
I don't think that even in this situation you could get rows in the wrong
order. It might just be conceivable that you get a duplicated row, but
even that seems incredible to me. UR just means you can see rows that are
uncommitted. If you get a result that doesn't follow the SQL semantics
(i.e. unordered rows when you specify ORDER BY), it is certainly a serious
bug no matter what the cause.

Dr. Michael Ebert
DB2 Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany



I can answer the second one

Let's say you are reading rows through an index. This ought to guarantee
sequence right? But after you read an index page (or perhaps even whilst
you are reading it) the page splits and a new page is created, further
"down" the index with half of the keys in it.

This might even conceivably result in you reading the same row more than
once!

Phil Grainger
Computer Associates
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]

-----Original Message-----
Subject: Question from the latest Idug Solutions Journal issue

Hi all,
Just finish to read the latest issue of Idug SJ , and have some
interrogations :
1- About the article : "The impact of UR on appl performance" : The CPU
and
elapsed time of the test running with isolation CS is better than
isolation
UR (40s CPU time vs 42 ) ??? only seen the difference in lock request
number. So you take less locks , you do the same SQL , the same number of
getpages an the CPU time is worse ?????

2-About the article : Know your isolation levels " : Craig Mullins
said : "An oder by clause does not garantee that rows will be returned in
order if UR isolation level is used" ??? How can you explain that ?


#############################################################################
Welcome to the IDUG DB2-L list. To change your subscription options or subscribe or to cancel your subscription, visit the IDUGDB2-L archives webpage at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG List Admins can be reached at
[login to unmask email]

Philip Nelson

FW: Question from the latest Idug Solutions Journal issue
(in response to Michael Ebert)
This E-Mail is sent in confidence for the addressee only. Unauthorised
recipients must preserve this confidentiality and should notify the
sender immediately by telephone on 0131-655-6789 and must delete the
original E-Mail without taking a copy. If you are not the addressee you
must not copy, distribute, disclose or use any of the information in any way.

This E-Mail is not intended nor should it be taken to create any legal
relations, contractual or otherwise.

We have taken all reasonable precautions to ensure that no viruses are transmitted
from the Scottish Widows Group of companies to any third party.

Scottish Widows accepts no responsibility for any loss or damage resulting directly
or indirectly from the use of this E-Mail or the contents.
*****************************************************************************

Folks,

Here is the reply from John Lynch, the author of the UR article, to the
question posed on the list a few days ago.

Phil
-----Original Message-----
From: Castiel, Brenda G [mailto:[login to unmask email]
Sent: 25 November 2003 19:22
To: Nelson, Philip
Cc: [login to unmask email]
Subject: FW: Question from the latest Idug Solutions Journal issue



Hi Phil,
Here is the response from John Lynch regarding use of UR.

Would you please post it on DB2-L for everyone's benefit ?


Thanks,
Brenda
(310) 403-9401

-----Original Message-----
From: Lynch, John A. [mailto:[login to unmask email]
Sent: Tuesday, November 25, 2003 10:34 AM
To: 'Castiel, Brenda G'
Subject: RE: Question from the latest Idug Solutions Journal issue



Brenda - Thanks for your note. To answer the reader's question - there was
no significant reduction of CPU or GETPAGES using an isolation level of (UR)
in the Erwin example using dynamic SQL. The 23% decrease in locking could
perhaps act to increase application concurrency and/or reduce lock overhead
but nowhere near the levels achieved in the second, DSNTIAUL example.
Dynamic SQL is catalog intensive and there are ways to mitigate that
overhead - such as dynamic SQL caching, parameter markers, etc. These are
not included in this discussion.

The second example illustrates that (UR) in a statically bound plan
(DSNTIAUL) can have a significant impact. 494,117 locks reduced to 183
locks. In this example the ability to unload 20G of data on a daily basis
with out impacting the on-line and scheduled utilities depended on using UR
and was of significant benefit to the organization.

Conclusions - dynamic SQL does have overhead that UR isolation may not
alleviate. On the other hand, the impact of UR on static SQL can be
impressive both in reducing lock overhead and by dramatically increasing
application concurrency.

THX JAL

-----Original Message-----
From: Castiel, Brenda G [ mailto:[login to unmask email]
<mailto:[login to unmask email]> ]
Sent: Tuesday, November 25, 2003 1:13 PM: Lynch, John A.
Subject: RE: Question from the latest Idug Solutions Journal issue

It is not posted online yet. Here is the last version of the article....




Thanks,
Brenda
(310) 403-9401
-----Original Message-----
From: Lynch, John A. [ mailto:[login to unmask email]
<mailto:[login to unmask email]> ]
Sent: Tuesday, November 25, 2003 9:58 AM
To: Castiel, Brenda G
Subject: RE: Question from the latest Idug Solutions Journal issue
Hi Brenda - Please send me the link to the article...THX JAL
-----Original Message-----
From: Castiel, Brenda G [ mailto:[login to unmask email]
<mailto:[login to unmask email]> ]
Sent: Tuesday, November 25, 2003 11:57 AM
To: Lynch, John A.
Subject: FW: Question from the latest Idug Solutions Journal issue
This question came to me... Could you pls reply ?

Thanks,
Brenda
(310) 403-9401

-----Original Message-----
From: Nelson, Philip [ mailto:[login to unmask email]
<mailto:[login to unmask email]> ]
Sent: Tuesday, November 25, 2003 7:15 AM
To: '[login to unmask email]'
Cc: '[login to unmask email]'
Subject: FW: Question from the latest Idug Solutions Journal issue

This was on the DB2-L today.

Phil
-----Original Message-----
From: Christophe Radier [ mailto:[login to unmask email]
<mailto:[login to unmask email]> ]
Sent: 25 November 2003 15:00
To: [login to unmask email]
Subject: Question from the latest Idug Solutions Journal issue

Hi all,
Just finish to read the latest issue of Idug SJ , and have some
interrogations :
1- About the article : "The impact of UR on appl performance" : The CPU and
elapsed time of the test running with isolation CS is better than isolation
UR (40s CPU time vs 42 ) ??? only seen the difference in lock request
number. So you take less locks , you do the same SQL , the same number of
getpages an the CPU time is worse ?????
2-About the article : Know your isolation levels " : Craig Mullins
said : "An oder by clause does not garantee that rows will be returned in
order if UR isolation level is used" ??? How can you explain that ?
############################################################################

#
Welcome to the IDUG DB2-L list. To change your subscription options or
subscribe or to cancel your subscription, visit the IDUGDB2-L archives
webpage at http://www.idugdb2-l.org/archives/db2-l.html
< http://www.idugdb2-l.org/archives/db2-l.html > . From that page
select "Join or Leave the list". The IDUG List Admins can be reached at
[login to unmask email]

****************************************************************************

*



*****************************************************************************
Visit the Scottish Widows website at www.scottishwidows.co.uk

Scottish Widows Group includes the following companies:-

Scottish Widows plc, Company No. 199549.
Authorised & Regulated by the Financial Services Authority.
Scottish Widows Annuities Limited, Company No. 199550.
Authorised & Regulated by the Financial Services Authority.
Scottish Widows Services Limited, Company No. 189975.

The Registered Office of all of the above companies is in the United Kingdom at 69 Morrison Street, Edinburgh, EH3 8YF.
Telephone: 0131 655 6000

Scottish Widows Unit Trust Managers Limited, Company No. 1629925
Registered Office: is in the United Kingdom at Charlton Place, Andover, Hampshire SP10 1RE.
Telephone: 0845 300 2244
Authorised & Regulated by the Financial Service Authority.

Scottish Widows Investment Partnership Limited, Company No. 794936
Registered Office : is in the United Kingdom at 10 Fleet Place, London EC4M 7RH.
Telephone: 0131 655 8500
Authorised & Regulated by the Financial Services Authority.

Scottish Widows Fund Management Limited, Company No. 74517.
Authorised & Regulated by the Financial Services Authority.
Pensions Management (SWF) Limited, Company No. 45361.
Authorised & Regulated by the Financial Services Authority.

The Registered Office of the above two companies is in the United Kingdom
at 15 Dalkeith Road, Edinburgh, EH16 5BU. Tel 0131 655 6000

The companies listed above are members of the Scottish Widows and Lloyds TSB Marketing Group-

Scottish Widows Bank plc, Company No. 154554
Registered Office: is in the United Kingdom at 67 Morrison Street, Edinburgh, EH3 8YJ.
Telephone: 0845 845 0829
Authorised & Regulated by the Financial Services Authority

Hill Samuel Unit Trust Managers Limited, Company No 406604
Registered Office: is in the United Kingdom at 10 Fleet Place,
London, EC4M 7RH
Telephone: 0800 336600
Authorised & Regulated by the Financial Services Authority

Hill Samuel Asset Management International Limited, Company No 106723
Registered Office: is in the United Kingdom at 15 Dalkeith Road,
Edinburgh, EH16 5BU
Telephone: 0131 655 8500
Authorised & Regulated by the Financial Services Authority

All of the above companies are part of the Lloyds TSB Group.

#############################################################################
Welcome to the IDUG DB2-L list. To change your subscription options or subscribe or to cancel your subscription, visit the IDUGDB2-L archives webpage at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG List Admins can be reached at
[login to unmask email]

James Campbell

Re: Question from the latest Idug Solutions Journal issue
(in response to Philip Nelson)
I recall seeing a documented situation where the rows can be out of
sequence. From memory it arose because an index was used to support
the ordering, but between the time the RID was obtained from the
index from the index and the time the data page was retrieved, the
ordering/indexed value was changed.

It was somewhat contrived - but possible.

As for duplicated rows - that's easy. Reading through an index,
you've retrieved all the rows with value 'A', while processing the
B's someone runs
UPDATE ... SET COL='C' WHERE COL='A'
and all your A's are there again as C's.

James Campbell

On 27 Nov 2003 at 12:53, Michael Ebert wrote:

> I don't think that even in this situation you could get rows in the wrong
> order. It might just be conceivable that you get a duplicated row, but
> even that seems incredible to me. UR just means you can see rows that are
> uncommitted. If you get a result that doesn't follow the SQL semantics
> (i.e. unordered rows when you specify ORDER BY), it is certainly a serious
> bug no matter what the cause.
>
> Dr. Michael Ebert
> DB2 Database Administrator
> aMaDEUS Data Processing
> Erding / Munich, Germany
>
>
>
> I can answer the second one
>
> Let's say you are reading rows through an index. This ought to guarantee
> sequence right? But after you read an index page (or perhaps even whilst
> you are reading it) the page splits and a new page is created, further
> "down" the index with half of the keys in it.
>
> This might even conceivably result in you reading the same row more than
> once!
>
> Phil Grainger
> Computer Associates
> Product Manager, DB2
> Tel: +44 (0)161 928 9334
> Fax: +44 (0)161 941 3775
> Mobile: +44 (0)7970 125 752
> [login to unmask email]
>
> -----Original Message-----
> Subject: Question from the latest Idug Solutions Journal issue
>
> Hi all,
> Just finish to read the latest issue of Idug SJ , and have some
> interrogations :
> 1- About the article : "The impact of UR on appl performance" : The CPU
> and
> elapsed time of the test running with isolation CS is better than
> isolation
> UR (40s CPU time vs 42 ) ??? only seen the difference in lock request
> number. So you take less locks , you do the same SQL , the same number of
> getpages an the CPU time is worse ?????
>
> 2-About the article : Know your isolation levels " : Craig Mullins
> said : "An oder by clause does not garantee that rows will be returned in
> order if UR isolation level is used" ??? How can you explain that ?
>
>
> #############################################################################
> Welcome to the IDUG DB2-L list. To change your subscription options or subscribe or to cancel your subscription, visit the IDUGDB2-L archives webpage at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG List Admins can be reached at
> [login to unmask email]
>

#############################################################################
Welcome to the IDUG DB2-L list. To change your subscription options or subscribe or to cancel your subscription, visit the IDUGDB2-L archives webpage at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG List Admins can be reached at
[login to unmask email]

Walter Jani&#223;en

Re: Question from the latest Idug Solutions Journal issue
(in response to James Campbell)
James

You wrote (among other things)

>As for duplicated rows - that's easy. Reading through an index,
>you've retrieved all the rows with value 'A', while processing the
>B's someone runs
>UPDATE ... SET COL='C' WHERE COL='A'
>and all your A's are there again as C's.
>

But that is not special to UR. If you fetch with CS, fetch A's, do
something with them, in between occured the update (which has also been
committed), then all your A's are C's right?

And I think the same (or similar) is with order by. Let's assume, a process
is reading the keys 1, 2, and so on. At some time it just read key 10. Now
another process inserts a key, so that the index leaf page hat to be split
just in front of key 10. So key 10 will be moved to another page which will
be pointed to from the original page. Then the second process commits, or
may be, the key inserted be process 2 is not in the range of the predicates
of process 1, then there is no need to commit for process 2. If DB2 wants
to read the next key, it will recognize, that this key has been moved to
another pages. So it will follow the pointer to get to the next logical
page. But how does DB2 know, that it has read the first keys on this page
and it must start with key 11, which is not the first index entry on that
page? Somehow it will cope with that, so where is the difference between UR
and CS with lock avoidance?

#############################################################################
Welcome to the IDUG DB2-L list. To change your subscription options or subscribe or to cancel your subscription, visit the IDUGDB2-L archives webpage at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG List Admins can be reached at
[login to unmask email]

Michael Ebert

Re: Question from the latest Idug Solutions Journal issue
(in response to Walter Janißen)
Hm. Case 1 does sound like a bug description to me. I'd be surprised if
the DB2 developers didn't see it that way as well. In your case 2, since
COL has changed, the retrieved rows are not duplicates, neither are they
out-of-order. The result may be unexpected, but it's not wrong.

Dr. Michael Ebert
DB2 Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany



I recall seeing a documented situation where the rows can be out of
sequence. From memory it arose because an index was used to support
the ordering, but between the time the RID was obtained from the
index from the index and the time the data page was retrieved, the
ordering/indexed value was changed.

It was somewhat contrived - but possible.

As for duplicated rows - that's easy. Reading through an index,
you've retrieved all the rows with value 'A', while processing the
B's someone runs
UPDATE ... SET COL='C' WHERE COL='A'
and all your A's are there again as C's.

James Campbell

On 27 Nov 2003 at 12:53, Michael Ebert wrote:

> I don't think that even in this situation you could get rows in the
wrong
> order. It might just be conceivable that you get a duplicated row, but
> even that seems incredible to me. UR just means you can see rows that
are
> uncommitted. If you get a result that doesn't follow the SQL semantics
> (i.e. unordered rows when you specify ORDER BY), it is certainly a
serious
> bug no matter what the cause.
>
> Dr. Michael Ebert
> DB2 Database Administrator
> aMaDEUS Data Processing
> Erding / Munich, Germany


#############################################################################
Welcome to the IDUG DB2-L list. To change your subscription options or subscribe or to cancel your subscription, visit the IDUGDB2-L archives webpage at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG List Admins can be reached at
[login to unmask email]

Hello

Re: Question from the latest Idug Solutions Journal issue
(in response to Michael Ebert)
Forwarded , from the author of the ISJ's article :

"Brenda - Thanks for your note. To answer the reader's question - there was
no significant reduction of CPU or GETPAGES using an isolation level of
(UR) in the Erwin example using dynamic SQL. The 23% decrease in locking
could perhaps act to increase application concurrency and/or reduce lock
overhead but nowhere near the levels achieved in the second, DSNTIAUL
example. Dynamic SQL is catalog intensive and there are ways to mitigate
that overhead - such as dynamic SQL caching, parameter markers, etc. These
are not included in this discussion.

The second example illustrates that (UR) in a statically bound plan
(DSNTIAUL) can have a significant impact. 494,117 locks reduced to 183
locks. In this example the ability to unload 20G of data on a daily basis
with out impacting the on-line and scheduled utilities depended on using UR
and was of significant benefit to the organization.

Conclusions - dynamic SQL does have overhead that UR isolation may not
alleviate. On the other hand, the impact of UR on static SQL can be
impressive both in reducing lock overhead and by dramatically increasing
application concurrency.

THX JAL
"

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Roger Miller

Re: Question from the latest Idug Solutions Journal issue
(in response to Hello)
The surprise for me in that article is that I did not see any discussion
of CURRENTDATA(YES) or CURRENTDATA(NO) for Cursor stability. The ability
to provide lock avoidance on rows returned with CURRENTDATA(NO) should
make the locking very close to that of UR, while providing better
consistency than UR. When there are no updates or the FOR UPDATE is used
when needed, then CURRENTDATA(NO) is almost always my choice.

Roger Miller

On Thu, 4 Dec 2003 09:23:09 -0600, Christophe Radier <[login to unmask email]>
wrote:

>Forwarded , from the author of the ISJ's article :
>
>"Brenda - Thanks for your note. To answer the reader's question - there
was
>no significant reduction of CPU or GETPAGES using an isolation level of
>(UR) in the Erwin example using dynamic SQL. The 23% decrease in locking
>could perhaps act to increase application concurrency and/or reduce lock
>overhead but nowhere near the levels achieved in the second, DSNTIAUL
>example. Dynamic SQL is catalog intensive and there are ways to mitigate
>that overhead - such as dynamic SQL caching, parameter markers, etc. These
>are not included in this discussion.
>
>The second example illustrates that (UR) in a statically bound plan
>(DSNTIAUL) can have a significant impact. 494,117 locks reduced to 183
>locks. In this example the ability to unload 20G of data on a daily basis
>with out impacting the on-line and scheduled utilities depended on using
UR
>and was of significant benefit to the organization.
>
>Conclusions - dynamic SQL does have overhead that UR isolation may not
>alleviate. On the other hand, the impact of UR on static SQL can be
>impressive both in reducing lock overhead and by dramatically increasing
>application concurrency.
>
>THX JAL
>"

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Venkat Srinivasan

Re: Question from the latest Idug Solutions Journal issue
(in response to Roger Miller)
There are actually several surprises in this discussion. First of all, per
the original email the author seems to discuss an improvment of a meagre 2
seconds. (40 seconds Vs 42 seconds??). This part seems to be vague.
There seems to be no detailed discussion on how such a benchmark was
conducted in the first place. It also seems to carry several blanket
statements without going into the merits of the situation.
Benchmarks should be conducted in a well controlled environment. The
results should be reproducible with a fair degree of accuracy.
Looks like a poor organization of facts, unfortunately got published in a
journal of repute.
One should always applaud the effort of the author but the organization of
facts could have been better.

Regards,
Venkat

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Walter Jani&#223;en

Re: Question from the latest Idug Solutions Journal issue
(in response to Venkat Srinivasan)
Hi

Nobody has replied to that thread anymore, but I think the question
regarding the order by problem is not yet solved. Some people say, you can
get rows out of order if you read with UR, and others say, that's not
possible (I count to the latter). Now who is right? I think that is a
serious problem and it should be clarified. May be Roger can do that. (May
I add in clear and simple words, that even some German guys can understand
it)

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Chris Blaicher

Re: Question from the latest Idug Solutions Journal issue
(in response to Walter Janißen)
If you read the manuals, and Roger has said it before, if you want rows
returned in order, the only way to be sure is to use an ORDER BY clause.
Some forms of processes do return rows in order, but that is by chance, not
by design. Any order of rows returned by one release of DB2 may not be the
same in the next release of DB2 unless an ORDER BY clause is included in the
SQL.

Chris Blaicher
BMC Software


-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Monday, December 08, 2003 8:51 AM
To: [login to unmask email]
Subject: Re: Question from the latest Idug Solutions Journal issue


Hi

Nobody has replied to that thread anymore, but I think the question
regarding the order by problem is not yet solved. Some people say, you can
get rows out of order if you read with UR, and others say, that's not
possible (I count to the latter). Now who is right? I think that is a
serious problem and it should be clarified. May be Roger can do that. (May
I add in clear and simple words, that even some German guys can understand
it)

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". If you will be out of the office, send the SET
DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins
can be reached at [login to unmask email] Find out the latest on
IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Aurora Dellanno

Re: Question from the latest Idug Solutions Journal issue
(in response to Chris Blaicher)
Chris,

The issue is exactly that, i.e. in his article Craig mentions that "An order
by clause does not guarantee that rows will be returned in order if UR
isolation level is used".

I know it sounds weird and it is very perplexing but I do not find this too
odd although MOST inconvenient. If a row can disappear from a result set, or
if there can be phantom reads or duplicate entries, it is equally possible
that once the query has been executed parts of the results may be changed,
but the result set will not be re-ordered.

Or have I totally misunderstood the whole horrible purpose of UR? :-)

Now Walter ich weiss nicht whether the concept is explained easy enough for
some German guys :-D

My less than 0.0002p worth for the day :-)

ciao!

Aurora Emanuela Dell'Anno
Database Analyst
Data Services Group - Bank of America
tel. 66192
ext. 0208 760 6192
[login to unmask email]

* std. disclaimer * MY OPINIONS ARE MY OWN AND NOT THOSE OF MY EMPLOYER

no trees were killed in sending this message. However, a large number of
electrons were seriously inconvenienced :-)



-----Original Message-----
From: Blaicher, Chris [mailto:[login to unmask email]
Sent: 08 December 2003 15:37
To: [login to unmask email]
Subject: Re: Question from the latest Idug Solutions Journal issue


If you read the manuals, and Roger has said it before, if you want rows
returned in order, the only way to be sure is to use an ORDER BY clause.
Some forms of processes do return rows in order, but that is by chance, not
by design. Any order of rows returned by one release of DB2 may not be the
same in the next release of DB2 unless an ORDER BY clause is included in the
SQL.

Chris Blaicher
BMC Software


-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Monday, December 08, 2003 8:51 AM
To: [login to unmask email]
Subject: Re: Question from the latest Idug Solutions Journal issue


Hi

Nobody has replied to that thread anymore, but I think the question
regarding the order by problem is not yet solved. Some people say, you can
get rows out of order if you read with UR, and others say, that's not
possible (I count to the latter). Now who is right? I think that is a
serious problem and it should be clarified. May be Roger can do that. (May
I add in clear and simple words, that even some German guys can understand
it)


Notice to recipient:
The information in this internet e-mail and any attachments is confidential
and may be privileged. It is intended solely for the addressee. If you are
not the intended addressee please notify the sender immediately by
telephone. If you are not the intended recipient, any disclosure, copying,
distribution or any action taken or omitted to be taken in reliance on it,
is prohibited and may be unlawful.
When addressed to external clients any opinions or advice contained in this
internet e-mail are subject to the terms and conditions expressed in any
applicable governing terms of business or client engagement letter issued by
the pertinent Bank of America group entity.
If this email originates from the U.K. please note that Bank of America,
N.A., London Branch, Banc of America Securities Limited and Banc of America
Futures Incorporated are regulated by the Financial Services Authority.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Walter Jani&#223;en

Re: Question from the latest Idug Solutions Journal issue
(in response to Aurora Dellanno)
Hi all

I got it. I read the whole thread a few time and especially James' reply
led me to my understanding. I even think now, that DB2 is not able to avoid
the wrong ordering.

Suppose the following SQL-statement:

SELECT col1, col2, col3
FROM TABLE T1
ORDER BY col1
WITH UR

and suppose further, there is an index on col1 and the sort is avoided
because of that index. The access path is not index only (I think that is
important). The rows in the table have keys 10, 20, 30 and 40.
Now when the statement picks up index value 30 and before reading the row
pointed by the RID, someone else changed col1 in that row to 50. Because
there is no additional sort, the rows are out of order and I see no way to
avoid this.
If DB2 would take the value from the index, inconsistencies in between a
row will occur, but of course I am not sure, if DB2 picks up the value for
col1 stored in the index (then the ordering is preserved) or the value
stored in the data row.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Roger Miller

Re: Question from the latest Idug Solutions Journal issue
(in response to Walter Janißen)
It is too easy to get wrong answers with UR. The documentation is
provided in the books. Cursor stability with currentdata(no) is better
for most situations. Here is one paste from Admin,

"What ensures that concurrent users access consistent data?

"If you do not use uncommitted read (UR) isolation, DB2 automatically
controls access using locks. You can trade locking resources among
concurrent users, but you cannot violate the basic principle of locking
control. No program can access data that another program changed but not
yet committed.

"However, if you use uncommitted read (UR) isolation, you can violate that
basic principle of locking. Uncommitted read (UR) isolation lets users to
see uncommitted data. Although the data is physically consistent, a number
of logical inconsistencies can occur, or the data could be wrong. ..."

Here is another one ...

"Use ISOLATION(UR) cautiously: UR isolation acquires almost no locks on
rows or pages. It is fast and causes little contention, but it reads
uncommitted data. Do not use it unless you are sure that your applications
and end users can accept the logical inconsistencies that can occur."

Here is one more ...

"ISOLATION (UR) Allows the application to read while acquiring few locks,
at the risk of reading uncommitted data. UR isolation applies only to read-
only operations: SELECT, SELECT INTO, or FETCH from a read-only result
table.

"There is an element of uncertainty about reading uncommitted data.
Example: An application tracks the movement of work from station to
station along an assembly line. As items move from one station to another,
the application subtracts from the count of items at the first station and
adds to the count of items at the second. Assume you want to query the
count of items at all the stations, while the application is running
concurrently.

"What can happen if your query reads data that the application has changed
but has not committed?

"If the application subtracts an amount from one record before adding it
to another, the query could miss the amount entirely.

"If the application adds first and then subtracts, the query could add the
amount twice.

"If those situations can occur and are unacceptable, do not use UR
isolation."

one more ...

"When can you use uncommitted read (UR)? You can probably use UR
isolation in cases like the following ones:
o When errors cannot occur.
Example: A reference table, like a table of descriptions of parts by
part number. It is rarely updated, and reading an uncommitted update is
probably no more damaging than reading the table 5 seconds earlier. Go
ahead and read it with ISOLATION(UR).
Example: The employee table of Spiffy Computer, our hypothetical user.
For security reasons, updates can be made to the table only by members
of a single department. And that department is also the only one that can
query the entire table. It is easy to restrict queries to times when no
updates are being made and then run with UR isolation.

o When an error is acceptable.
Example: Spiffy wants to do some statistical analysis on employee data.
A typical question is, “What is the average salary by sex within education
level?” Because reading an occasional uncommitted record cannot affect
the averages much, UR isolation can be used.

o When the data already contains inconsistent information.
Example: Spiffy gets sales leads from various sources. The data is often
inconsistent or wrong, and end users of the data are accustomed to
dealing with that. Inconsistent access to a table of data on sales leads
does not add to the problem.

"Do NOT use uncommitted read (UR):
When the computations must balance
When the answer must be accurate
When you are not sure it can do no damage"

If people have the false belief that isolation UR will always give correct
answers, then that belief is incorrect. This is the biggest reason for
using isolation CS currentdata(no). The performance is very close to
isolation UR, but pages or rows that are being changed still get
consistent answers.

Roger Miller, DB2 UDB for z/OS ibm.com/software/db2zos


On Wed, 10 Dec 2003 03:29:19 -0600, [login to unmask email] wrote:

>Hi all
>
>I got it. I read the whole thread a few time and especially James' reply
>led me to my understanding. I even think now, that DB2 is not able to
avoid
>the wrong ordering.
>
>Suppose the following SQL-statement:
>
>SELECT col1, col2, col3
> FROM TABLE T1
> ORDER BY col1
> WITH UR
>
>and suppose further, there is an index on col1 and the sort is avoided
>because of that index. The access path is not index only (I think that is
>important). The rows in the table have keys 10, 20, 30 and 40.
>Now when the statement picks up index value 30 and before reading the row
>pointed by the RID, someone else changed col1 in that row to 50. Because
>there is no additional sort, the rows are out of order and I see no way to
>avoid this.
>If DB2 would take the value from the index, inconsistencies in between a
>row will occur, but of course I am not sure, if DB2 picks up the value for
>col1 stored in the index (then the ordering is preserved) or the value
>stored in the data row.
>
>--------------------------------------------------------------------------
-------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". If you will be out of the office, send
the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List
Admins can be reached at [login to unmask email] Find out the
latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Michael Ebert

Re: Question from the latest Idug Solutions Journal issue
(in response to Roger Miller)
Yes, the basic database theory examples on what locking is for. But it
does not address whether DB2 can return unordered data when ORDER BY is
explicitely coded - something I continue not to believe. As stated
specifically, using WITH UR returns physically consistent data.
Also it seems to me, that in the example with items on an assembly line
moving from one station to another, using anything except RR (or Oracle
with its point-in-time consistency) might return logically inconsistent
results (but I admit that locking is not my speciality; being paranoid, I
mistrust & double-check everything I get from elsewhere anyway, so I don't
really care :-).

Dr. Michael Ebert
DB2 Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany



It is too easy to get wrong answers with UR. The documentation is
provided in the books. Cursor stability with currentdata(no) is better
for most situations. Here is one paste from Admin,

....

If people have the false belief that isolation UR will always give correct
answers, then that belief is incorrect. This is the biggest reason for
using isolation CS currentdata(no). The performance is very close to
isolation UR, but pages or rows that are being changed still get
consistent answers.

Roger Miller, DB2 UDB for z/OS ibm.com/software/db2zos


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Walter Jani&#223;en

Re: Question from the latest Idug Solutions Journal issue
(in response to Michael Ebert)
Hi Michael

Did you read my try of an explanation, why DB2 can retrieve rows in an
unordered way, even when you code ORDER BY. Before I discovered that, I was
believing too, that this is impossible, but I think, my example illustrates
that it can be.

On Mon, 15 Dec 2003 19:10:07 +0100, Michael Ebert <[login to unmask email]>
wrote:

>Yes, the basic database theory examples on what locking is for. But it
>does not address whether DB2 can return unordered data when ORDER BY is
>explicitely coded - something I continue not to believe. As stated
>specifically, using WITH UR returns physically consistent data.
>Also it seems to me, that in the example with items on an assembly line
>moving from one station to another, using anything except RR (or Oracle
>with its point-in-time consistency) might return logically inconsistent
>results (but I admit that locking is not my speciality; being paranoid, I
>mistrust & double-check everything I get from elsewhere anyway, so I don't
>really care :-).
>
>Dr. Michael Ebert
>DB2 Database Administrator
>aMaDEUS Data Processing
>Erding / Munich, Germany
>
>
>
>It is too easy to get wrong answers with UR. The documentation is
>provided in the books. Cursor stability with currentdata(no) is better
>for most situations. Here is one paste from Admin,
>
>....
>
>If people have the false belief that isolation UR will always give correct
>answers, then that belief is incorrect. This is the biggest reason for
>using isolation CS currentdata(no). The performance is very close to
>isolation UR, but pages or rows that are being changed still get
>consistent answers.
>
>Roger Miller, DB2 UDB for z/OS ibm.com/software/db2zos
>
>
>---------------------------------------------------------------------------
------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". If you will be out of the office, send the
SET DB2-L NO MAIL command to [login to unmask email] The IDUG List
Admins can be reached at [login to unmask email] Find out the
latest on IDUG conferences at http://conferences.idug.org/index.cfm
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Michael Ebert

Re: Question from the latest Idug Solutions Journal issue
(in response to Walter Janißen)
Yes I did, but I don't believe that's possible. UR isn't the same as
"anything goes" (at least, I hope so). Your scenario would return
physically inconsistent data. Even if DB2 avoids (most) locks when using
UR, it doesn't mean that it can return just anything; there must be some
means of preventing inconsistencies between index key and table row, or
within a table row.

Dr. Michael Ebert
DB2 Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany



Hi Michael

Did you read my try of an explanation, why DB2 can retrieve rows in an
unordered way, even when you code ORDER BY. Before I discovered that, I
was
believing too, that this is impossible, but I think, my example
illustrates
that it can be.

On Mon, 15 Dec 2003 19:10:07 +0100, Michael Ebert <[login to unmask email]>
wrote:

>Yes, the basic database theory examples on what locking is for. But it
>does not address whether DB2 can return unordered data when ORDER BY is
>explicitely coded - something I continue not to believe. As stated
>specifically, using WITH UR returns physically consistent data.
>Also it seems to me, that in the example with items on an assembly line
>moving from one station to another, using anything except RR (or Oracle
>with its point-in-time consistency) might return logically inconsistent
>results (but I admit that locking is not my speciality; being paranoid, I
>mistrust & double-check everything I get from elsewhere anyway, so I
don't
>really care :-).
>
>Dr. Michael Ebert
>DB2 Database Administrator
>aMaDEUS Data Processing
>Erding / Munich, Germany


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Roger Miller

Re: Question from the latest Idug Solutions Journal issue
(in response to Michael Ebert)
I think that Walter is correct. With UR, it is possible to get duplicate
rows, missing rows, so out of order is probably possible too. If the rows
are sorted in a workfile, the ordering should be fine. Locking is the
mechanism we use for consistency, so turning off locking removes the
consistency. Having the DBMS provide all of the consistency with RR
generally provides too little concurrency.

Roger Miller

On Tue, 16 Dec 2003 02:26:16 -0600, [login to unmask email] wrote:

>Hi Michael
>
>Did you read my try of an explanation, why DB2 can retrieve rows in an
>unordered way, even when you code ORDER BY. Before I discovered that, I
was
>believing too, that this is impossible, but I think, my example
illustrates
>that it can be.
>
>On Mon, 15 Dec 2003 19:10:07 +0100, Michael Ebert <[login to unmask email]>
>wrote:
>
>>Yes, the basic database theory examples on what locking is for. But it
>>does not address whether DB2 can return unordered data when ORDER BY is
>>explicitely coded - something I continue not to believe. As stated
>>specifically, using WITH UR returns physically consistent data.
>>Also it seems to me, that in the example with items on an assembly line
>>moving from one station to another, using anything except RR (or Oracle
>>with its point-in-time consistency) might return logically inconsistent
>>results (but I admit that locking is not my speciality; being paranoid, I
>>mistrust & double-check everything I get from elsewhere anyway, so I
don't
>>really care :-).
>>
>>Dr. Michael Ebert
>>DB2 Database Administrator
>>aMaDEUS Data Processing
>>Erding / Munich, Germany
>>
>>
>>It is too easy to get wrong answers with UR. The documentation is
>>provided in the books. Cursor stability with currentdata(no) is better
>>for most situations. Here is one paste from Admin,
>>
>>....
>>
>>If people have the false belief that isolation UR will always give
correct
>>answers, then that belief is incorrect. This is the biggest reason for
>>using isolation CS currentdata(no). The performance is very close to
>>isolation UR, but pages or rows that are being changed still get
>>consistent answers.
>>
>>Roger Miller, DB2 UDB for z/OS ibm.com/software/db2zos

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm