ambiguous cursor

manish raj kr

ambiguous cursor
Dear List,
Recently while going thru an article related DB2 application performance I
came across two keywords “ambiguous cursor” and “non ambiguous” cursor. I
could figure out the difference between these two types of cursor.
I request a clear picture regarding this from esteem member form the list.
I would like to know the way these cursors are being defined, opened and
fetched in application program. Also the advantage of one above other.

I ma a novice to DB2 world.

Thanks in advance,
Kr Manish



Phil Grainger

Re: ambiguous cursor
(in response to manish raj kr)
Kr

In VERY simple terms, an UNAMBIGUOUS cursor is one where DB2 knows whether
you are READ ONLY or whether you are going to perform UPDATES

An AMBIGUOUS cursor is one where DB2 does NOT know whether you are going to
update or not.

If you say FOR FETCH ONLY or FOR UPDATE OF, then you have a NON-AMBIGUOUS
cursor

If you say NEITHER of these, then you MAY (or may not) have an AMBIGUOUS
cursor. For example, if you have an ORDER BY and Db2 uses a SORT to do the
ordering, then the cursor will become READ ONLY and hence UM-AMBIGUOUS.
However, if it uses an index to do the ordering then the cursor will be
AMBIGUOUS.

It is MUCH better for DB2 to be able to tell whether a cursor is read only
or not - it can then make different locking decisions for example.

BEGIN SOAPBOX
There is NO reason NOT to tell DB2 what you are doing. All cursors should
have FOR FETCH ONLY or FOR UPDATE OF
END SOAPBOX

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: Kr Manish [mailto:[login to unmask email]
Sent: 16 January 2003 12:58
To: [login to unmask email]
Subject: [DB2-L] ambiguous cursor


Dear List,
Recently while going thru an article related DB2 application performance I
came across two keywords "ambiguous cursor" and "non ambiguous" cursor. I
could figure out the difference between these two types of cursor.
I request a clear picture regarding this from esteem member form the list.
I would like to know the way these cursors are being defined, opened and
fetched in application program. Also the advantage of one above other.

I ma a novice to DB2 world.

Thanks in advance,
Kr Manish

================



Phil Grainger

Re: ambiguous cursor
(in response to Georg Peter)
Kr

In VERY simple terms, an UNAMBIGUOUS cursor is one where DB2 knows whether
you are READ ONLY or whether you are going to perform UPDATES

An AMBIGUOUS cursor is one where DB2 does NOT know whether you are going to
update or not.

If you say FOR FETCH ONLY or FOR UPDATE OF, then you have a NON-AMBIGUOUS
cursor

If you say NEITHER of these, then you MAY (or may not) have an AMBIGUOUS
cursor. For example, if you have an ORDER BY and Db2 uses a SORT to do the
ordering, then the cursor will become READ ONLY and hence UM-AMBIGUOUS.
However, if it uses an index to do the ordering then the cursor will be
AMBIGUOUS.

It is MUCH better for DB2 to be able to tell whether a cursor is read only
or not - it can then make different locking decisions for example.

BEGIN SOAPBOX
There is NO reason NOT to tell DB2 what you are doing. All cursors should
have FOR FETCH ONLY or FOR UPDATE OF
END SOAPBOX

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: Kr Manish [mailto:[login to unmask email]
Sent: 16 January 2003 12:58
To: [login to unmask email]
Subject: [DB2-L] ambiguous cursor


Dear List,
Recently while going thru an article related DB2 application performance I
came across two keywords "ambiguous cursor" and "non ambiguous" cursor. I
could figure out the difference between these two types of cursor.
I request a clear picture regarding this from esteem member form the list.
I would like to know the way these cursors are being defined, opened and
fetched in application program. Also the advantage of one above other.

I ma a novice to DB2 world.

Thanks in advance,
Kr Manish

================



Georg Peter

AW: ambiguous cursor
(in response to Phil Grainger)
Kr Manish,

sometimes DB2 for OS/390 cannot properly determine wheter a CURSOR is
read-only. This type of CURSOR is called an "ambigious cursor".

You can ensure that a CURSOR is unambigious in a simple way: Use the FOR
READ ONLY clause. And then DB2 can work with a function called BLOCK FETCH
(= a very fast way to send rows over the network).

If data can be updated through a cursor DB2 must send the data over the
network one row at a time.....

In additon to mention: Certain CURSOR are unambigous (for read only) by
nature, e.g.
- if you are joining tables
- using UNION or UNION ALL
- using a scalar function in the first SELECT clause
- using an ORDER BY
and so on and so on...

HTH.

With kind regards - mit freundlichen Gruessen,
Georg H. Peter c/o
-------------------------------------------------------------------
Datenzentrale Baden-Wuerttemberg
Software Development & Technology Center
Knowledge Center Database Systems
Krailenshaldenstrasse 44, 70469 Stuttgart, Germany, EURurope
e:mail [login to unmask email]
Phone 0049-711-8108-271
PC-Fax 004971189696071
Internet (only in german language):http://www.dzbw.de
----------------------------------------------------------------------
Life's too short to be taken seriously ALL the time...



-----Ursprüngliche Nachricht-----
Von: Kr Manish [mailto:[login to unmask email]
Gesendet am: Donnerstag, 16. Januar 2003 13:58
An: [login to unmask email]
Betreff: ambiguous cursor

Dear List,
Recently while going thru an article related DB2 application performance I
came across two keywords "ambiguous cursor" and "non ambiguous" cursor. I
could figure out the difference between these two types of cursor.
I request a clear picture regarding this from esteem member form the list.
I would like to know the way these cursors are being defined, opened and
fetched in application program. Also the advantage of one above other.

I ma a novice to DB2 world.

Thanks in advance,
Kr Manish



http://listserv.ylassoc.com. The owners of the list can be reached at
[login to unmask email]