Four Hundred Guru--Looking for Commitment, Part 3

Newsletters	Subscriptions	Forums	Safari	Store	Career	Media Kit	About Us	Contact	Search	Home

Volume 9, Number 11 -- March 25, 2009

Looking for Commitment, Part 3

Published: March 25, 2009

by Paul Tuohy

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

Commitment Control and Journals

Commitment control is dependant upon the use of a journal. A journal is used in conjunction with commitment control as follows:

When you start commitment control (using the STRCMTCTL command), a commitment boundary entry is placed in the journal.
As a program inserts, updates, and deletes rows in a table, the rows are actually inserted, updated, and deleted, and the corresponding entries are made in the journal.
If the program issues a commit, then another commitment boundary entry is placed in the journal.
If the program issues a roll back, then all entries in the journal revert back to the previous commitment boundary or are removed from the tables.

Journal Entries

Each journal entry contains a sequence number, a journal code, and a journal entry type. The journal entry type is dependent on the journal code.

There are many journal codes, but for the purpose of learning about commitment control, we are only interested in the following two:

C--Commitment control operation
R--Record level operation

The journal entry types for a journal code of C are:

Type	Description
BA	Commit block in use at abnormal end
BC	Commitment control environment begun
CM	Set of record changes committed
CN	End rollback
DB	Internal entry
EC	Commitment control environment ended
LW	Logical unit of work ended
PC	Prepare commit block
RB	Set of record changes rolled back
R1	Rollback started
SB	Start save point
SC	Commit cycle started
SQ	Release save point
SU	Rollback save point

The journal entry types for a journal code of R are:

Type	Description
BR	Before image of record updated for rollback
DL	Record deleted from physical file member
DR	Record deleted for rollback
IL	Increment record limit
PT	Record added to physical file member
PX	Record added directly to physical file member
UB	Before image of record updated in physical file member
UP	After image of record updated in physical file member
UR	After image of record updated for rollback

Journal Entries for a Committed Transaction

Let's look at what happens behind the scenes when a transaction is committed.

To make it easier to decipher the journal entries generated, enter the following command to attach a new receiver to the QSQJRN journal:

CHGJRN JRN(COMMIT/QSQJRN) JRNRCV(*GEN) SEQOPT(*RESET)

The value *RESET for the SEQOPT parameter indicates that sequencing restarts at 1, as opposed to continuing from the last entry in the previous journal receiver. When you attach a new journal receiver to a journal, the last journal entry in the original journal receiver identifies the new journal receiver. The first journal entry in the new journal receiver identifies the original journal receiver.

Enter the Start Commitment Control (STRCMTCTL) command as follows:

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

Remember that *JOB is not the default value for the Commit Scope (CMTSCOPE) parameter.*ACTGRP is the default (more about this later in the article).

Call the program we used in the previous article using the command:

CALL PGM(COMMITRPG1) PARM('1')

When prompted, enter a value of "h1" for the key and a value of "c" (commit) for the commitment option.

Enter the following command to view a list of journal entries, as shown below:

DSPJRN JRN(COMMIT/QSQJRN)
Sequence  Code  Type  Object      Library     Job         Time    
       1   J     PR                           COMCONPTA   11:12:39
       2   C     BC                           COMCONPTA   11:13:52
       3   C     SC                           COMCONPTA   11:14:05
       5   R     PX   HEADER      COMMIT      COMCONPTA   11:14:05
       7   R     PX   DETAILS     COMMIT      COMCONPTA   11:14:05
       8   R     UB   HEADER      COMMIT      COMCONPTA   11:14:05
       9   R     UP   HEADER      COMMIT      COMCONPTA   11:14:05
      10   R     PX   DETAILS     COMMIT      COMCONPTA   11:14:05
      11   R     UB   HEADER      COMMIT      COMCONPTA   11:14:05
      12   R     UP   HEADER      COMMIT      COMCONPTA   11:14:05
      13   R     PX   DETAILS     COMMIT      COMCONPTA   11:14:05
      14   R     UB   HEADER      COMMIT      COMCONPTA   11:14:05
      15   R     UP   HEADER      COMMIT      COMCONPTA   11:14:05
      16   C     CM                           COMCONPTA   11:14:07

