Regular expressions in RPG

I learned something new today. Regular expressions are something I have used quite frequently on my Linux laptop at home but I hadn’t realised that they are natively supported in the IBM i ILE environment.

Thanks to Scott Klement at iProDeveloper, now I know.

This is a useful and powerful feature and one for which I can see many real-world applications. As soon as an opportunity presents itself, I shall be returning to Scott’s article, downloading the code bundle and taking full advantage of this functionality.

Unduplicating duplicate lines with SQL and RPG

This is probably a very specific issue but I’m quite proud of the solution. And it may prove to be useful to someone else (or me at some other point) so I’m putting it here for posterity.

The issue

We receive a list of sales transactions through an EDI interface. With the implementation of a new retail management system, these transactions now need to be pointed in the direction of the new application. This has proved interesting, on occasion, as the existing system is a lot more forgiving of incomplete data than the new one.

One of these cases, we have discovered, is that for some stores we’re not receiving transaction numbers. We have dates, times, store codes, transaction line numbers – but no transaction numbers. This means that if a store manages to enter two transactions at two tills at exactly the same time, we get a whole bunch of duplicate lines in the interface extract.

This interface is a transitional one (once the roll-out is completed for all stores, it will be redundant) so I shouldn’t be spending too much time on it. Just enough to make it work.

The Solution

Putting together an SQL statement that identifies transactions with duplicate line numbers is pretty easy, as is constructing a statement to update records based on a fixed criteria. The question is, how easy is it to construct an SQL statement to quickly update records based on a dynamic criteria.

The answer is: easier than I expected.

The Implementation

The (slightly simplified) code, is pasted below without explanation but if any part of it is unclear do feel free to either leave a comment or contact me and I will be happy to clarify.

      * Program     :                                                          *
      * Description : Clean up duplicate lines                                 *
      * Written by  : Paul Pritchard                                           *
      * Date        :  2/07/2012                                               *
     D pDuplicate      DS                  QUALIFIED
     D  Date                         10A
     D  Time                          8A
     D  Store                         5S 0
     D  Register                      3S 0
     D  Transaction                   5S 0
     D  Line                          3S 0
     D SaveDate        S             10A
     D SaveTime        S              8A
     D SaveStore       S              5S 0
     D SaveReg         S              3S 0
     D SaveTxn         S              5S 0
     D SaveLine        S              3S 0
     D NewLine         S              3S 0

          // The Commitment control workaround
          exec sql Set Option Commit = *NONE;

          // Renumber any duplicate lines in the Sales File
          exsr srDuplicate;

          // And exit
          *INLR = *ON;

      * Subroutine: Renumber any duplicate lines in the Sales interface file   *

          begsr srDuplicate;

              // Initialise the work values
              exsr srReset;

              // Find the lines with duplicate numbers
              exec sql declare cSales cursor for
                      Duplication as
                      (select wrslsdate, wrslstime, wrslsistr, wrslsreg#,
                              wrslstxn#, wrslslin#, count(*)
                       from SALES
                       group by wrslsdate, wrslstime, wrslsistr, wrslsreg#,
                                wrslstxn#, wrslslin#
                       having count(*) > 1
                       order by wrslsdate, wrslstime, wrslsistr, wrslsreg#,
                                wrslstxn#, wrslslin#)
                  select wrslsdate, wrslstime, wrslsistr, wrslsreg#, wrslstxn#,
                  from SALES
                  where wrslsdate || wrslstime || char(wrslsistr) ||
                  in (select wrslsdate || wrslstime || char(wrslsistr) ||
                      char(wrslsreg#) from Duplication)
                  order by wrslsdate, wrslstime, wrslsistr, wrslsreg#,
                           wrslstxn#, wrslslin#
                  for update of wrslslin#;
              exec sql open cSales;

              // Start retrieving the data
              dou SQLCOD = 0 and SQLCOD  100;
                      exsr srRenumber;
                      exec sql
                          update SALES
                          set WRSLSLIN# = :NewLine
                          where current of cSales;

              // Close the cursor and quit
              exec sql close cSales;


      * Subroutine: Renumber                                                   *

          begsr srRenumber;

              if pDuplicate.Date  SaveDate or
                 pDuplicate.Time  SaveTime or
                 pDuplicate.Store  SaveStore or
                 pDuplicate.Register  SaveReg or
                 pDuplicate.Transaction  SaveTxn;

                  SaveDate = pDuplicate.Date;
                  SaveTime = pDuplicate.Time;
                  SaveStore = pDuplicate.Store;
                  SaveReg = pDuplicate.Register;
                  SaveTxn = pDuplicate.Transaction;

                  NewLine = 0;

              NewLine += 1;


      * Subroutine: Reset                                                      *
      *             Initialise the Save* Values                                *

          begsr srReset;

              SaveDate = *BLANKS;
              SaveTime = *BLANKS;
              SaveStore = *ZERO;
              SaveReg = *ZERO;
              SaveTxn = *ZERO;



Using SQL Fetch/Update in RPG

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                                                   
(index integer not null
 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.

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

         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;
                  Exec Sql
                     Update SqlTest
                     Set SoldTo = :NewSoldTo
                     Where current of C1;

         Exec Sql
            Close C1;



A better way to display ad-hoc messages

When debugging – and when developing quick and dirty utilities – I often resort to the DSPLY opcode. It isn’t pretty, but it puts information onto the screen without having to bother with DDS. There is a better way.

Display Long Text (QUILNGTX) API displays a pop-up window containing the string of text passed to it. You can even add a title to the window by passing a Message ID and (optionally) a qualified message file name.

This API has been around for a while, but it’s new to me and, after playing around with it a bit, I have now updated the DSPOSRLS utility to take advantage of it.

Pretty, isn’t it?

For the sake of completeness, I should probably add a message to a message file for this so that I can display a window title. For the sake of laziness, that’s something for the future.

Better coding starts with a design

David Shirey’s article, Managing RPG Program Complexity is, as the title suggests, aimed primarily at IBM i developers. The general principles, however, are true for anyone doing any sort of development and is worth a read. I particularly liked the last couple of paragraphs:

Limiting program complexity is a way of thinking, not a series of tips, and it starts with thinking through your design before you begin putting code on the screen. What are you trying to accomplish? What individual logical ideas make up your overall goal? How do you want to structure those ideas using programs and subprocedures?

… Most important, simplicity doesn’t just happen. Complexity just happens—you have to work at simplicity. But in today’s world, I can’t think of a more worthy endeavor.

If you didn’t manage to read that far, the TL;DR version is that any application consists of several descrete logical steps. Each of these discrete steps should be coded seperately and should be small enough that anyone (with the relavant language skills, obiously) should be able to glance at it and know exactly what it is doing. How you break out the various functional pieces is less important than that you do break them out.

Or, to rephrase it slightly, people who write massively monolithic programs deserve to have their fingers broken.

DSPOSRLS: Display Operating System Release

One of the surprisingly non-inuitive parts of the IBM i is that there is no easy way to identify what OS release your machine is on. Well, not if you don’t have enough authority to use DSPPTF.

A common approach is to use DSPDTAARA QSS1MRI which works, but isn’t officially supported. So this approach could stop working at any point and without notice.

The officially supported method is to use the QSZRTVPR (Retrieve Product Information) API. So I did.

DSPOSRLS is a simple command that retrieves and displays the current OS release level. I have posted the source on GitHub so feel free to browse or download it from there.

Debugging RPG Programs in Batch

If you spend any time developing RPG applications you will find that, sooner or later, you will need to debug a program that runs in batch. There are a few steps that you need to take in order to do this and, because my memory is terrible, I’m putting them here.

  • Know what job queue your job is being submitted to. Hold this job queue.
  • Submit your job.
  • Your job is now sitting on the held job queue waiting to be released. Display the job and make a note of the user name, job name and number.
  • Start a service job using STRSRVJOB entering the name, job name and number from the previous step.
  • STRDBG PGM(ProgramName). You can’t enter any breakpoints yet, so hit F12 to exit the source display
  • Release the job queue
  • The Start Serviced Job screen will be displayed asking you to press F10 to enter debug commands for the job. Press F10.
  • DSPMODSRC and enter your breakpoints.
  • Press F12 to resume and F3 to return to the Start Serviced Job screen.
  • Press Enter to start your job.

The job will now begin running and will stop, passing control back to your screen, when the first breakpoint is reached.

Application modernisation on the IBM i: Developers, Developers, Developers!

RPG, the programming language that remains very popular on the IBM i platform is often criticised for being a bit… venerable. It is certainly true that the language goes all the way back to 1959 and is one of the very few languages developed for punched card processing that is still in common use today.

However, in its current incarnation the language is a very different beast to its precursors. ILE RPG is every bit as modern, functional and flexible as its more recent rivals and, when it comes to encapsulating business rules, ILE RPG on an IBM i remains an unbeatable combination.

This is only true, however, if programmers take advantage of the features provided and, in far too many cases, they don’t.

When people talk about modernising RPG or about modernising i applications it is worth keeping in mind that the perceived datedness does not stem from either the languages or the tools. The real problem lies in the army of semi-competent, under-resourced developers scattered across isolated offices and churning out code that would have been embarrassing twenty years ago.

The backward compatibility that the IBM i retains is a hugely important part of the box’s appeal. Knowing that you can simply apply any upgrade that comes along without any downtime or breakages is a significant part of the i’s stability and provides a great deal of peace of mind to those administering it. The downside of this, however, is that it also makes it very easy for developers to settle into their comfort zones and quietly watch the rest of the industry pass them by.

These are the people that IBM needs to reach out to. IBM needs to convince these people that it is in their interest to understand how to make the most of the available languages and tools. I suspect that IBM will also need to convince IT managers that the productivity gains to be made from moving forward far outweigh the cost of training, but this is what marketing folks are for.

The IBM i is a secure, stable, reliable and powerful platform and RPG is a powerful, functional and flexible language that enables very small teams to develop and maintain very large systems. I am increasingly of the view that what undermines the platform more than anything else is the large number of developers who are unwilling to keep up with the technology.

This rant was provoked by the goto statement I found in a three year old program.

Display Program Locks: The quick way of finding who is using a program

Another week, another utility. I’m supporting an application which, among other things, includes the concept of Conflicting Programs. This works by attaching an Activity Level to each program – essentially a count of how many instances of that program are active. When someone launches a program, its Activity Level is incremented and when they exit the program its activity level is decremented.

Each program within the application also has a list of Conflicting Programs attached to it. When you try to launch a program, the activity level of each of its conflicting programs is checked and if any of these activity levels is not zero, the program will not start. Instead, you will see a message telling you which Conflicting Program is active.

It’s a simple and (usually) effective method of ensuring that end users can’t cause data discrepancies by executing tasks when they shouldn’t – you can’t start entering transactions when a month end close is in progress, for example. The problem is that the application can only tell you that a program is active – not who is using it. When someone is preventing you from starting the month close and going home by leaving a transaction entry screen open, identifying who is holding things up is something you want to be able to do as quickly as possible. This is where the Display Program Locks utility comes in.

What this utility does is scans the active jobs on the system and, for each one found, checks its call stack. If the offending program is found, the job is added to a list which is displayed on the screen. The screen handling is pretty basic, but sufficient for the environment in which I am using this utility.

Three source files are attached, for the Display File (DSPPGMLCKD.DSPF), the RPG (DSPPGMLCKR.RPGLE) and the Command Source (DSPPGMLCK.CMD). Feel free to take a look and, if you decide to download and compile it, you will be able to use the DSPPGMLCK command to find exactly who is using any given program.

As ever, comments, observations and improvements are always welcome.

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…


And start journaling your files…

When you want to stop journaling a file, you can do this:

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.