RE: Row locks in DB2/400 UDB

[Jim Damato <jdamato@xxxxxxxxxxxxxxxxx>]

I've seen apps where, in the holy name of the audit, programs have been
changed to write canceled transactions as canceled transactions.  In order
to preserve an unbroken sequence a record is written every time a next
sequence number is generated.

The database equivalent of "this page was intentionally left blank"

-Jim

-----Original Message-----
From: rob@xxxxxxxxx [mailto:rob@xxxxxxxxx]
Sent: Friday, August 08, 2003 9:56 AM
To: Midrange Systems Technical Discussion
Subject: Re: Row locks in DB2/400 UDB


Booth,

Why not skip numbers?
Perhaps it has to do with the fact that some ISO auditors are real 
a$$holes.  Like the ones that we insist that we get the source code to MS 
products so that we can change it to say "Page x of y".  Lets them know if 
there are any missing pages.

Having a server program is a good idea.  Powered by a data queue or some 
such thing.  If the backout concerns you, then do it as a trigger.  Only 
when it actually goes to write the record to disk do you grab a new 
number.

Of course, if you're running V5R2 then I'd use an identity column.


Rob Berendt
-- 
"They that can give up essential liberty to obtain a little temporary 
safety deserve neither liberty nor safety." 
Benjamin Franklin 





"Booth Martin" <Booth@xxxxxxxxxxxx>
Sent by: midrange-l-bounces@xxxxxxxxxxxx
08/08/2003 09:11 AM
Please respond to Midrange Systems Technical Discussion
 
        To:     <midrange-l@xxxxxxxxxxxx>
        cc: 
        Fax to: 
        Subject:        Re: Row locks in DB2/400 UDB


How much time is there between the access and the update? the three lines 
of
code should be together. "get the number", "add 1 to the number", "update
the record".
 
Why not write one program that does that, and let the other programs call
that program for the next number? 
 
Sometimes people like to get the next number but not do the actual update
till the user has finished the process just in case the user bails out. 
The
idea being so that there's no wasted numbers. That always struck me as an
odd decision because numbers are free.
 
 
 
---------------------------------------------------------
Booth Martin http://www.MartinVT.com
Booth@xxxxxxxxxxxx
---------------------------------------------------------
 
-------Original Message-------
 
From: Midrange Systems Technical Discussion
Date: Friday, August 08, 2003 9:02:07 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Row locks in DB2/400 UDB
 
All
 
Is there a way to lock a row in DB2/400 UDB SQL without using commitment
control?
 
We have a table that holds the 'next sequence number'. Whenever several
processes access the table, we end up with a scenario like:
 
program A accesses the table and grabs
the next counter number (counter number = 50)
 
program B accesses the table and grabs
the next counter number (counter number = 50)
 
program B adds one to the counter
number, and updates the table (counter number = 51)
 
program A adds one to the counter
number, and updates the table (counter number = 51)
 
Now, there are two transactions with an identical sequence number.
 
We're doing a simple select to fetch the sequence number, and we are not
using commitment control.
 
Any suggestions?
 
Thanks
 
-Doc