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:

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

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:

• IBM i 7.5 TR3
• IBM i 7.4 TR9

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.

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.

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:

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:

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:

Testing the new save compression ZLIB

After applying the latest round of CUM PTFs to a partition with IBM i 7.4 I noticed that the new save compression algorithm, ZLIB, is available. I was surprised as I was under the impression that ZLIB was only available for IBM i 7.5 . There is no mention of ZLIB in the help for the Save Objects command, SAVOBJ, data compression parameter's help. Was this an oversight by IBM introducing something that was not compatible 7.4? This made me want to test it to see if it worked in 7.4, and if it did how much were the savings compared to the other types of data compression.

I have before compared the savings I could get with the various compression algorithms. Now I could compare them all to the new ZLIB.