• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Looking for Commitment, Part 2

    March 18, 2009 Paul Tuohy

    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

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    UCG Technologies

    CYBER-ATTACKS ON THE RISE. PROTECT WITH THE TRIPLE PLAY.

    COVID-19 has not only caused a global pandemic, but has sparked a “cyber pandemic” as well.

    “Cybersecurity experts predict that in 2021, there will be a cyber-attack incident every 11 seconds. This is nearly twice what it was in 2019 (every 19 seconds), and four times the rate five years ago (every 40 seconds in 2016). It is expected that cybercrime will cost the global economy $6.1 trillion annually, making it the third-largest economy in the world, right behind those of the United States and China.”1

    Protecting an organization’s data is not a single-faceted approach, and companies need to do everything they can to both proactively prevent an attempted attack and reactively respond to a successful attack.

    UCG Technologies’ VAULT400 subscription defends IBM i and Intel systems against cyber-attacks through comprehensive protection with the Triple Play Protection – Cloud Backup, DRaaS, & Enterprise Cybersecurity Training.

    Cyber-attacks become more sophisticated every day. The dramatic rise of the remote workforce has accelerated this trend as cyber criminals aggressively target company employees with online social engineering attacks. It is crucial that employees have proper training on what NOT to click on. Cyber threats and social engineering are constantly evolving and UCG’s Enterprise Cybersecurity Training (powered by KnowBe4) is designed to educate employees on the current cutting-edge cyber-attacks and how to reduce and eliminate them.

    A company is only as strong as its weakest link and prevention is just part of the story. Organizations need to have a quick response and actionable plan to implement should their data become compromised. This is the role of cloud backup and disaster-recovery-as-a-service (DRaaS).

    Data is a company’s most valuable asset. UCG’s VAULT400 Cloud Backup provides 256-bit encrypted backups to two (2) remote locations for safe retrieval should a cyber-attack occur. This is a necessary component of any protection strategy. Whether a single click on a malicious link brings down the Windows environment or an infected SQL server feeds the IBM i, once the data is compromised, there is no going back unless you have your data readily available.

    Recovery is not a trivial task, especially when you factor in the time sensitive nature of restoring from an active attack. This leads to the third play of the Triple Play Protection – DRaaS.  Companies have myriad concerns once an attack is realized and a managed service disaster recovery allows employees to keep focus on running the business in a crisis state.

    The combination of training employees with secure backup and disaster recovery offers companies the best chance at avoiding financial disruption in an age of stronger, more frequent cyber-attacks.

    Reach out to UCG Technologies to discuss your company’s security needs and develop a data protection plan that fits you best.

    ucgtechnologies.com/triple-play

     800.211.8798 | info@ucgtechnologies.com

     

    1. https://theconversation.com/cyberattacks-are-on-the-rise-amid-work-from-home-how-to-protect-your-business-151268

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    New Generation Software:  Watch a demo, then test drive NGS-IQ
    Northeast User Groups Conference:  19th Annual Conference, April 6 - 8, Framingham, MA
    COMMON:  Join us at the 2009 annual meeting and expo, April 26-30, Reno, Nevada

    IT Jungle Store Top Book Picks

    Easy Steps to Internet Programming for AS/400, iSeries, and System i: List Price, $49.95
    The iSeries Express Web Implementer's Guide: List Price, $49.95
    Getting Started with PHP for i5/OS: List Price, $59.95
    The System i RPG & RPG IV Tutorial and Lab Exercises: List Price, $59.95
    The System i Pocket RPG & RPG IV Guide: List Price, $69.95
    The iSeries Pocket Database Guide: List Price, $59.00
    The iSeries Pocket SQL Guide: List Price, $59.00
    The iSeries Pocket Query Guide: List Price, $49.00
    The iSeries Pocket WebFacing Primer: List Price, $39.00
    Migrating to WebSphere Express for iSeries: List Price, $49.00
    Getting Started With WebSphere Development Studio Client for iSeries: List Price, $89.00
    Getting Started with WebSphere Express for iSeries: List Price, $49.00
    Can the AS/400 Survive IBM?: List Price, $49.00
    Chip Wars: List Price, $29.95

    Mid-Market Companies Want to Be Green, Too Wall Street Makes IBM, Sun Strange Bedfellows?

    Leave a Reply Cancel reply

Volume 9, Number 10 -- March 18, 2009
THIS ISSUE SPONSORED BY:

Help/Systems
COMMON
System i Developer

Table of Contents

  • Looking for Commitment, Part 2
  • Treasury of New DB2 6.1 (V6R1) Features, Part 3: Client Special Registers
  • Admin Alert: Changing your SMTP Server is Easy-ish

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • Why Open Source Is Critical for Digital Transformation
  • mrc Refreshes IBM i Low-Code Dev Tool
  • Unit Testing Automation Hits Shift Left Instead of Ctrl-Alt-Delete Cash
  • Four Hundred Monitor, March 3
  • IBM i PTF Guide, Volume 23, Number 9
  • Doing The Texas Two Step From Power9 To Power10
  • PHP’s Legacy Problem
  • Guru: For IBM i Newcomers, An Access Client Solutions Primer
  • IBM i 7.1 Extended Out To 2024 And Up To The IBM Cloud
  • Some Practical Advice On That HMC-Power9 Impedance Mismatch

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2021 IT Jungle

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.