Best Practice to Add Commitment Control to a Service Program

[Scholli2000]

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;
else;
  // do nothing - caller decides if to commit or rollback
endif;

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;
else;
  exec sql rollback;
endif;

Do you think this is generally a good idea?
Or is there a better practice?

Thanks

[B.Hauser]

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.

Birgitta

[Scholli2000]

Thank you, Birgitta.

That seems like the better approach.
But doing so, I must add commitment control in each of the programs that use my service program.
My hope was to only change my service program. Or did I miss something?

[TedHolt]

When trying to figure out a best practice, I start with the ideal and modify it to address the practical.

Concerning commitment control, I agree with Birgitta – I think the ideal is for the callers to issue the commits and rollbacks. If you go that route, then as you say, you have to modify all the existing callers to COMMIT the transactions, and that may be impractical. At least it could be a lot of work, because you might have to dig around to figure out where to put the COMMITs in the calling programs. Do you need to commit after each call to a subprocedure? Can you wait until program exit to issue a single COMMIT? Answering such questions may eat up some time.

How many calling programs and calls to subprocedures are we talking about?

Here’s another approach that may be more practical and eliminate the analysis. A while back I worked on a project where we could make the subprocedures in the service programs commit or not. We added a commit parameter to each subprocedure. The default value was not to commit in the subprocedure. If the parm was passed and it had a value of *ON, then the subprocedure would go ahead and commit the transaction.

In your situation, what I would try to do is this:

• I’d add the optional commit parameter to each subprocedure. Default is not to commit.
• I would add the commit parm to all the existing callers, telling the subprocedures to commit.

Yes, it’s a bit of work to modify the existing callers, but you get the option to commit in the subprocedures if you want to and you’re more assured that the subprocedures will continue to work as they have been working.

[TedHolt]

I just reread my post. This may be a little more practical:

o Add the optional commit parm to each subprocedure. Default is to commit.
o Add the commit parm to the existing callers, telling the subprocedures to commit.
o Change the optional commit parm in each subprocedure to NOT commit by default.

[Scholli2000]

Thanks a lot. Your solution sounds very much like my approach in the original posting, doesn't it?

By declaring the CommitToBePerformed parameter in the subprocedures with options(*nopass) and a default of commiting (as it has been so far with the old callers), I won't have to add the commit parm to all old callers.

Good to hear that someone did something similar before and it seems to have worked.

[TedHolt]

The only difference in my approach and yours, Scholli, is that you commit by default and I don't commit by default.

I think it's better not to commit by default, as it puts the responsibility on the callers to handle commit and rollback, which I think is where it should be.

[Scholli2000]

I fully agree with you. The problem is that we are a "always put commit=*none in every program" shop, unfortunately.
Thanks again.