WITH HOLD & FOR UPDATE - CURSORS

Patrick Steurs

WITH HOLD & FOR UPDATE - CURSORS
Sorry,

but we are converting IBM's database DB2 on z/Os to Oracle10G on
Solaris.



In the DB2-environment, we use Sql-cursors with options "WITH HOLD" and
"FOR UPDATE OF" in the same cursor. In these programs we are also using
commit-points before fetching the next record in the cursor. How can we
implement the same feature/behaviour in Oracle ? I think this
db2-feature is incompatible with Oracle . Is this true ?



greetings,


Patrick Steurs
Dba - Database Services

National Bank of Belgium - Eurosystem

Office : +32 (0)2 221 53 84

P Think twice before printing











-----------------------------------------
Visit our website! http://www.nbb.be

"DISCLAIMER: The content of this e-mail message should not be
construed as binding on the part of the National Bank of Belgium
(NBB) unless otherwise and previously stated. The opinions
expressed in this message are solely those of the author and do not
necessarily reflect NBB viewpoints, particularly when the content
of this message, or part thereof, is private by nature or does not
fall within the professional scope of its author."

_____________________________________________________________________

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

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

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

David Seibert

Re: WITH HOLD & FOR UPDATE - CURSORS
(in response to Patrick Steurs)
You think you're sorry now, ....



Dave

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Steurs
Patrick
Sent: Monday, January 18, 2010 8:46 AM
To: [login to unmask email]
Subject: [DB2-L] WITH HOLD & FOR UPDATE - CURSORS



Sorry,

but we are converting IBM's database DB2 on z/Os to Oracle10G on
Solaris.



In the DB2-environment, we use Sql-cursors with options "WITH HOLD" and
"FOR UPDATE OF" in the same cursor. In these programs we are also using
commit-points before fetching the next record in the cursor. How can we
implement the same feature/behaviour in Oracle ? I think this
db2-feature is incompatible with Oracle . Is this true ?



greetings,


Patrick Steurs
Dba - Database Services

National Bank of Belgium - Eurosystem

Office : +32 (0)2 221 53 84

P Think twice before printing








The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it.


Visit our website! http://www.nbb.be "DISCLAIMER: The content of this
e-mail message should not be construed as binding on the part of the
National Bank of Belgium (NBB) unless otherwise and previously stated.
The opinions expressed in this message are solely those of the author
and do not necessarily reflect NBB viewpoints, particularly when the
content of this message, or part thereof, is private by nature or does
not fall within the professional scope of its author."



________________________________

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/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

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

Dirk Herzhauser

Re: WITH HOLD & FOR UPDATE - CURSORS
(in response to David Seibert)

Hi Patrick,

 

Unfortunately I need some Information to dig deeper into this topic.

 

1) Is your goal to prevent other transactions from modifying these rows?

 

2) Or you want to prevent other transactions from reading it?

The select for update is available in oracle as well for pessimistic locking. Oracle has a feature that is called multi versioning maybe -I guess you know this feature- this will help you to accomplish your task.

 

As well here is link that describes the pessimistic and optimistic locking in oracle.

 

http://www.orafaq.com/papers/locking.pdf

 

Oracle knows commit points as well, so I guess I miss some information to help you.

 

Best regards 



Dirk Herzhauser
Data Architect

CSC
Abraham-Lincoln-Park 1
65189 Wiesbaden
Germany
Phone: +49.611.142.22117
Fax: +49.611.142.29512
Mobile: +49.172.6675269
e-Mail: [login to unmask email]
Internet: http://www.de.csc.com


CSC • This is a PRIVATE message. If you are not the intended recipient, please delete without copying and kindly advise us by e-mail of the mistake in delivery. NOTE: Regardless of content, this e-mail shall not operate to bind CSC to any order or other contract unless pursuant to explicit written agreement or government initiative expressly permitting the use of e-mail for such purpose • CSC Deutschland Solutions GmbH • Registered Office: Abraham-Lincoln-Park 1, 65189 Wiesbaden, Germany • Board of Directors: Gerhard Fercho (Chairman), Thomas Nebe, Peter Schmidt • Chairman of the Supervisory Board: Guy Hains • Registered in Germany: HRB 22374

-----IDUG DB2-L <[login to unmask email]> wrote: -----

To: [login to unmask email]
From: Steurs Patrick <[login to unmask email]>
Sent by: IDUG DB2-L <[login to unmask email]>
Date: 18/01/2010 14:45
Subject: [DB2-L] WITH HOLD & FOR UPDATE - CURSORS

Sorry,

but we are converting IBM's database DB2 on z/Os to Oracle10G on Solaris.

 

In the DB2-environment, we use Sql-cursors with options "WITH HOLD" and "FOR UPDATE OF" in the same cursor. In these programs we are also using commit-points before fetching the next record in the cursor. How can we implement the same feature/behaviour in Oracle ? I think this db2-feature is incompatible with Oracle . Is this true ?

 

