Commitment Control in SQLRPGLE

[Vectorspace]

How does it work?

For an RPGLE program with F-specs, you can specify COMMIT selectively against individual files
Files with COMMIT will only update only when COMMIT is done
Files without COMMIT will update immediately.


For SQLRPGLE, I found this mentioned on the internet: "Also, if your program updates a file that is not journaled, you must change the command option COMMIT to *NONE, otherwise the updates will not occur"

That implies to me, that if you want to use commitment control in SQLRPGLE, all files updated by the program must be journalled and all must use commitment control.

Is that correct? Is there no way of having a mix of commit and non commit files in a single program?

[TedHolt]

Re: Commitment Control in SQLRPGLE

Is there no way of having a mix of commit and non commit files in a single program?

Yes, you can have a mix. I've done it. Use the "with nc" option on the SQL statements that should not run under commitment control.

[Vectorspace]

Re: Commitment Control in SQLRPGLE

Lighning fast!

Thanks Ted, much appreciated.

[Michael Jones]

Re: Commitment Control in SQLRPGLE

Alternative for SQLRPGLE: add EXEC SQL SET OPTION COMMIT = *NONE to the SQLRPGLE code, and then selectively use WITH CS (or other commitment control isolation level) to those SQL statements that you want to participate in a commitment control unit of work. That is my preference. SET OPTION COMMIT = *NONE sets the default behavior to not use commitment control in the program containing it.

I prefer to code it that way, because I think that commitment control units of work should be "designed" to make it easy to recover when a unit of work fails. By adding WITH CS (or other isolation level) to selective SQL statements, I think it may encourage more design thought around how a unit of work should be structured.

[TedHolt]

Re: Commitment Control in SQLRPGLE

I prefer to code it that way, because I think that commitment control units of work should be "designed" to make it easy to recover when a unit of work fails. By adding WITH CS (or other isolation level) to selective SQL statements, I think it may encourage more design thought around how a unit of work should be structured.

Sounds like a good plan to me. I did it the other way because all of the tables were running under commitment control except the one to which I was logging errors.