SQL RPG and the annoying compile COMMIT *NONE thing

SQLRPGLE and COMMIT(*NONE)

Moving to SQL (from native file IO) has been a major game changer in the RPG programming world.

SQLRPGLE can be confusing to learn, horrible to use if it needs lots of compile time parameters but using Exec SQL SET OPTION something answers my prayers.

I first played with SQL back the golden days of RPG3 and RPG400 when we were all wrapped up in using Commitment Control. Journaling files and making changes to data in our RPGSQL programs before issuing a COMMIT or ROLLBACK was exciting and new.

Roll forward a few years and database access times have massively increased and the entire concept of commitment control is not something we focus on so much in the RPG programming world.

Because of this history, in modern SQLRPGLE programs, we still have to tell the program not to use commitment if we don’t want to use it. This also means you can use embedded SQL in RPG programs without journaling your files.

COMPILE TIME PARAMETER

The easiest way is to enter the create parameter of COMMIT(*NONE) thing so it turns it off after the first commit without the isolation level nonsense.

USE RPG CODE

This is my preferred technique. Adding a line of code to the program and then know that you never need to specify additional compilation parameters makes me sleep easy at night. Just specify COMMIT *NONE in the compile command or add a SET OPTION statement at the beginning of the program:

RPG3/400

C/EXEC SQL SET OPTION COMMIT = *NONE
C/END-EXEC

SQLRPGLE:

Exec SQL SET OPTION COMMIT = *NONE;

Easy Peasy, Lemon Squeezy.

Now we can get quite fancy with SQL SET OPTION and use something like this:

EXEC SQL
Set Option
Naming = *Sys,
Commit = *None,
UsrPrf = *Owner,
DynUsrPrf = *Owner,
CloSqlCsr = *EndMod;