RE: Locking records in DB2

 

Joe,  

 

The question for debate is why use timestamp or counter fields when one can
use the actual database fields.  Making changes to all files to add counter
or timestamp fields would be

Cumbersome as far as RPG recompiles, data migration, programmer time, etc
when  it is not needed.  A generic concurrency and update strategy would be:


 

1.      When data is read, create a “Row” bean to hold the data from the db.
Inside the Row bean create a collection of “Field” beans.  One Field bean
for each field in the file being read.  Row has a collection of Fields.  
2.      When a Field bean is updated, it can record the new value but also
maintain the original value as well as a Boolean indicating the change.
Fields can also be soft marked to indicate they are a keys or they don’t
allow concurrent updates, and a host other generic software behaviors such
as validation, formatting, required entries, etc.  
3.      When you want to update the database, a generic method runs so that
loops through each Field beans and asks if it has changed.  If all Fields
return false, no update occurs, if they have changed, SQL is generated where
ONLY the fields that have changed are on the UPDATE …. SET Field1 =
‘NewValue’  and key fields plus any non concurrent fields are generated in
the “where” clause.  
4.      If the update operation returns 0 records updated, then inform the
user and handle.  

 

The benefit of this is generic code in a single location handles ALL updates
to all files as well as concurrency checks.  Once written the code is used
for all cases present and future versus custom code for each file and
application.  Here is that code:  

 

Generic Row – Record Checks for Updates – Loop through all fields and see if
they have changed:  

 

            /**

             * Returns whether or not this Row's Fields have changed. Ignore
CURRENT

             * VALUE fields.

             * 

             * @return boolean

             */

            public boolean isChanged() {

                        // we use the fieldNames.size() for performance
reasons

                        Field field = null;

                        for (int i = 0; i < getFieldNames().size(); i++) {

                                    field = getField(i);

                                    if (field.isChanged() && !(field
instanceof ICurrentValueField)) {

                                                return true;

                                    }

                        }

                        return false;

            }

 

 

Field Class – Any changes to a field toggle on a Boolean for each field
changed

 

            /**

             * Returns wheter or not this Field's

             * value has changed.

             *

             * @return boolean

             */

            public boolean isChanged() {

                        return changed;

            }

 

 

Paul Holm…

 

> From: Paul Holm
> 
> Update x.y  set name = 'Frank' where keyField = 9 and lockfield1 =
"old
> value1' and lockfield2 = 'old value2'
 
If you're going to do this, please include the code that checks for lack
of update.  I assume there is some magic field in the SQL return data
that indicates the number of records updated; if that value is zero,
then perform the "record was updated by someone else" logic.
 
This also gets really cumbersome when there are lots of fields to check.
 
 
 
 
 
 
 
 
 
 
**********************
 
> b.  Using a counter or timestamp loses this level of control.  An
> unimportant field change could through out a pending change when
really it
> shouldn't have to.
 
If you have dissociated field groups (whether or not such a database
design is valid is a different discussion for a different day), simply
add one counter for each field group.  If you have data that can be
overwritten, only update the counter if an "important" field is updated.
 
 
> In addition, most databases don't have this counter or
> timestamp and folks are resistant to adding a new field to all files
for
> this purpose.
 
This is not a good reason, especially if you have encapsulated your
updates in a single place.  It only gets to be problematic when you have
database updates littered throughout your application, which is poor
architecture in the first place.
 
Joe

 

 

Paul Holm
Senior Web Architect
PlanetJ Corp. 
Phone: 760-432-0600, Cell: 760-415-8830
WOW, Web Applications In Under 5 Minutes
HYPERLINK "http://www.gotWebData.net"; \nhttp://www.gotWebData.net