SQL in RPG: Updating tables with and without journaling

When you compile a SQLRPGLE program, the default Commitment Control option is *CHG. This indicates that tables you update will be locked until the changes are committed or rolled back. This is all well and good, but you do need to have journaling switched on for the tables for it to work.

The compiler doesn’t check this so, by default, a program to update an unjournaled table will compile and can be executed. It just won’t make any updates and you will need to dig through the job log to track this down.

The Right Way

What you should do is journal everything and take full advantage of commitment control.

In order to do this, you would need to create a journal receiver and a journal…

CRTJRNRCV JRNRCV(MYLIB/MYRECV) TEXT('My Journal Receiver')
CRTJRN JRN(MYLIB/MYJRN) JRNRCV(MYLIB/MYRECV) TEXT('My Journal')

And start journaling your files…
STRJRNPF FILE(FILEA, FILEB, FILEC, …) JRN(MYLIB/MYJRN) IMAGES(*BOTH) OMTJRNE(*OPNCLO)

When you want to stop journaling a file, you can do this:
ENDJRNPF FILE(*ALL | FILEA, FILEB, FILEC, …) JRN(MYJRN)

Bear in mind that the journal receivers can get pretty big pretty quickly so you will need to talk to your operations folks about disconnecting and purging these on a regular basis.

The Other Way

Of course, you may well be working with a third party application which is not taking advantage of commitment control and, for which, you can’t justify implementing a whole set of journals. This leaves you needing to turn off commitment control for your SQLRPGLE program. There are two ways of doing this:

What you can do, every time you come to compile this program, is ensure that you compile it with COMMIT(*NONE).

This approach works but it does rely on you, and every developer that follows you, remembering to change the COMMIT parameter. To me, this is a guarantee that sooner or later someone will forget and a trivial change will suddenly break a previously reliable program.

It is much better to explicitly turn off commitment control in the program itself, ideally in the header spec. Unfortunately, I’m on release 6.1 and this doesn’t include a compiler option for commitment control in the h-spec, so I am left with the following workaround.

Put this line of code at the start of you program:
exec sql Set Option Commit = *NONE;

Commitment control is now switched off and your SQLRPGLE program will happily update your horrible, unjournaled, third-party table.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s