Guru Classic: Looking For Commitment, Part 2
July 17, 2019 Paul Tuohy
As companies look to modernize their applications, commitment control can play an integral role. This set of three articles was originally published in March 2009. The content of the articles has been updated for free form RPG.
In this article I will take a look at the basic rules and coding requirements for using commitment control within RPG programs.
A Database
This is the SQL used to create a schema named COMMIT, which contains two related tables called HEADER and DETAILS.
(A) CREATE SCHEMA "COMMIT" ;
(B) CREATE TABLE COMMIT/HEADER (
"KEY" CHAR(2) CCSID 37 NOT NULL DEFAULT '' ,
TEXT CHAR(20) CCSID 37 NOT NULL DEFAULT '' ,
NUMROWS DECIMAL(5, 0) NOT NULL DEFAULT 0 ,
CONSTRAINT COMMIT/HEADER_PRIMARY PRIMARY KEY( "KEY" ) ) ;
LABEL ON TABLE COMMIT/HEADER
IS 'Header Rows' ;
LABEL ON COLUMN COMMIT/HEADER
( "KEY" TEXT IS 'Key' ,
TEXT TEXT IS 'Header Text' ,
NUMROWS TEXT IS 'Number of Rows' ) ;
(C) CREATE TABLE COMMIT/DETAILS (
"KEY" CHAR(2) CCSID 37 NOT NULL DEFAULT ' ' ,
"SEQUENCE" DECIMAL(5, 0) NOT NULL DEFAULT 0 ,
TEXT CHAR(20) CCSID 37 NOT NULL DEFAULT ' ' ,
CONSTRAINT COMMIT/DETAIL_PRIMARY PRIMARY KEY( "KEY" , "SEQUENCE" ) ) ;
LABEL ON TABLE COMMIT/DETAILS
IS 'Detail Rows' ;
LABEL ON COLUMN COMMIT/DETAILS
( "KEY" TEXT IS 'Key' ,
"SEQUENCE" TEXT IS 'Key Seq.' ,
TEXT TEXT IS 'Detail Text' ) ;
(D) CREATE UNIQUE INDEX COMMIT/HEADER1
ON COMMIT/HEADER ( "KEY" ASC ) ;
COMMENT ON INDEX COMMIT/HEADER1
IS 'Header by Key' ;
CREATE UNIQUE INDEX COMMIT/DETAILS1
ON COMMIT/DETAILS ( "KEY" ASC , "SEQUENCE" ASC ) ;
COMMENT ON INDEX COMMIT/DETAILS1
IS 'Details by Key' ;
(E) ALTER TABLE COMMIT/DETAILS
ADD CONSTRAINT COMMIT/DETAIL_TO_HEADER
FOREIGN KEY( "KEY" )
REFERENCES COMMIT/HEADER ( "KEY" )
ON DELETE CASCADE
ON UPDATE RESTRICT ;
The main points to note are as follows. (Refer to the corresponding letters in the code above.)
- 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.
- 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.
- 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.
- 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.
- 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
This is 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.
**free
ctl-Opt option(*srcStmt: *noDebugIO)
dftActGrp(*no) actGrp('COMMITDEMO');
(A) dcl-F header1 usage(*update: *delete: *output)
keyed commit(someTimes);
dcl-F details1 usage(*update: *delete: *output)
keyed commit(someTimes);
dcl-Pr Commit1 ExtPgm('COMMITRPG2');
someTimes ind;
end-Pr;
dcl-Pi Commit1;
(B) someTimes ind;
end-Pi;
dcl-S toDo char(1);
(C) dsply 'Enter a Key Value (2 long): ' ' ' key;
(D) if (key <> *blanks);
text = 'header for ' + key;
write header;
for sequence = 1 to 3;
text = 'Detail for ' + key + ' ' + %char(sequence);
write details;
(E) chain key header1;
numRows += 1;
update header;
endFor;
endIf;
toDo = *blanks;
(F) dow (toDo <> 'c' and toDo <> 'r' and toDo <> 'i');
dsply 'c - Commit, r - Rollback, i - Ignore ' ' ' toDo;
endDo;
(G) if someTimes and (toDo = 'c');
commit;
(H) elseIf someTimes and (toDo = 'r');
rolBk;
endIf;
(I)
*inLR = *on;
The main points to note are as follows. (Refer to the corresponding numbers in the code above.)
- 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.
- The indicator SomeTimes is passed as a parameter when the program is called.
- The program requests the entry of a key value.
- If a key value is entered, the program adds a record to the header table and three corresponding records to the details table.
- The Number of Rows on the HEADER rows is incremented for each row added to DETAILS.
- The program prompts for an entry to determine what should be done with the rows just added to the tables.
- 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).
- 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.
- 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 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. Use your favorite method to look at the contents of the two tables and you will see that they have the values shown below. A single row was added to the HEADER table and three corresponding rows were added to the DETAILS table.
KEY TEXT NUMROWS aa Header for aa 3 KEY SEQUENCE TEXT aa 1 Detail for aa 1 aa 2 Detail for aa 2 aa 3 Detail for aa 3
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 below. An additional row has been added to the HEADER table and three corresponding rows were added to the DETAILS table.
KEY TEXT NUMROWS aa Header for aa 3 bb Header for bb 3 KEY SEQUENCE TEXT aa 1 Detail for aa 1 aa 2 Detail for aa 2 aa 3 Detail for aa 3 bb 1 Detail for bb 1 bb 2 Detail for bb 2 bb 3 Detail for bb 3
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 above. 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 below. Even if you signed into another job and viewed the contents of the tables, the new rows would appear to be there.
KEY TEXT NUMROWS aa Header for aa 3 bb Header for bb 3 dd Header for dd 3 KEY SEQUENCE TEXT aa 1 Detail for aa 1 aa 2 Detail for aa 2 aa 3 Detail for aa 3 bb 1 Detail for bb 1 bb 2 Detail for bb 2 bb 3 Detail for bb 3 dd 1 Detail for dd 1 dd 2 Detail for dd 2 dd 3 Detail for dd 3
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 below. 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.
Display Member Record Locks
System: xxxxxxxx
File . . . . . . . . : DETAILS Member . . . . . . . : DETAILS
Library . . . . . : COMMIT
Record Lock
Number Job User Number Status Type
7 COMCONPTA TUOHYP 115363 HELD UPDATE
8 COMCONPTA TUOHYP 115363 HELD UPDATE
9 COMCONPTA TUOHYP 115363 HELD UPDATE
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, 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.
