DB2 11 Application Developer for z/OS, part 7 - Units of work, concurrency, and checkpoint/restart
A good application should be a good citizen in the environment it is living in. This means that it should not only be good to its users, but it also should respect the law and order of the environment it is running in. For DB2 applications this can mean that the applications should consider the highly competitive environment, where many applications compete for the same DB2 resources or even access the same data. Applications must also cope with situations where some of the actions (like updating rows) may fail because of other applications doing the same task. So the program should be coded in such way that it can recover from such a situation either by a reasonable error message or by trying to restart its processing to some of the previously established checkpoints.
This can be a problem with database applications in general because they are often being developed on a single machine running a single user with no concurrent activity. Such applications can behave badly when running outside the comfortable test environment.
The seventh section of the test will remind you that the DB2 applications run in a busy environment and need to cope with concurrency issues that may occur. We cover the basic concepts like unit of work, unit of recovery, but also more advanced topics like isolation levels and concurrency options.
We have already covered some of the topics in previous blog posts partially, but I think it will not hurt if we mention such topics here again.
Ability to use Isolation Levels & bind release options
- I would probably start study the following overview of programming for concurrency first.
- Isolation of transactions is an important concept of Databases in general. I prefer to look at this in terms of phenomena that can occur - dirty reads, non-repeatable reads, phantom reads - and then deduce the isolation levels accordingly:
- Uncommitted Read
- where all the phenomena may occur
- Cursor Stability
- where phantom reads and non-repeatable reads may occur, but dirty reads will not occur
- Read Stability
- where the phantom reads may occur, but not the other two
- Repeatable Read
- where none of the phenomena may occur, because this level does not allow updates of data while the application is reading.
- Uncommitted Read
Please note that if you come from a different environment than DB2, you may be familiar with a slightly different terminology.
- How to choose the correct isolation level depends on how your application should interact with other transactions on the same data.
- A nice and a short overview of isolation levels is also provided in the ISOLATION BIND option. The default isolation level is Cursor Stability.
- During the run of an application, DB2 acquires locks and other resources on objects the transactions access. The RELEASE BIND option determines when to release these resources - either at commit or when the thread de-allocates.
- This section helps to choose a proper RELEASE option. Make sure you understand how it works for both static and dynamic SQL.
Identify the differences between COMMIT, ROLLBACK, and SAVEPOINTS
- For basic terminology and notations refer to the Introduction to DB2. Look here for an overview of COMMIT and ROLLBACK processing.
- A COMMIT statement ends the transaction and makes all the changes done by the unit of work permanent.
- There are two flavours of the ROLLBACK statement - (1) the first one (ROLLBACK) ends the transaction and backs out all the changes made; (2) the second flavour (ROLLBACK TO SAVEPOINT) backs out all the changes made after a savepoint, but does not end the transaction (this is sometimes called a partial rollback). Look here for an overview of ROLLBACK.
- Savepoints are defined by the SAVEPOINT You can find a few tips on to use savepoints here. Please note that if you set the same named savepoint more than once, the new value replaces the old value. If you want to avoid this, use UNIQUE savepoints.
- A savepoint can be released using the RELEASE SAVEPOINT It is important to know that it releases not only the specified savepoint, but also any subsequently established savepoints.
Ability to recognize and code basic Units of Work
- Read the Introduction to DB2 for the basic ideas behind the units of work versus units of recovery. Basically, the main difference in the terminology is that the unit of work is application relevant, while unit of recovery is more DB2 related. The two terms can mean the same if no other recoverable resources than DB2 are used.
- For example, the TSO applications define the unit of work using SQL COMMIT and ROLLBACK statements.
- The principle of two phase commit is described in the Administration guide.
Ability to recognize and code Data Currency options
- We already discussed the data currency options in the fifth part of this series together with the BIND options. Data currency as such may involve isolation levels and different options.
- The CURRENTDATA option for Cursor Stability isolation level has already been mentioned several times in the series. It will not hurt mentioning it here again as it can be beneficial for lock avoidance when CURRENTDATA(NO) is used. Make sure you understand the processing for local and for remote
- A section above describes the isolation levels, there are also ways how to override the isolation level at the statement when needed.
- For very special cases, you can use special statements or options like LOCK TABLE, USECURRENTLYCOMMITTED data, or SKIP LOCKED DATA. However, I believe that proper coded SQL statements, using non-ambiguous cursors, and a good commit strategy should be considered first before choosing some of the specials.
Knowledge of best practices and restrictions for Autonomous Transactions
- An autonomous transaction is an independent transaction with its own COMMIT/ROLLBACK scope. The autonomous transactions were introduced in DB2 11 and implemented using autonomous
- An autonomous procedure is a procedure that executes its own unit of work independently of the calling program, where the updates to the log or audit tables are committed independently of the caller. This is especially useful for logging or auditing procedures. Make sure to review the restrictions of the autonomous procedures (for example that only the Native SQL procedures are supported).
- This link shows how you can control the autonomous procedures via DISPLAY THREAD and CANCEL THREAD.
Applies Process control and Checkpoint / Restart processing
- This section is about the commit strategy and the restart processing of applications. It is a difficult subject, because these concepts depend heavily on the nature of the application. However, DB2 provides some means that were mentioned in this blog who can help you with that - you can use COMMIT, ROLLBACK, SAVEPOINTs, or even use some tables where your application will track its progress.
- Often, the developers don't commit properly for different reasons. Craig Mullins wrote an interesting article about the Fear of Committing.
- IBM provides several hints you should consider when designing a DB2 application, for example:
- Your program should also check the execution of each SQL statement and handle possible errors.