sponsored links



No announcement yet.

Best Practice to Add Commitment Control to a Service Program

  • Filter
  • Time
  • Show
Clear All
new posts

  • Best Practice to Add Commitment Control to a Service Program

    Hi all,

    I've a service program that contains some procedures that delete, insert or update records of several files using embedded SQL.
    Unfortunately, when I created the service program years ago I followed the principle "when using SQL always set option commit = *none".

    Now I want to switch to commitment control in new programs that are calling procedures of the service program without having to change the existing calling programs.

    What I have done so far:
    • First step was to set actgrp(*caller) in the service program.
    • Second to eliminate "exec sql set option commit = *none".
    • The third step was to add a parameter "CommitToBePerformed ind options(*nopass)" to all procedures setting its default to *on.

    In each procedure I would write

    if CommitToBePerformed;
      exec sql commit;
      // do nothing - caller decides if to commit or rollback
    at the appropriate positions in my code.

    Doing so -- my hope is -- I won’t have to recompile all programs that use the service program.

    A new calling program would call the service program procedures with CommitToBePerformed set to *off like this:

    id1 = srvP1WriteFile1('name':'age':*off); // *off => no commit performed by procedure
    id2 = srvP1WriteFile2(id1:'age':*off); // *off => no commit performed by procedure
    if id2 > 0;
      exec sql commit;
      exec sql rollback;
    Do you think this is generally a good idea?
    Or is there a better practice?


  • #2
    I'd set COMMIT and/or ROLBACK in the caller program, at the beginning of the transaction. Here you know wether to work under commitment control or not.
    Execut the commit (it also can be a RPG or CL commit) at the beginning of the transaction and another one at the end of the transaction. If something goes wrong (check for errors and use a monitor group) within the transaction, perform a rollback.