UPDATE CURRENT OF CURSOR

Frank Swarbrick wrote:
Depending on your isolation level one of two things can happen:
* The row may change under your feet and you wipe someone elses updates.
* You get a deadlock when the read lock get upgraded

You can right that by adding WITH RS USE AND KEEP UPDATE UPDATE LOCKS to
the end of the SELECT query.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

"Frank Swarbrick" <Frank.Swarbrick@efirstbank.comwrote in message
news:48A32175.6F0F.0085.0@efirstbank.com...

Yes this is common practice, but whether it is "standard" depends on what
you are trying accomplish and what level of locking and concurrent access
you need.

I often do a separate update/delete instead of "where current of cursor"
even when there is only one table involved, simply because I want to have
maximum concurrency (minimize locking issues). I declare the cursor as READ
ONLY, and use the WITH HOLD option (so that the cursor does not close upon
commit, and then issue a separate update or delete statement (followed by
frequent commits). Sometimes I may even select the cursor WITH UR if I know
it will not create a data integrity problem (I know that no other programs
are updating the columns in the predicate).

>>On 8/13/2008 at 7:00 PM, in message
<6ghedkFfvbfnU1@mid.individual.net>,
Serge Rielau<srielau@ca.ibm.comwrote:

Great! Thanks for the tip. Makes sense.

Can you clarify the meaning of the following?

"Share: Limits concurrent application processes to read-only operations on
the data."

How is WITH RS USE AND KEEP SHARED LOCKS different than just WITH RS and no
lock-request-clause and how is it different then an UPDATE lock?

I guess I am wondering, if another process attempt to access a table that I
have declared a cursor on, and that other process wants to update it, what
will the other process see if I have SHARED locks on my cursor, and what
will he see if I have UPDATE locks on my cursor? I assume if I have UPDATE
locks then the other process will simply wait until my cursor is closed.
How is SHARED different from this?

Also, does it make sense to specify "FOR READ ONLY"? I guess in my
particular case it is superflulous, since that particular SELECT is already
a read only select. But I'm guessing it's probably a good idea to specify
FOR READ ONLY any time that one is not doing positioned updates/deletes. Is
this a fair statement?

Thanks!
Frank

Frank Swarbrick wrote:
Specifying FOR READ ONLY is generally good practice since it assures
that DB2 can use "blocking" of rows (send more than one row to the
consumer). As you note: In this case it is irrelevant.
I don't think the KEEP SHARED LOCK Sis useful for anything but
completeness of the options.
What you want is USE AND KEEP UPDATE LOCKS

Cheers
Serge


--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

>>On 8/13/2008 at 11:55 PM, in message
<lJPok.10220$Ep1.4961@bignews2.bellsouth.net>, Mark A<xxxxxx@xxxxx.com>
wrote:

This information is very helpful. Thank you!
Frank

>>On 8/14/2008 at 11:43 AM, in message
<6gj96pFg5gjqU1@mid.individual.net>,
Serge Rielau<srielau@ca.ibm.comwrote:
updates.





Great! Thanks again.

>>On 8/14/2008 at 11:43 AM, in message
<6gj96pFg5gjqU1@mid.individual.net>,
Serge Rielau<srielau@ca.ibm.comwrote:


A further question... Below are two explain plans for an UPDATE. The first
does a regular update using the primary key in the predicate. The second
uses the RID_BIT() function to do a 'direct read' of the record.

---------------------------------------------------------------
Statement:

UPDATE ACCOUNTS.ACCOUNT_ADJUSTMENTS AS ADJ SET STATUS_IND =:H9_0,
INVOICE_ID =:H9_1, LAST_ID =:H9_2
WHERE ADJUSTMENT_ID =:H9_3


Section Code Page = 1252

Estimated Cost = 21.316250
Estimated Cardinality = 1.000000

Access Table Name = ACCOUNTS.ACCOUNT_ADJUSTMENTS ID = 9,775
| Index Scan: Name = ACCOUNTS.ADJUSTMENTS_PK ID = 1
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: ADJUSTMENT_ID (Ascending)
| #Columns = 0
| Single Record
| Fully Qualified Unique Key
| #Key Columns = 1
| | Start Key: Inclusive Value
| | | | 1: ?
| | Stop Key: Inclusive Value
| | | | 1: ?
| Data Prefetch: Eligible 0
| Index Prefetch: None
| Lock Intents
| | Table: Intent Exclusive
| | Row : Exclusive
Update: Table Name = ACCOUNTS.ACCOUNT_ADJUSTMENTS ID = 9,775
---------------------------------------------------------------
Statement:

UPDATE ACCOUNTS.ACCOUNT_ADJUSTMENTS AS ADJ SET STATUS_IND =:H9_0,
INVOICE_ID =:H9_1, LAST_ID =:H9_2
WHERE RID_BIT(ADJ)=:H9_3


Section Code Page = 1252

Estimated Cost = 10.647691
Estimated Cardinality = 1.000000

Access Table Name = ACCOUNTS.ACCOUNT_ADJUSTMENTS ID = 9,775
| #Columns = 3
| Single Record
| Fetch Direct Using Row IDs
| Lock Intents
| | Table: Intent Exclusive
| | Row : Exclusive
Update: Table Name = ACCOUNTS.ACCOUNT_ADJUSTMENTS ID = 9,775
---------------------------------------------------------------

The second is obviously "twice as efficient" as the first. Seems to me the
second is the way to go in all situations where you have already done a
SELECT to retrieve information about the row you end up updating. This is a
slightly different example, I think, than is described in the article
"Improve concurrency with DB2 9.5 optimistic locking: New optimistic locking
feature to avoid maintaining long-lived locks"
(http://www.ibm.com/developerworks/d...-0801schuetz/in
dex.html) Technically I am not using this to avoid a long-lived lock, in
the sense that the article intends. I'm just using it to fetch the record
quicker during a batch update process.

Seems almost too good to be true. Am I missing some reason that I should
not do this?

Thanks,
Frank

Frank Swarbrick wrote:
It's not relational. Also, in theory the rid of a row can change under
some circumstances (e.g. a reorg).



--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

>>On 8/14/2008 at 8:49 PM, in message
<6gk95gFfs65jU1@mid.individual.net>,
Serge Rielau<srielau@ca.ibm.comwrote:

Yes, it's "not relational". Not sure if I care, though, if it's more
efficient.

As for reorg, I am pretty sure I would not be doing a reorg during a batch
processing window (hopefully!).

:-)

Frank