Note: When referring to the QSQJRN journal in any command (e.g., CHGJRN or DSPJRN) it is recommended that you qualify the object name with the library name. QSQJRN is a commonly used name for journals and, if you depend on the library list as opposed to a qualified name, you may find that you are inadvertently changing or displaying the wrong journal.

The main points to note about the journal entries above are as follows (refer to the corresponding numbers in the Sequence column and check the Journal Code and Journal Type in the previous tables):

2--Commitment control is started. This is not as a result of running the STRCMTCTL command. Rather, the journal entry is created when the first file is opened with commitment control enabled
3--The start of a commit cycle. This is the starting point for inserting, updating, or deleting related rows under commitment control. In other words, this entry identifies the start of a transaction
5--A row is inserted in the HEADER table
7--A row is inserted in the DETAILS table
8--The before image for the HEADER row update
9--The after image for the HEADER row update
10 to 15--The second and third DETAILS rows are added and the HEADER row is updated for each
16--A commit is issued

Here you clearly see how commitment control is implemented. A journal entry (Sequence 5) marks the beginning of a transaction, and a corresponding journal entry (Sequence 16) marks the end of a transaction. The interceding journal entries identify the transaction. Of course, the format is easy to see here because there is only one program currently accessing the database. If there were two or more programs accessing the database at the same time, there would be journal entries for each program interspersed throughout the list. How can you identify which record level journal entry relates to which commitment control entry? It is identified in the details of the record level entries. Use option 5 to view an entry, and press F10 to display the entry details.

The entry details for the before image of the HEADER row prior to the first update (Sequence 8) is shown below in Figure 1. The Commit cycle ID (Sequence 3) is the sequence number of the corresponding Commit Cycle Started journal entry. All of the record level journal entries for a transaction will have the same Commit cycle ID.

Figure 1 - Entry details for the HEADER row before image.

Since the DSPJRN command arranges the list by sequence number, it can be difficult to discern which journal entries belong to which job or program--especially when multiple programs and/or jobs are accessing the tables at the same time. In this case, you can either identify the required job in the Job parameter on the DSPJRN command or direct the output from the DSPJRN command to a database and access the output with QUERY or SQL, and sequence the journal entries by sequence number within Commit cycle ID.

Journal Entries for a Pending Transaction

Let's see what happens with the journal entries when you have a pending transaction. Call the program again and enter a value of "h2" for the key and a value of "i" (ignore) for the commitment option. As you will remember from the previous article, the new rows are physically placed in the HEADER and DETAIL tables, but are not available for update by any other job (they are available for input, however).

Issuing the DSPJRN command again will display the journal entries shown in the code below. A Commit Cycle Started entry (Sequence 17) marks the beginning of the transaction, and is followed by the record level entries for the transaction. There is no corresponding commit entry (Code C, Type CM) since the program did not issue a commit operation.

Sequence  Code  Type  Object      Library     Job         Time    
      17   C     SC                           COMCONPTA   12:51:03
      19   R     PX   HEADER      COMMIT      COMCONPTA   12:51:03
      21   R     PX   DETAILS     COMMIT      COMCONPTA   12:51:03
      22   R     UB   HEADER      COMMIT      COMCONPTA   12:51:03
      23   R     UP   HEADER      COMMIT      COMCONPTA   12:51:03
      24   R     PX   DETAILS     COMMIT      COMCONPTA   12:51:03
      25   R     UB   HEADER      COMMIT      COMCONPTA   12:51:03
      26   R     UP   HEADER      COMMIT      COMCONPTA   12:51:03
      27   R     PX   DETAILS     COMMIT      COMCONPTA   12:51:03
      28   R     UB   HEADER      COMMIT      COMCONPTA   12:51:03
      29   R     UP   HEADER      COMMIT      COMCONPTA   12:51:03

At this point, if the job ended abnormally for any reason, an automatic rollback would be performed to remove the pending entries from the tables.

Issuing a COMMIT command at the command line places a commit journal entry in the journal (at Sequence 30).

Journal Entries for Rolling Back a Transaction

Finally, let's see what happens when you issue a rollback. Call the program again and enter a value of "h3" for the key and a value of "r" (rollback) for the commitment option.

Issue the DSPJRN command to view the journal entries shown in the code below. A Commit Cycle Started entry (Sequence 31) marks the beginning of the transaction and is followed by the record level entries for the transaction (Sequence 33 to 43).

