Creating a screen with two side-by-side subfiles

I have written about subfiles, and even a screen with two subfiles on it. When I was asked about a screen with two subfiles, side-by-side, I was intrigued how to do it.

The first problem to overcome is that if I code two subfiles, one to occupy the left side of the screen and the other the right, whichever subfile's control record I write last will overlay the other.

After using Google I could find reference to an example of using DDS windows to contain the subfiles, but the links to the article only returned a "page not found". With that hint I created the following.

Let me start by saying that this display file is a "stripped down" version. I have removed a lot of the things I put in display files so not to clutter this example. Let me start with the file level keywords and a record format I have called HEADER.

Welcome to 2019

A new year has started, and I am always excited to look back on the old year and forward to what the new year will bring.

The past few years have been an exciting time for IBM i community. Just in the life of this blog I have seen RPG undergo a radical make over, and numerous wonderful things added to Db2 for i, or SQL.

You feel the same as the most popular articles from 2018 have been:

1. Creating a XML file
2. Processing simple XML using XML-INTO
3. Using RPG data structures with SQL insert and update
4. Displaying more than one subfile at a time
5. Copying any data to and from a file in the IFS

Have you liked others? If so which ones?

RPG400 manuals found

In my previous post I stated that IBM had removed the RPG400 user's guide and reference from the KnowledgeCenter.

Having performed a good deal of searching of the internet I have been able to find copies of both of these manuals in a dark corner of the IBM website. The manuals are from 2000. That may seem a long time ago for a "current" manual, but RPG3 has not been enhanced since RPG4 was introduced 23 years ago.

I have uploaded both of these PDF files to my Dropbox account, and you can reach them by using the following links:

• RPG user's guide:  view   download
• RPG400 reference:  view   download

Having given you these links I trust you will only refer to these manuals to remind yourself of some of the anachronisms of old RPG, rather than use to develop new code.

RPG400 manuals no longer available

As I have said in previous posts if you are programming only in RPG400, or RPG3, and not learning and using the latest version of RPG you are risking your future.

I found further proof yesterday browsing IBM's KnowledgeCenter looking for the latest RPG reference manual, when I noticed that the RPG400 (RPG3) manuals are no longer offered on line.

The following manuals are not included in the IBM i Information Center...

• RPG/400 Reference...
• RPG/400 User's Guide...

Searching strings using SQL

In a previous post I had written about using regular expressions to search for data within a string, but I did not mention the easier to use LOCATE_IN_STRING SQL scalar function. I believe this was introduced as part of IBM i Technical Refresh 2 for 7.3 and TR6 for 7.2.

The LOCATE_IN_STRING has four parameters:

1. The source string to be searched (mandatory)
2. The string used to search (mandatory)
3. Starting position (optional)
4. Instance, which number occurrence to find (optional)

I created a SQL DDL table, which I called TESTFILE, which I will be using in these examples. It contains one column, called DESCRIPTION, that is 40 long character, and the table contains just one row:

Using position to in a data structure array

Update

This is a better way to sort a data structure array using an update to the SORTA operation code.

 

---

 

Any of you who have followed this blog for any amount of time are aware that I am proponent of using multiple row fetches in SQL to load data into a data structure array, which I will then use to load something like a subfile. In all the examples I have given in the past have been for files/tables that could contain many records/rows more than could be loaded into a load all subfile.

The slowest part of any program is loading data from the file/table. Using a multiple row fetch makes this many times faster by reducing the number of disk I/O to get my desired amount of data, versus getting one record at a time as I would need to do if I was using RPG to read the file/table. Every time I reload the subfile it is slow as I have to get the data from disk. This is especially true if I have a "Position to" field on my display file. If I am using a large file I have to reload the subfile every time the position to field is changed.

Using a trigger to prevent the deleting of records

The idea for this post came from a question I was asked about ways to stop a programmer from deleting records from a file using the STRSQL command. I thought this would be a good reason to use a combination of two solutions I have written about before.

My first thought was to use a validation trigger. Validation triggers must be executed before the insert, update, or delete occurs. That way I can determine if the STRSQL program was used, and if it was return an error code to the calling program.

Validation programs can be written in any language, and I have written them in both RPG and SQL. In this scenario I will be writing the trigger in SQL as, in my opinion, it is easier to write and, more importantly, easier for someone else to understand what is going on.

Find String PDM to an outfile

We have all had those requests from our superiors: "Where is insert-name-here file used?"

There are tools from third parties that will give me this information. I have used some of them, and found that they missed some objects. I always combine the information these tools provide me with a scan of the members in the source files in the production library list. I use the Find String PDM command, FNDSTRPDM, to search for the string of information I want. Most people who use this command use it to produce a printed list of the source members it finds. While that may be OK if there are just a few libraries and source members, I work in an environment where there are 25 libraries in the production library list and many of these libraries contain multiple source files. I do not want to have to copy information from all of the generated spool files into a physical file that I can share with others, that would be too much work.

Looking at the help for the FNDSTRPDM I had a eureka moment, that would give me a way to write the name of the source member to an output file.

Using CL to get records from a file for display

I have been receiving a lot of messages asking for more examples using the CL programming language. For those of you who have been asking I hope you find this useful.

A few weeks ago I received a request for help from a reader, just a beginner in the IBM i world, with an assignment he had to accomplish just using CL. He needed to write a program, or programs, that would allow the entry an employee id, and the program would retrieve information from the employee file and pass it to the display file for display. For anyone with a little RPG experience this is a very simple task, and we could all write a RPG program to do this in a few minutes. Many IBM i programmers balk to do anything like this in CL, but programming is programming, and the same steps you would perform in RPG can be duplicated in CL.

Using command to zip and unzip files

In the past I have written about zipping files in the IFS using Java jar and APIs, this week a colleague showed me a simpler way to do it using a couple of IBM i commands.

He explained that these commands had been introduced in IBM i 7.2, and can zip and unzip files in the IFS as well as files in, what I call, the native IBM i environment. With these commands the zipped/compressed files are called "archive files", therefore, IBM named them:

• CPYTOARCF:  Copy to archive file
• CPYFRMARCF:  Copy from archive file

 

Converting spool file data into data file

I was surprised that I was asked this question, but as I have been asked six times in the last week it must be something that is important for people to find an answer for. Copying data from a spool file to a data file with separate fields is not an efficient way to get data. If you are considering using this method for capturing system information, spool files, active job info, etc, then you should be using the Views, Tables, etc that IBM has been creating for us to use. You can search this web site to see if I have written about getting to the information you desire using SQL. Trust me it is easier that what I am describing below.

In this example I am going to use the Work Output Queue command, WRKOUTQ, to generate a list of spool files, and I will be writing that data to a file. If I was doing this in the real world I would using the SQL and the method described in Output queue entries information via SQL. Now I have vented my feelings on this, let me proceed.

I want to know the following information about spool files in the QEZJOBLOG output queue:

Using new SQL built in function to convert character to number

Included in the technical refreshes for IBM i 7.3 TR5 and 7.2 TR9 was a new Db2 for i built in function to convert character strings into numbers.

Why is this needed when I can just CAST the one type of a column to another?

01 SELECT COLUMN1, 02 CAST(COLUMN1 AS DECIMAL(10,2)) 03 FROM QTEMP.TESTFILE

Numbers are problematic as I can make character representations of them in many different ways. I created a few examples of character representations of numbers, and then used the above SQL statement to display them.