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.

Defining LDA in free format RPG

It has been pointed out that this web site lacks a good description of how to retrieve the data from the Local Data Area, LDA, in the post about defining variables in free format RPG. I agree, which is why I have written this about retrieving and updating the LDA.

I am going assume that we all know what the LDA is. If you do not then you can read IBM's definition of what it is.

There are two common ways to bring in and update the data from the LDA in fixed format RPG.

Why is my RPG program not writing to the file?

I have been asked this question a lot recently:

When I am in debug my RPG program does not write to the file. What is wrong?

I have decided to write this post so I can, in the future, refer people here.

Let me give a very simple example RPG program so I can explain what is going on.

Using SQL for message queue data

One of the new enhancements that came with the latest TRS for IBM i 7.3 and 7.2 is a new SQL View: MESSAGE_QUEUE_INFO. This view returns one line for each message in a message queue, in a similar manner to what is returned by the Display Messages command, DSPMSG.

This will allow me to have a way to quickly, and easily, search a message queue for a message. I must have *USE authority to the message queue, and *EXECUTE authority to the library it is in. One thing to remember is that message queues do get full, and when they do messages from them will be deleted. Also certain message queues are cleared when the IBM i partition is IPL-ed. If you are looking for a message that may have been issued sometime ago you might have better luck searching the History log via SQL.

Viewing object authority using a SQL view

One of the new SQL Views added with the latest Technical Refreshes for IBM i, 7.3 TR2 and 7.2 TR6, is going to be very useful to me. Once a year, at audit time, I am asked to produce a list of all objects within the production library list that are not adequately secured. With the new Object Privileges SQL view I can generate all of the information I need in minutes, using a few SQL statements. This is going to save me a lot of time and effort.

The Object Privileges view, OBJECT_PRIVILEGES, like many of the new Views is to be found in the library QSYS2. The information it contains is the same as the information I can generate using in the Display Object Authority command, DSPOBJAUT. Rather than list all of the columns I will list them when I use them in various SQL statements. If you want to see a full list of all the columns visit the link I have provided a link to the IBM documentation for this View at the bottom of this post.

Second parameter added to SQL's LTRIM and RTRIM

As part of the goodies that came with the recent Technical Refreshes, TR, was the addition of a second parameter to SQL's Left trim, LTRIM, and Right trim, RTRIM, built in functions. The second parameter has the same functionality as it does in CL and RPG's trim built in functions, when used trimming particular character from either the left or right of the string.

The syntax of these SQL functions is remarkably similar to that of the CL built in function: