RE: Locking records in DB2

>Note that this has the possibility of wiping out changes done
>"externally" (as in via DFU or ODBC). 

Not if the update stamp is changed by a trigger -- no way around that
(unless you remove the trigger <G>)

>Reread the counter on update and if it has changed, then go through the
whole
>"record has changed" logic.  Otherwise, increment the counter and
update
>the record.

My only caveat here is you're still leaving yourself a window where the
row could be changed after you reread it, before you update it. Instead
I'd do it in one statement. For example, assume you're trying to update
the customer name, and the UpdateCount field had a value of 6 when you
read the row before:

Update customer set Name="NewName", UpdateCount=UpdateCount+1 where ID =
7 and UpdateCount=6

Or, if you use a trigger to update updatecount:

Update customer set Name="NewName" where ID = 7 and UpdateCount=6

If you update a row then the updatecount hadn't changed, if SQL can't
find a row you can assume the updatecount had changed. 

Of course, the tradeoff to using UpdateCount is that _any_ update to the
row should update the updatecount field. So, if there is, for example, a
salesYTD field on the customer row, and an order is processed while
you're looking at changing the customer name then customer name update
will fail. To be successful you'd need to check each column, so your SQL
would become:

Update customer set Name="NewName" where ID = 7 and Name="OldName"

-Walden

------------
Walden H Leverich III
Tech Software
(516) 627-3800 x11
WaldenL@xxxxxxxxxxxxxxx
http://www.TechSoftInc.com

Quiquid latine dictum sit altum viditur.
(Whatever is said in Latin seems profound.)