Although they have not been committed, rows on the HEADER and DETAILS tables have been physically changed. When you issue a rollback operation, the row is changed back to its original values. This is reflected in the corresponding journal entries (Sequence 44 to 53). These entries are basically the reverse of the entries in Sequence 33 to 43. For example, the entry in Sequence 44 (the before image) corresponds to the entry in Sequence 43 (after image). The entry in Sequence 45 (after image) corresponds to entry in Sequence 42 (before image). An original Insert operation will have a corresponding Delete operation, and an original Delete operation will have a corresponding Insert operation.

Sequence  Code  Type  Object      Library     Job         Time    
      31   C     SC                           COMCONPTA   13:09:01
      33   R     PX   HEADER      COMMIT      COMCONPTA   13:09:01
      35   R     PX   DETAILS     COMMIT      COMCONPTA   13:09:01
      36   R     UB   HEADER      COMMIT      COMCONPTA   13:09:01
      37   R     UP   HEADER      COMMIT      COMCONPTA   13:09:01
      38   R     PX   DETAILS     COMMIT      COMCONPTA   13:09:01
      39   R     UB   HEADER      COMMIT      COMCONPTA   13:09:01
      40   R     UP   HEADER      COMMIT      COMCONPTA   13:09:01
      41   R     PX   DETAILS     COMMIT      COMCONPTA   13:09:01
      42   R     UB   HEADER      COMMIT      COMCONPTA   13:09:01
      43   R     UP   HEADER      COMMIT      COMCONPTA   13:09:01
      44   R     BR   HEADER      COMMIT      COMCONPTA   13:09:04
      45   R     UR   HEADER      COMMIT      COMCONPTA   13:09:04
      46   R     DR   DETAILS     COMMIT      COMCONPTA   13:09:04
      47   R     BR   HEADER      COMMIT      COMCONPTA   13:09:04
      48   R     UR   HEADER      COMMIT      COMCONPTA   13:09:04
      49   R     DR   DETAILS     COMMIT      COMCONPTA   13:09:04
      50   R     BR   HEADER      COMMIT      COMCONPTA   13:09:04
      51   R     UR   HEADER      COMMIT      COMCONPTA   13:09:04
      52   R     DR   DETAILS     COMMIT      COMCONPTA   13:09:04
      53   R     DR   HEADER      COMMIT      COMCONPTA   13:09:04
      54   C     RB                           COMCONPTA   13:09:04

A rollback journal entry (Sequence 54) marks the end of the transaction.

All of the record level entries (for the original transaction and the rollback entries) have the same Commit cycle ID of 31.

Ending Commitment Control

Ending commitment control--by using the ENDCMTCTL command or ending a job normally--results in a "Commitment control environment ended" journal entry, as shown in the following code. This is the corresponding end entry for the original start entry (Sequence 2 in the first piece journal list) when commitment control was started.

Sequence  Code  Type  Object      Library     Job         Time    
      55   C     EC                           COMCONPTA   13:29:50

Locking Level and Scoping

When you start commitment control, using the STRCMTCTL command, you must specify the locking level for rows in tables opened under commitment control. Your options are:

*CHG--Every row read for update is locked. Rows that are changed, added, or deleted, remain locked until the transaction is committed or rolled back. Unchanged rows are unlocked.
*CS--Every row accessed is locked. Rows that are changed, added, or deleted, remain locked until the transaction is committed or rolled back. Unchanged rows are unlocked.
*ALL--Every row accessed is locked until the transaction is committed or rolled back.

As mentioned previously, the default value for the CMTSCOPE parameter on the STRCMTCTL command is *ACTGRP. This means that commitment control only applies to tables that are open within the activation group in which the COMMIT or ROLLBACK is issued.

Using a value of *ACTGRP means that the STRCMTCTL and ENDCMTCTL commands must be issued from within the required activation group.

The value of *JOB (as I have been using in the examples) means that commitment control applies to tables that are open within the job, regardless of activation group, in which the COMMIT or ROLLBACK is issued.

The default use of *ACTGRP provides a means of minimizing the chance of unintentionally committing or rolling back a pending transaction within a job.

All For Now. . .

There you have it. You should now have enough information to start playing with commitment control.

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.