Enhanced encryption routine added to SQL

Included within the latest Technology Refreshes, IBM i 7.5 TR3 and 7.4 TR9, is an improved encryption algorithm for AES, which uses a 256 bit key. It is as easy to use as the older versions of encryption I wrote about over a year ago. I just wanted to take this opportunity to reinforce the best ways of using it.

It is a scalar function that does the encryption. Its syntax is just:

ENCRYPT_AES256(< string or variable >, < password string >, < hint >)

In its simplest for I can just use it like:

01 VALUES BINARY(ENCRYPT_AES256('This is RPGPGM.COM','Password'))

Obscuring data in a column with SQL

I am not sure if this is the most accurate title for what I want to do, obscure part of an email address as a hint for someone trying to signon to a web faced application. If someone has forgotten the email address they used to register, part of the process is to present them with a hint of the email address they used to sign up for the service. It obscures part of the username of the email address. For example, the email address:

john.smith@gmail.com

Would be obscured to become:

jXXXXXXXXX@gmail.com

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:

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:

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.