greetings,


Patrick Steurs
Dba - Database Services

N ational B ank of B elgium - Eurosystem

Office : +32 (0)2 221 53 84

P Think twice before printing




 


Visit our website! http://www.nbb.be "DISCLAIMER: The content of this e-mail message should not be construed as binding on the part of the National Bank of Belgium (NBB) unless otherwise and previously stated. The opinions expressed in this message are solely those of the author and do not necessarily reflect NBB viewpoints, particularly when the content of this message, or part thereof, is private by nature or does not fall within the professional scope of its author."




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.

Patrick Steurs

Re: WITH HOLD & FOR UPDATE - CURSORS
(in response to Dirk Herzhauser)
Hi,



Oracle can't process a cursor with hold and for update. It's always been treated as a cursor for update. So, if you use and Oracle-database, the programmer has to make a choice , either he has to lock the data or to keep a cursor-position. But if you need both features : locking & keeping cursor-position ?



Db2 can process these cursors. This means records/data are locked until commit/rollback AND cursor-positioning has been kept, so, a fetch next-sql does not create an sql-exception.



greetings,



Patrick Steurs





From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Dirk Herzhauser
Sent: maandag 18 januari 2010 15:41
To: [login to unmask email]
Subject: Re: [DB2-L] WITH HOLD & FOR UPDATE - CURSORS



Hi Patrick,



Unfortunately I need some Information to dig deeper into this topic.



1) Is your goal to prevent other transactions from modifying these rows?



2) Or you want to prevent other transactions from reading it?

The select for update is available in oracle as well for pessimistic locking. Oracle has a feature that is called multi versioning maybe -I guess you know this feature- this will help you to accomplish your task.



As well here is link that describes the pessimistic and optimistic locking in oracle.



http://www.orafaq.com/papers/locking.pdf < http://www.orafaq.com/papers/locking.pdf >



Oracle knows commit points as well, so I guess I miss some information to help you.



Best regards



Dirk Herzhauser
Data Architect

CSC
Abraham-Lincoln-Park 1
65189 Wiesbaden
Germany
Phone: +49.611.142.22117
Fax: +49.611.142.29512
Mobile: +49.172.6675269
e-Mail: [login to unmask email]
Internet: http://www.de.csc.com < http://www.de.csc.com/ >


CSC • This is a PRIVATE message. If you are not the intended recipient, please delete without copying and kindly advise us by e-mail of the mistake in delivery. NOTE: Regardless of content, this e-mail shall not operate to bind CSC to any order or other contract unless pursuant to explicit written agreement or government initiative expressly permitting the use of e-mail for such purpose • CSC Deutschland Solutions GmbH • Registered Office: Abraham-Lincoln-Park 1, 65189 Wiesbaden, Germany • Board of Directors: Gerhard Fercho (Chairman), Thomas Nebe, Peter Schmidt • Chairman of the Supervisory Board: Guy Hains • Registered in Germany: HRB 22374



-----IDUG DB2-L <[login to unmask email]> wrote: -----

To: [login to unmask email]
From: Steurs Patrick <[login to unmask email]>
Sent by: IDUG DB2-L <[login to unmask email]>
Date: 18/01/2010 14:45
Subject: [DB2-L] WITH HOLD & FOR UPDATE - CURSORS

Sorry,

but we are converting IBM's database DB2 on z/Os to Oracle10G on Solaris.



In the DB2-environment, we use Sql-cursors with options "WITH HOLD" and "FOR UPDATE OF" in the same cursor. In these programs we are also using commit-points before fetching the next record in the cursor. How can we implement the same feature/behaviour in Oracle ? I think this db2-feature is incompatible with Oracle . Is this true ?



greetings,


Patrick Steurs
Dba - Database Services

N ational B ank of B elgium - Eurosystem

Office : +32 (0)2 221 53 84

P Think twice before printing







________________________________

Visit our website! http://www.nbb.be "DISCLAIMER: The content of this e-mail message should not be construed as binding on the part of the National Bank of Belgium (NBB) unless otherwise and previously stated. The opinions expressed in this message are solely those of the author and do not necessarily reflect NBB viewpoints, particularly when the content of this message, or part thereof, is private by nature or does not fall within the professional scope of its author."



________________________________

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



-----------------------------------------
Visit our website! http://www.nbb.be

"DISCLAIMER: The content of this e-mail message should not be
construed as binding on the part of the National Bank of Belgium
(NBB) unless otherwise and previously stated. The opinions
expressed in this message are solely those of the author and do not
necessarily reflect NBB viewpoints, particularly when the content
of this message, or part thereof, is private by nature or does not
fall within the professional scope of its author."