Looking for Commitment, Part 2

In this article I will take a look at the basic rules and coding requirements for using commitment control within RPG programs.

A Database

Figure 1 – SQL used to create a sample schema and tables.

Note: The code show above is available for download here.

Figure 1 shows the SQL used to create a schema named COMMIT, which contains two related tables, called HEADER and DETAILS. The main points to note are as follows (refer to the corresponding letters in Figure 1):

A. Creating the schema COMMIT results in a library named COMMIT that contains a journal named QSQJRN and a corresponding journal receiver named QSQJRN0001. All tables created in the schema will be automatically attached to the journal QSQJRN.

B. The table HEADER consists of a key, a text field, and a numeric field containing the number of corresponding DETAIL rows for the key. HEADER has a primary key constraint based on the key field.

C. The table DETAILS consists of a key (which corresponds to the key on the header table), a sequence number, and a text field. DETAILS has a primary key constraint based on the key field and the sequence number.

D. An index is created for each table (HEADER1 and DETAILS1). The index keys correspond to the definition of the primary key constraint for each table. RPG programs will use these indexes to access the rows in the tables.

E. A foreign key constraint is defined between the DETAILS and HEADER tables. The constraint contains a cascade delete rule which means that when a row is deleted from the HEADER table all corresponding rows (based on the key fields) are automatically deleted from the DETAILS table. A cascade delete is only possible when the files are attached to the same journal.

Since this is a schema the HEADER and DETAILS tables are automatically attached to the journal QSQJRN. If you intend to implement commitment control on an existing database, you must ensure that each table (or physical file) is attached to the same journal.

Having the journal in the same schema as the tables is not a requirement for commitment control; the only requirement for commitment control is that the tables are attached to the same journal.

A Program

Figure 2 – A test program to demonstrate commitment control.

Note: The code show above is available for download here.

Figure 2 shows the source of an RPG program used to populate the database and test the commitment control process. This program prompts for a key, writes one record to the HEADER table and three records to the DETAILS table for the key value entered. Every time a row is added to the DETAILS table, the Number of Rows on the corresponding HEADER row is incremented. The program then prompts to commit, roll back, or ignore the transaction. The main points to note are as follows (refer to the corresponding numbers in Figure 2):

A. The program uses the indexes HEADER1 and DETAILS1 to access the database. Both tables specify the COMMIT keyword to indicate that commitment control may be used on the tables. The SomeTimes indicator controls whether (*ON) or not (*OFF) commitment control is active when the files are opened.

B. The indicator SomeTimes is passed as a parameter when the program is called.

C. The program requests the entry of a key value.

D. If a key value is entered, the program adds a record to the header table and three corresponding records to the details table.

E. The Number of Rows on the HEADER rows is incremented for each row added to DETAILS.

F. The program prompts for an entry to determine what should be done with the rows just added to the tables.

G. If the entry was “c” for commit, then the rows are committed. Note that the SomeTimes indicator also conditions the commit operation. Issuing a commit or roll back operation when commitment control is not active results in run-time error RNQ0802:

COMMIT or ROLBK operation attempted when commitment control
was not active (C G D F).

H. If the entry was “r” for roll back, then the rows just written are removed from the tables. As with the commit operation, the roll back operation is also conditioned by the SomeTimes indicator.

I. No action takes place if the entry was “i” for “ignore.” Remember that tables opened while commitment control is enabled must be attached to the same journal. If either of the tables is not attached to a journal, or if the tables are not attached to the same journal, the system will issue the following message:

Member *N not journaled to journal *N.

A Normal Call

Let’s start by calling the program without commitment control (the indicator parameter must be in single quotes):

CALL PGM(COMMITRPG1) PARM('0') 

When prompted, enter a value of ‘aa’ for the key. It is irrelevant what value you enter for the commitment option because the program is not performing commitment control. By using the run query command to look at the contents of the two tables, you will see that they have the values shown in Figure 3. A single row was added to the HEADER table and three corresponding rows were added to the DETAILS table.

Figure 3 – Contents of tables after call with no commitment control.

Note: The code show above is available for download here.

A Committed Call

Now let’s try to call the program with commitment control enabled by entering the command:

CALL PGM(COMMITRPG1) PARM('1')

Disaster! The program fails with the message:

Commitment definition *N not valid for open of DETAILS1

The second level message text for the error provides more information, but the issue is that you forgot to say you were using commitment control in the job. It is not enough to simply have the COMMIT keyword on the file specifications in the RPG program–you must also specify that you are using commitment control in the job. Enter the Start Commitment Control (STRCMTCTL) command as follows:

STRCMTCTL LCKLVL(*CHG) CMTSCOPE(*JOB) 
          TEXT('Test Commitment Control')

The command specifies a lock level of *CHG and a commit scope of *JOB. Note that *JOB is not the default value for the commit scope parameter; the default value is *ACTGRP for activation group. We will look more closely at the lock level and commit scope parameters (along with the rest of the STRCMTCTL parameters) in my next article.

Putting Commitment Control to Work

Now let’s look at your three main options when using commitment control (specify a parameter value of ‘1’ on all calls to the program).

Call the program and enter a value of “bb” for the key and a value of “c” for the commitment option. The tables should now contain the values shown in Figure 4. An additional row has been added to the HEADER table and three corresponding rows were added to the DETAILS table.

Figure 4 – Contents of tables after call with commitment control.

Note: The code show above is available for download here.

Call the program and enter a value of “cc” for the key and a value of “r” (roll back) for the commitment option. When you look at the contents of the two tables you will see that they remain unchanged, the new rows have not been added and the contents are as they were in Figure 4. The ROLBK operation in the program removed the four rows that had been added to the two tables.

Call the program and enter a value of “dd” for the key and a value of “i” (ignore) for the commitment option. What do you expect to find on the tables? Will the new rows appear in the table or won’t they? At first glance, it appears that the new rows are on the tables, as shown in Figure 5. Even if you signed into another job and viewed the contents of the tables, the new rows would appear to be there.

Figure 5 – Contents of tables after call with commitment control; no commit or rollback issued.

Note: The code show above is available for download here.

But this is not the full story. Although the new rows appear in the tables, they are only available to other programs if the tables are open for input only. Use the Display Record Locks (DSPRCDLCK) command to see that the three newly inserted rows are all locked, as shown in Figure 6. These rows are not available for update to any other programs. Although the rows have been physically added to the tables, they have not yet had a commit or a roll back instruction issued to them.

Figure 6 – Rows locked while awaiting a commit or rollback.

Note: The code show above is available for download here.

Enter the command ROLLBACK. This means that the pending changes have been removed and will not appear if you view the contents of the tables. Alternatively, you could have entered the command COMMIT to have the pending changes applied. Isn’t it nice to know that there are commands for COMMIT and ROLLBACK?

Ending Commitment Control

At any stage in a job you can end commitment control by issuing the End Commitment Control (ENDCMTCTL) command. There are no parameters for this command.

If there are pending changes when you end commitment control you will receive the message:

ENDCMTCTL requested with changes pending.
(RB C CM)

Entering a value of RB (the default reply) indicates that a roll back should be performed. Entering a value of CM indicates that a commit should be performed. Entering a value of C indicates that the End Commitment Control command should be cancelled.

If there are pending changes when you end a job, the implicit roll back is performed before the job ends.

All For Now. . .

In the next article we will take a closer look at how commitment control works by looking at the journal entries for commitment control. We will also take a closer look at the LCKLVL and CMTSCOPE parameters on the STRCMTCTL command.

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.

RELATED STORIES

Looking for Commitment, Part 1

Looking for Commitment, Part 3

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot