Looking for Commitment, Part 1

March 11, 2009 Paul Tuohy

In this article I will discuss what commitment control is, why you may want to use it, and the basic requirements for commitment control. In subsequent articles we will look more closely at some of the considerations for using commitment control effectively: how commitment control works; different ways in which it can be implemented; and how it works in ILE.

Throughout these articles I will be using the SQL terminology of schema, table, row, and index as opposed to library, physical file, record, and logical file. However, be aware that commitment control is applicable to both SQL-defined tables and DDS-defined physical files, regardless of which terminology is used.

Background

Commitment control is a powerful, but seldom used, feature that helps ensure the integrity of a database. Commitment control gives you the ability to ensure that a set of related changes across one or more tables in a database are not permanently applied until a program actually commits them. Alternatively, a program may roll back any of the changes instead of committing them. Many would consider commitment control a necessity in the maintenance of a database.

Any programmer who has ever used embedded SQL knows that SQL (a database language) assumes you are making use of commitment control. How often have you changed the COMMIT parameter on the CRTSQLxxxI command?

But if commitment control is such a necessity, why aren’t more applications using this powerful option? There are three main reasons:

Commitment control requires the use of journals. In the early days of the system, the unreasonable fear that the use of journals might consume too much disk space or inadvertently affect system performance meant that many software vendors were reluctant to force their clients to use journals.
Until the advent of RPG IV, it was cumbersome to make optional use of commitment control in programs.
Commitment control used to work only at a job level. When a program issued a commit, it committed all open transactions for the job rather than just for the program. You had to be extremely careful to ensure that a program did not inadvertently commit a transaction that it wasn’t meant to.

So what has changed to make the use of commitment control more acceptable?

Systems are now bigger and faster, and there is much less resistance to the use of journals.
RPG IV allows optional use of commitment control in programs.
ILE provides a means of limiting the scope of commitment control within a job.

Why Use Commitment Control

When a program is processing a transaction that consists of writing, updating, and deleting one or more rows across multiple tables in a database, commitment control offers a means of treating the transaction as a single unit. For example, commitment control provides a way for a program to identify and process an order as a single transaction–even though that order is spread over many rows on many tables, and adding the order involves updating columns in many rows on other tables (e.g., stock figures and customer balance information).

Imagine what would happen if your system suddenly lost power while a program (or a number of programs) was processing a transaction. When the system performs an initial program load (IPL), the databases would now contain a number of incomplete transactions. However, if the programs were using commitment control, the system would automatically roll back any uncommitted rows from the databases.

Now imagine what would happen if a program processing a transaction failed because of a bug. (I know, it’s hard to imagine.) Again, there would be incomplete transactions on the database. Of course, if the program were using commitment control, you would have a means of rolling back the incomplete transactions.

Therefore, commitment control should be a consideration for any transaction that consists of more than one row on one or more tables.

Requirements–Journals and Journal Receivers

Commitment control requires that the tables involved in the transaction being committed are all attached to the same journal.

The basic concept behind journals is to offer a means of database recovery up to a specific point in time. When a table is attached to a journal, the database manager records a copy of every row in the table that is added updated or deleted. This means that the system has a copy of every change that was made to an attached table so, in the case of data loss, the system has a means of recovering all changes that were made to that table.

The journal process consists of two parts: a journal and a journal receiver. Journals are created using the Create Journal (CRTJRN) command and journal receivers are created using the Create Journal Receiver (CRTJRNRCV) command. When a journal is created, it is attached to a journal receiver. You can specify that changes made to a table be recorded in a journal by using the Start Journal Physical File (STRJRNPF) command. You may choose to record before images, after images or both before and after images.

Figure 1: The Journal Process

The journal process is shown in Figure 1. The journal is depicted as a funnel and a bucket represents the journal receiver. As changes are made to tables, a copy of the changes is sent to the journal, which then drops the copies into the attached journal receiver. When a journal receiver is full, a new journal receiver can be created and attached to the journal in place of the original. This is a simple means of managing the amount of space occupied by the journal receivers.

As you will see in a later article, the commitment control process uses special journal entries to enforce commitment of a transaction.

If your tables are already attached to journals, there is nothing else you need to do to implement commitment control–apart from the applying the required code in your programs.

If you are using journals for database recovery (or high availability), you more then likely have a strategy where all tables (regardless of application) are attached to a single journal. If you do not require journals for database recovery, you may want to consider having separate journals per application or per schema (library), as is the default when you create a schema with SQL.

All For Now. . .

In the next article we will have a look at how to implement commitment control in an RPG program.

Paul Tuohy is CEO of ComCon, an iSeries consulting company, and is one of the co-founders of System i Developer, which hosts the RPG & DB2 Summit conferences. He is an award-winning speaker who also speaks regularly at COMMON conferences, and is the author of “Re-engineering RPG Legacy Applications,” “The Programmers Guide to iSeries Navigator,” and the self-study course called “iSeries Navigator for Programmers.” Send your questions or comments for Paul to Ted Holt via the IT Jungle Contact page.