Wednesday, December 6, 2023

An earlier way to get Table function data using 3 part name

In August I wrote about a new way to get data from another partition using the three part name with the new REMOTE TABLE. I said that I did not know of another way to get data from a Table function using the three part name.

I received an answer from IBM Db2 for i member Sue Romano with an example of how to do it for older releases. The example she gave she used the WHERE EXISTS clause with the three part name in it.

I use the three part name to get all kinds of information from other partitions, and in this example I will be giving a couple of examples:

Tuesday, December 5, 2023

Another way of using an array for the SQL Where clause

Earlier I wrote a post about how to take data from an array and use it in a SQL Where clause. The method I described was to concatenate the elements of the array into a string, and then use that in the Where clause.

I did mention I had tried an alternative method moving data from the array into a Table and then using it as a subselect in the Where clause. I have been asked several times how I did that, which is what this post is about. Let me state here that what I am going to describe here is not as efficient as the method I described before, which is what you should use.

This alternative program consists of the main body, and two subprocedures. I am going to start by showing the main body:

Friday, December 1, 2023

December presentation

The twice postponed presentation I am doing to my local Local User Group, Central Texas IBM i User Group, is now on Tuesday December 12 at 6:30 PM (CT).

This meeting is online and free to everyone. I will be talking about the recent additions to the CL programming language.

If you had registered before your registration is carried over to this meeting.

If you had not registered you can do so here.


On Tuesday 20 December at 5:00 PM (IST) I will be presenting to COMMON India again. This is part of a four part series in conjunction with COMMON North America.

I am up first and will be talking about the recent additions to the RPG programming language.

You need to register through the COMMON North America website. If you don't have a user id you can create one for free.

The link for registration is here.


If you are a member of a LUG and you would like me to talk to your group, use the contact form to reach me. We can then arrange a date and subject.

If you are a member of a LUG that is not listed on my LUG page, please contact me and send me your group's details.

Wednesday, November 29, 2023

Using values in an array for the SQL Where clause

It was a good question to ask: "Is there a way to use an array in the Where clause of a SQL Select statement?"

I am disappointed that it is not possible to use an array in that manner:

01  exec sql SELECT * FROM PERSON                   
02           WHERE LAST_NAME IN (:MyArray) ;

This code will not pass the SQL precompile. It gives the following error message in the precompile listing:

MSG ID  SEV  RECORD  TEXT
SQL0312  30      13  Position 32 Variable MYARRAY not defined or not usable
                     for reason code 2.

When I look up that message I see that arrays are not allowed:

Wednesday, November 22, 2023

Program to capture CPU usage over time

I have been asked multiple times if there is an easy way to capture a list of jobs with the amount of CPU they, with a program. The answer is, of course, "Yes". I thought it was time to give an example of a program example of how I would do this.

My scenario is that I need to capture all the jobs in this partition with the CPU percentage they are using, every five minutes. This data needs to be captured in an output file, or in this example it's a table. The information I need is:

  • CPU percent
  • Job name
  • Subsystem and the library it is found in
  • User name
  • Job type – batch, interactive, prestart, etc.
  • Function and function type
  • Job status
  • CPU time
  • Time the data was captured

Friday, November 17, 2023

PTFs for 7.5 TR3 and 7.4 TR9 now available

Today is the day the PTFs for the fall 2023 Technical Refreshes are available, IBM i 7.5 TR3 and 7.4 TR4.

This page shows the PTF numbers for the TR base PTFs:

If you also want all the new cool enhancements made to SQL and RPG you'll need to download the latest PTFs for Database, which you can find here (at the time of publication the link to 7.5 PTF is incorrect). According to the RPG Café page: "The 7.4 and 7.5 [ RPG ] PTFs are also available in Db2 for i Fix Packs"

I am excited about this and will be asking the folks at RZKH to apply all of these PTFs to the partition I use.

Thursday, November 16, 2023

A quick and easy way to end a job

I have written programs that capture information over time, using a "never ending" Do-loop in a RPG program. When I wanted the job to end I would use the ENDJOB command. There must be an easier way to use ENDJOB without having to search for the job, copy it's name, and then manually enter the information into the command. This post will give you an example of how to do this.

What is becoming one of my favorite SQL View, ACTIVE_JOB_INFO, has exactly what I need to accomplish this. The column JOB_NAME_SHORT was added to this View in 2021 as part of IBM i 7.4 TR4 and 7.3 TR10. This column contains the job name's name, in this case SIMON_JOB. I can use this column to return the full job name and then end the job with that.

Wednesday, November 15, 2023

Another way to read a subfile

The idea for this post came from a question I was asked. I was asked to clarify an answer to a question someone was asked during a job interview. They had been asked was it possible to read a subfile without using the READC, read changed records, operation code? The interviewee answered that it could probably be read using a CHAIN operation. The interviewer told them they were wrong as it was not possible to use a CHAIN with a subfile.

The interviewee is correct. You can "read" a subfile using a CHAIN operation. This post demonstrates that is possible.

Before I start showing source code for a display file or RPG program, I am going to start with a DDS file I created:

Wednesday, November 8, 2023

My favorite file definition keywords in free format

This all started with a question, and then I got carried away. I was asked did I have a free format example code for what I described in my post about Useful keywords for your F-specs. Recently I have not used file definitions in my programs as I use SQL for my file I/O. I relished the thought of giving these file definition keywords, and then went on to show how they could be used.

The scenario is I have a file, TESTFILE, that I want to make a "work" copy in QTEMP. Then I want to have two members in the file and I fill the first with the contents of TESTFILE in ascending key order, and the second in descending key order. Just to make it interesting for myself, I will not use SQL for any of this, or for showing the results.

TESTFILE contains one field, NAME, and I can use the Run Query command, RUNQRY, to views it contents:

Tuesday, November 7, 2023

Validating an email address

I am sure every application has a need to validate email addresses. After finding a question on Facebook I decide to create one just to see how easy, or hard, it would be.

If this was going to be something that could be used from multiple programs it made sense to put the logic in a procedure that I can then bind into any programs that needs this functionality.

For this scenario I have created two new objects:

  1. MODULE01:  The *MODULE that contains the procedure ValidateEmail
  2. RPGPGM01:  A *PGM that needs to call the procedure to validate various email addresses

IMHO it make sense to start showing and explaining the procedure. As it is long, rather than show it all at once I am going to show it in two parts. The first part: