RPGPGM.COM four years old

Wow! I find it hard to believe that I have been writing this blog for four years.

We have been fortunate to share this journey in an exciting time for the IBM i operating system. In all my years of working with this operating system, and is predecessors, there seems to be so much more functionality added every year than there was in the past. During these four years I have written almost 400 posts and pages, trying to share my enthusiasm for IBM i and what it can do.

Using relative record number in SQL

I have written about using the Relative Record Number, RRN, in RPG and said that I could not think of a reason why I would ever need to use RRN in one of my programs. Well, I have to take that statement back as I recently found a really good reason for using it. I needed a quick way to retrieve the last record from several different "flat files", and check if it started with the characters "END:". I could do it in RPG, but what is the fun with doing that when I could do the same using SQL?

What is my definition of what a "flat file" is? See here.

I am not describe these "flat files" as it is not really relevant to what I am going to show. I will say that I had a variety of these files from several sources, the layout of the contents is different, and the maximum record lengths are different to. They all have the following in common:

Happy birthday AS400

Today is the 29th anniversary of the launch of the AS400. It was June 21, 1988, when IBM announced their latest midsize server. You can see a video recording of the announcement here.

At its launch the AS400 server and its OS400 operating were ground breaking.

IBM VP reaffirms commitment to RPG and IBM i

In my opinion it is always good when IBM publishes something about their future commitment to PowerSystems servers, IBM i operating system, and the RPG programming language. Steve Pitcher sent me a copy of a letter, from June 9 2017, by Steve Sibley, the Vice President of IBM Cognitive Systems, that hits this trifecta.

At the start of his letter Steve leaves us in no doubt IBM's regard for RPG:

SQL Views for Authorization Lists

Many companies that use IBM i secure their objects using Authorization Lists. The Authorization List is its own object type, *AUTL. If you have not used or heard of them before I found this good description of them in IBM's KnowledgeCenter.

Like a group profile, an authorization list allows you to group objects with similar security requirements and associate the group with a list of users and user authorities.

Authorization lists provide an efficient way to manage the authority to similar objects on the system and aid in the recovery of security information.

I am not going to discuss in this post how to create Authorization Lists, how to add objects to the lists, or anything similar. I am just going to describe two new SQL Views that were added in the latest IBM i Technical Refreshes, 7.3 TR2 and 7.2 TR6:

Using SQL to load data into a test instance

Today's post is written by David Taylor.

From time to time, we need to load data from a production system to a development system. To make the process more complex, the data often resides in more than one file. Using SQL, we finally found a way to load the data into multiple files from the same cursor. We include in the SELECT clause any fields from any of the files in the data set to refine the cursor. Once we have the right data set, we can use the final version for the multiple file loads. Keep in mind, we need the data from all three files for the process test to work correctly. We need not just any set of records; we need the data that matches the relationships and limits defined in the cursor. In the samples each file ends with a letter. That same letter is the first character for the field names. For example, FILEA has fields like AKEYFLD1, AFIELD1, and so forth.

Repositioning file pointer after EOF in CL

I am always grateful for the feedback I receive from you, the readers of this web site. There are many times you mention ways of doing things that either did not think of or was unaware of. Today is a good example. I have Andrew Norton to thank for bringing to my attention a command that is a better solution for the scenario I described in the previous version of this post.

Those of us who have programmed in CL have found that when read a file when the end of file has been encountered I thought there was no way I could use the file again in the same CL program. In RPG I could use the Set Lower Limits operation, SETLL operation to reposition the file pointer. But in CL I was stuck.

Display files in CL

I could give this a subtitle: "How to put a bad date in a date field", but this is not the reason of this post, just an accidental discovery. Having previously written about how to use database files in a CL program I have to mention using display files in CL.

You cannot use CL for anything as complex as a subfile. But you can for what I call "report submission screens", you know the type: a screen is presented to the user, they enter the selection criteria they desire and press Enter, and a program is submitted to batch to generate either a paper report or an email attachment. My example display file has two record formats, and is very simple:

Using OPNQRYF for file I/O in CL

In my last post about the various way I could perform file I/O in CL code I deliberately omitted mention of the Open Query file command, OPNQRYF, as I wanted to cover it in its own post. I am sure everyone who has been programming on the IBM i or its predecessors have encountered and used this command.

I used it too, but not anymore. In the days before embedded SQL become efficient and fast within RPG programs I would often use OPNQRYF. This command allowed me to select fields and records, sort the data in a different order to the files' access paths, etc. thereby reducing the amount of checking and processing the following RPG program would need to perform. But it was always slow as it used the Classic Query Engine, CQE, to access the IBM i's database. In version 7.2 OPNQRYF was moved from using CQE to the faster and more efficient, SQL Query Engine, SQE. While this did improve its speed and performance it is still more efficient, and in my opinion easier and clearer, to do the equivalent using SQL embedded in RPG.

I am going to be giving examples of how to use OPNQRYF to do some of the things I talked about in yesterday's post.

Read, write, and update a file in CL

Someone messaged me telling me that there were not, in their opinion, many good articles about CL programming, and asked if I would write some. Personally I think the majority of IBM i developers under utilize this language, keeping it just as a simple control language. I use CL programs and procedures a lot, and wrote about my thoughts on the need to modernize your CL in a post last year.

Where to start? Coincidentally I received an email from another asking about reading, writing, and updating a file just using CL. Let's start with data base access. In this post I will use the RPG file operation's name and then describe how to do the equivalent in CL.

Determining the screen size

Most of us are aware that TN5250 sessions (displays) on the IBM i can be configured in two sizes:

• 24 x 80 commonly known as *DS3
• 27 x 132 commonly known as *DS4

Most of the time IBM i operating system handles this for us, only displaying "wide" screens when my session is *DS4 and the information to be displayed is wider than 80 columns.

My favorite CL shortcuts

If I am going to write and give examples of programming in CL I need to start by explaining some of the CL language shortcuts I use. Perhaps "shortcuts" are not the right word, maybe "shorthand". Oh well, I am sure you will work out what I mean as you read this.

I use these shortcuts extensively in my CL code as for a couple of reasons:

1. I think it is easier for someone else to understand the code I have written.
2. It is just quicker to write, the faster I can write code the more I can get done.

I am going to start with my favorite three.