Midrange Programmer--Back to Basics: Get That Code Out of Your RPG

As long as RPG has been around, in all its forms, there is still one thing that many of us haven't learned, and that is to stop making our RPG work so hard. Or maybe we do understand this, but because of the fast-paced nature of our business we take the easy way out. Whatever the case, I want to really get back to basics in this article and discuss how to improve applications as well as give RPG a little break by removing the record selection logic and placing it where it should be.

Take a look at the RPG code RECSELECT1. This is a pretty simple example, but I want to use it to prove a point. The program accepts an item number as input and then reads through a file to count how times it finds that record. Notice anything wrong with this logic? Yes, the RPG program is working too hard. It reads every record in the file and checks to see of the item in the file matches the item passed as a parameter. One way to ease the workload of your RPG is to reduce the number of records that are read and also to remove the code that checks whether the item read in the file matches the item passed in as a parameter. RECSELECT2 shows the more leisurely RPG code. Now, instead of reading through the entire file, it uses the item passed in as a parameter as the key to read the file. So the only records read from the file are those that match the item passed in as a parameter. The program simply has to count the number of records read, and that tells you how many of the passed-in items exist in the file. I know, I know, you don't need RPG to find out how many of a certain item exists in a file. You could use the following SQL statement to do the same thing:

The point is that you can simplify your RPG code by removing the record selection from the RPG and accomplishing it by using some other method. So what other method? The answer is that it depends. In the above example, you would probably run the SQL statement, as opposed to writing an RPG program, to count items, but given that, and allowing me to come back to SQL later, let's take a closer look. What valid reasons do I have to tell you to remove record selection from RPG? I talk about RPG working too hard, but what do I mean by that? If you compile and run the two programs listed above, you will notice that RECSELECT2 runs a whole lot faster than RECSELECT1. This probably makes sense, since the program reads fewer records, and in this case that's exactly the advantage. As the code gets more complex, however, removing the record selection logic from your code means so much more.

Take a look at RECSELECT3. This program is not much more complicated, but it will help make another point: that you can increase the task's performance by removing the code from the RPG, even if you read the same number of records. That's a huge point. In the first example, it was easy to understand that RECSELECT2 would run faster than RECSELECT1, because you simply cut down the number of records that were read. Removing the check in the RPG also helped, but the big difference was the number of records. The primary issue in shops today is that there is too much record selection code embedded in our RPG, and this leads to bugs, poor performance, and nasty looking code. You can fix all three problems by simply removing the logic from RPG and placing it somewhere else. This performance gain will be realized even if you read the same number of records in one file, or in multiple files.

Placing record selection, especially complex record selection, inside RPG code is the worst thing you can do to your applications. It's like knowing nothing about law, having a best friend as an attorney, but still choosing to represent yourself in a murder trial. It would be wiser to walk 10 miles to work even though you are late for a meeting, have bad feet, and have a perfectly working vehicle in your garage than it would be to put all your record selection inside your RPG. That's because there is a wonderful database engine integrated with OS/400 just waiting to process your data. It's called DB2. Not only is it waiting to process your data, but it's also waiting to process it really well, much better than your RPG statements could ever hope for. DB2 provides multiple tools to accomplish data crunching, and it also offers tools to optimize this data crunching. Most of the time, you, the programmer or user, do not see when these optimizing tools are in effect; they just go to work for you. However, if you are not satisfied with something, there are ways to check under the hood, so to speak, and see what's going on as well as receive recommendations from the engine. Can your RPG operations do that?

Back to RECSELECT3. You'll notice that the program is reading all the records in the file and adding one to the HITS variable when either the item passed in as a parameter matches the item in the record or if a certain field, DSPPRC, is equal to Y. This program will certainly provide you with the correct count, but it will do so without taking advantage of the DB2 engine. We could make this program much more efficient, perform less work, and look better by letting DB2 take care of the record selection.

One way to accomplish this is by using a logical file. A logical file permits selection criteria and keys so you could create a logical that only contains records where DSPPRC is equal to Y and that is keyed by item number, but that doesn't really help us here, does it? What about the item that is passed in as a parameter? You need to know when that item exists in the file or when DSPPRC is equal to Y. When you create your logical file, you don't yet know which item the user is going to need. Logical files or SQL indexes (synonymous terms in iSeries lingo) are better suited for static data selection. When you know ahead of time the data extraction rules, logical files are the way to go. Once they are built, they are very efficient and let you use your RPG code to publish the data to a report or a screen. However, in my example, it looks like we need something more flexible and dynamic. No problem, that's where Open Query File, embedded SQL, or query management queries come into play. These tools let you dynamically select data at program runtime and feed that data into your RPG program. The big advantage is that these tools use the DB2 engine to do the work and therefore can obtain the data much faster than an RPG program. DB2 knows how to best put the data into memory, which access path to use, or which access path to create when one is not available. It also allows you to code cleaner or more maintainable RPG programs, which could lead to fewer bugs and a happier user community. That's because DB2 contains an optimizer that automatically analyzes your system to determine the best way to get the data to you. This goes on without your worry. If you need to worry because you are not pleased with the performance, or you just want to get an idea of what's going on, there are ways to analyze what DB2 is doing and to obtain recommendations that will help with performance. So, in the example, even though you need to read all the records in the file, doing so using one of the DB2 tools will let you do so much more efficiently. And it's usually much easier to code a query or embedded SQL statement than to use RPG C-specs. I won't say the same thing for Open Query File, but I'll touch on that in a future article.

The days of monolithic RPG programs should be behind us. Not just because of what I've written about in this article but also because of enhancements to the RPG language as a whole and the shift in programming philosophy. However, I still see that RPG programs, and therefore OS/400 systems and applications, are working too hard. Removing record selection from RPG and using one of the excellent DB2 tools will go along way toward improving performance and program readability, as well as cut down the number of bugs in your applications. I've provided the "best practice" points in this article. In coming articles I will touch on some of the tools and get deeper into why and when you would use each tool. I will also discuss the DB2 optimizer and explain how to get information from it. In the meantime, for more information on embedded SQL, check out "Dynamic Selection with Embedded SQL."