Chapter 6. Recovery 197
completes, the updated information from the work-in-progress table can be stored in the
actual table. This process is within the scope of a DB2 logical unit of work, so DB2 locking
mechanisms can be used to guarantee consistency.
A business process requiring multiple transactions can be implemented by tying transactions
together using artificial keys: A logical transaction identifier can be passed from the front end
(which is aware of a logical unit of work containing n physical units of work) to application
processes in the back end.
The work-in-progress table solves the consistency issues of the application but might create
another challenge if multiple transactions running in parallel use the same table. The
work-in-progress table becomes a hot spot.
There are several ways we can deal with the hot spot. Using UR isolation can be a solution for
those statements that retrieve data. UR isolation does not, however, provide a solution for
UPDATE and DELETE. The use of row locking is appropriate unless this table is used in a
data sharing environment. In a data sharing environment, consider using MAXROWS 1.
To remove any contention problem, it is best to add a column that uniquely identifies your
application logical unit of work. Adding this column to all WHERE clauses guarantees that you
do not really need UR, although there might be a performance benefit, provided that the
additional column is a matching predicate in the index.
A work-in-progress table can vary in size dramatically, starting with zero rows, growing during
the day, and containing again zero rows at the end of the day (assuming your business cases
are finished during online hours). This makes it even harder to collect proper statistics to
ensure appropriate access paths. Consider using the “VOLATILE” attribute for a
work-in-progress table to guarantee index access even if no column statistics are available.
Also try to keep a minimum number of indexes (one if possible) on a work-in-progress table.
6.5.4 Restricting other applications’ data access
If your application needs to protect data from being accessed by other applications, consider
locking tables or sets of tables using logical application locking. In this context, when we talk
about logical application locking we assume a control table containing different rows for
critical tables as mentioned earlier. In case a table is not allowed to be updated by other
processes, the control table contains at least one entry per affected table (or set of tables).
Example 6-6 shows table T1 is not allowed to be updated by other applications and table T2
is not allowed to be read by other applications.
Example 6-6 Logical locking table
TABLE_NAME JOB_NAME TIMESTAMP LOCK_TYPE
---------- -------- -------------------------- ---------
T1 JOB1 2005-07-12-184.108.40.2062282 UPDATE
T2 JOB2 2005-07-13-08.14.20.825462 READ
Using this method implicitly forces applications accessing tables contained as rows in a
control table to read this table first to access the operational data. If no row can be found, all
access is granted. Depending on the lock type, only reads or updates are currently allowed on
the specified table.
This technique might only be used for critical tables where retrieval of inconsistent data
cannot be tolerated (for example, as a result of application problems) or exclusive access is
needed to ensure data consistency during data updates (for example, job step scope unit of
work). You can also use logical application locking in case you encounter application