Guru Classic: Looking For Commitment, Part 1
June 19, 2019 Paul Tuohy
Author’s Note: This set of three articles was originally published in March of 2009. (See links in Related Stories below). As companies look to modernizing their applications, commitment control can play an integral role. In my next three Guru Classic articles, I will be updating the content of these articles for free-form RPG.
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, I will look more closely at how commitment control works, different ways in which it can be implemented, how it works in ILE, and some of the considerations for using commitment control effectively.
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.
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 is 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 it 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 that 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.
In the following diagram, 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 than 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 my next article, I will have a look at how you implement commitment control in an RPG program.
Paul Tuohy, IBM Champion and author of Re-engineering RPG Legacy Applications, is a prominent consultant and trainer for application modernization and development technologies on the IBM Midrange. He is currently CEO of ComCon, a consultancy firm in Dublin, Ireland, and partner at System i Developer. He hosts the RPG & DB2 Summit twice per year with partners Susan Gantner and Jon Paris.
Looking for Commitment, Part 1
Looking for Commitment, Part 2