Lightly Seared On The Reality Grill

I’m not going to criticise anyone for trying to move away from native I/O on the IBM i and into SQL, but I have seen a few eye-watering attempts. So in the spirit of sharing knowledge and making life a bit easier for all of us, here is an example of reading and updating a table using SQL.

Let’s start with the table we’re updating. It’s a very simple table and simply maps the SoldTo/ShipTo combination in the distibution system to a Store number in the retail system. You can build a simplified version of this using the following:

create table
testlib.sqltest
(index integer not null
 GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
 Soldto char (5) not null,
 shipto char (4) not null,
 store numeric (5, 0) not null)

The requirement is that, based on various business rules, some SoldTo numbers in this table need to be changed. In order to keep things simple, the below program applies a simple rule of: If the store number is less than ten, change the SoldTo to 99999. (I know, I said I was keeping things simple).

The program, as it stands, is pretty much as simple as it gets, but there are couple of points that are worth noting explicitly.

Firstly, when we declare the cursor, we need to include a For Update clause to identify which field (or fields) can be updated.

Secondly, in the Update statement, the Where Current of C1 clause applies the update to all records returned by the cursor. That means that flexible mass updates with a minimum of logical mess are now at your fingertips.

     H
     D SqlRecord       DS
     D  SoldTo                        5A
     D  ShipTo                        4A
     D  Store                         5S 0
     D
     D NewSoldTo       S              5A
      /Free

         Exec Sql
            Declare C1 cursor for
               Select SoldTo, ShipTo, Store
               From SqlTest
               For Update of SoldTo;

         Exec Sql
            Open C1;

         Dou SQLSTATE>='02000';
            Exec Sql
               Fetch C1 into :SqlRecord;

            If SQLSTATE<'02000';
               If Store<=10;
                  NewSoldTo='99999';
                  Exec Sql
                     Update SqlTest
                     Set SoldTo = :NewSoldTo
                     Where current of C1;
               Endif;
            Endif;
         Enddo;

         Exec Sql
            Close C1;

         *INLR=*ON;

      /End-Free

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.