|
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.
RELATED STORIES
Looking for Commitment, Part 1
Looking for Commitment, Part 2
Post this story to del.icio.us
Post this story to Digg
Post this story to Slashdot
