To Commit or Not to Commit?

by Randy Adamski

If data integrity is important to you, you owe it to yourself to learn more about IBM's advanced database feature known as commitment control for the iSeries. This feature promises an all-or-nothing approach to database file changes. If multiple files are being updated, you can guarantee that all the files will be updated, or none of the files will be updated. Why is this important? Well, that's what this article explains.

WHAT IS COMMITMENT CONTROL?

Commitment control is handled by the operating system, but the programmer still has a final say about whether a record (even with bad data) is written to a database file. But if you change your mind, commitment control will definitely make your life easier. Specifically, iSeries commitment control provides a way to process a group of database changes (updates, inserts, or deletes) as a single unit of work (a transaction). This unit of work is atomic, meaning that it's indivisible. It's all or nothing. Commitment control ensures that all of the changes to the files within that transaction/commitment cycle are applied, or, if processing is interrupted before the COMMIT statement is encountered, that all changes within a transaction are removed. Once you see what commitment control can do for you, I believe you will want develop your applications with commitment control in mind.

I had known about commitment control for a long time but always avoided it because I thought it was something that was used only in extreme cases, such as in banking transactions in which one account was debited and another was credited, and unless both were processed, accounts could easily become out of balance. I was completely wrong. So let's create an application and walk through all the steps necessary to take advantage of commitment control. By the end of this article, you will be able to decide whether or not to commit in your shop.

EASY AS 1-2-3

First, there is one prerequisite, and that is another nice IBM feature called journaling. To take advantage of commitment control, the files have to be journaled. I won't get into journaling in great detail; basically, you create a journal receiver with the CRTJRNRCV command, then a journal with the CRTJRN command, specifying the journal receiver you just created, and finally you start journaling on the file or files, with the STRJRNPF command.

Here is the DDS for the file used in this example followed by the command used to create it and the commands to setup the journaling environment:

  * File Name............. CCTESTPF                                       
  *           -----------------------------------------------------       
            R FCCTESTPF                 TEXT('CC Test File')              
  *           =================================       
              CCFLD1         5P 0       COLHDG('CC field1')               
  *           -----------------------------------------------------       
              CCFLD2        10A         COLHDG('CC field2')               
  *           -----------------------------------------------------       
            K CCFLD1                                                      
  *           -----------------------------------------------------       

CRTPF FILE(YourDestinationLibrary/CCTESTPF) SRCFILE(YourSourceFileLibrary/QDDSSRC)

First create the journal receiver:

CRTJRNRCV JRNRCV(MYJRNRCLIB/MYJRNRCV)

Next, create the journal specifying the journal receiver created above:

CRTJRN JRN(MYLIB/MYJRN) JRNRCV(MYJRNRCLIB/MYJRNRCV)

And, finally, start journaling on the physical file:

STRJRNPF FILE(*LIBL/CCTESTPF) JRN(MYLIB/MYJRN)

So my file is being journaled. What's next? I am going to work with a very familiar RPG programmer environment, a CL wrapper around an RPG program. Here's the code for the CL wrapper called CCTEST1:

PGM                              
STRCMTCTL  LCKLVL(*CHG)          
CALL       PGM(*LIBL/CCTEST2)    
COMMIT                           
CALL       PGM(*LIBL/CCTEST2)    
ROLLBACK                         
ENDCMTCTL                        
RETURN                           
ENDPGM    

Here's the code for the RPG program CCTEST2:

FCCTESTPF  UF A E           K DISK    COMMIT          
                                                      
DCOUNTER          S              5I 0                 
                                                      
C                   DO        7             COUNTER   
C                   EVAL      CCFLD1 = COUNTER        
C                   EVAL      CCFLD2 = 'TESTING CC'  
C                   WRITE     FCCTESTPF               
C                   ENDDO                             
                                                      
C                   EVAL      *INLR = *ON             
C                   RETURN                                                   

The CL program establishes a commitment definition with the Start Commitment Control (STRCMTCTL) command. The most important parameter on this command is the lock level. I found *CHG to be the recommended value. It specifies if a record is changed, added, or deleted, that record remains locked until the transaction is committed or rolled back. Records that are accessed for update operations but are released without being changed are unlocked. This parameter value prevents unnecessary record locking. Next, the CL calls program CCTEST2, which opens our physical file, CCTESTPF, under commitment control and writes seven records to it. In RPG, a file is put under commitment control by specifying the COMMIT keyword on the F-spec. To examine the results first hand, and to see how things work step by step, I suggest running the CL program CCTEST1 in debug mode, using the following command (make sure you compile your program to allow debug):

STRDBG PGM(*LIBL/CCTEST1) UPDPROD(*YES) OPMSRC(*YES)

After starting debug, stop on the COMMIT statement just after the first call to CCTEST2. Sign on to another session and query the CCTESTPF file. You should see the seven records even before they're committed; this is known as uncommitted data. Go back to the session under commitment control and, before hitting the Commit statement, end the debug session with F3 and sign off. Query the file again on the other session, and the records are gone! Start another session, and again put CCTEST1 into debug, but this time stop at the ROLLBACK statement just after the second call to CCTEST2. Query the file on the other session, and you should see 14 records. Continue with your debug session and process the ROLLBACK. Query the file again, and notice that the last seven records added have been removed. That's because the first seven written with the first call to CCTEST2 were committed with the COMMIT command in the CL program; however, the seven records that were written during the second call to CCTEST2 have not yet been committed and therefore were removed with the ROLLBACK command.

Are you starting to see the beauty of commitment control? This is great, but what happens when the application or the iSeries abends? Well, that's where the real beauty of commitment control comes in. When an abnormal termination of the program or the machine occurs, the operating system will perform an implicit rollback of the uncommitted changes for you. How cool is that? Once you see how this works, there is no going back to the old Russian Roulette way of "how often does my application abend, anyway?" With commitment control, you don't have to worry about it. It's all or nothing. You can go to meetings with users and managers and make promises you can actually keep! What a novel concept. The example programs in this article were kept very simple in order to concentrate on the concept of commitment control. In the real world, however, commitment control environments can become quite complex and daunting, but if you start small and get comfortable with it, you can take on more complex scenarios. I hope you decide to commit it and forget it!

Randy Adamski is a senior programmer/analyst for Vision Solutions, Inc. E-mail: radamski@visionsolutions.com