Enumeration of constants comes to RPG

Enumeration definitions have been added to RPG as part of the Fall Technology Refreshes, IBM i 7.5 TR3 and 7.4 TR9. This allows me to define a list, or group, of constants in one definition structure. Personally I would only include related constants in one enumeration group, but I could include all of my constants, both related and unrelated, in one group.

The definition looks very similar to how to code a data structure definition.

01 dcl-enum EnumerationName ; 02 Name1 'Value 1' ; 03 Name2 'Value 2' ; 04 end-enum ;

Creating Excel spreadsheets with SQL

One of the more eye-catching additions to Db2 for i in the latest round of Technology Refreshes, IBM i 7.5 TR3 and 7.4 TR9, was a SQL scalar function that would create a Microsoft Excel compatible spreadsheet in the IFS.

For years we have been able to create Common Separated Variable length file, CSV, by copying data to the IFS. This can be opened using Microsoft Excel and then saved as an Excel compatible spreadsheet. This is the first time an easy-to-use method has been provided, as part of native IBM i, to allow us to create an Excel in the IFS from data in the Db2 database.

The new scalar function is called GENERATE_SPREADSHEET and is found in the library SYSTOOLS. It has a number of parameters that need to be passed to it:

Check if license product installed

I was performing some testing on a different IBM i partition where I wanted to take a spool file and generate a PDF in my IFS folder. I received an error saying that "Transformation services not loaded". Before I move my testing to another partition I wanted an easy way to check is "IBM Transformation Services for i" is installed.

I could check using the Work License Information, WRKLICINF, as the message suggested. But I my profile does not have sufficient authority to use it.

Fortunately there is an alternative I can use. I need to thank IBM Db2 for i chief architect, Scott Forstie, for bringing to my attention a SQL View that provides me with an easy to way to accomplish what I want.

Happy New Year 2024

Another year has passed and we all look forward into the New Year, 2024, with excitement thinking of all the good things it can bring to us all.

Before I put the old year out of mind, what did it bring us in the IBM i world?

• After seven years the end of regular support of IBM i 7.3
• Ten years after its announcement the end of service for IBM Power8 servers
• Two new Technology Refreshes for 7.4 and 7.5

What did you find the most interesting last year? These were the top ten popular posts from last year:

Using QShell to search source members

The idea for this post comes from one I found upon the Seiden Group's website. I need to thank Alan Seiden for giving me permission to take the contents of his post and tweak it in a way I would use it.

The post gave an example of using QShell to grep, search files for a string of characters, to search source members in source files. As it is possible to use wild cards in a grep statement I can search many libraries and/or many different source files for the string I desire. For example if I want to search every RPG source file for the string "free". I would start QShell by using the following command:

qsh

900th post

Yersterday this blog reached another milestone, the 900th post I have published! (not including this one)

I thank all of you for visiting this blog. Without you I would not have had the will to produce the amount of content needed to reach this milestone.

SQL Scalar Function to make comparisons easier

This is another SQL Scalar Function I found almost by accident. The TOTALORDER scalar function allows me to compare two numbers and returns an integer to tell me which one is greater:

Comparison	Returned
Value 1 < Value 2	-1
Value 1 = Value 2	0
Value 1 > Value 2	1

The syntax for this Scalar Function is:

TOTALORDER(Value_1, Value_2)

RPG BiF to get leftmost or rightmost characters

Within the latest Technology Refreshes, IBM i 7.5 TR3 and 7.4 TR9, are a couple of new RPG Built in Functions, BiF, that can make it easier to get the leftmost or rightmost characters from another variable.

The new BiF are:

• %LEFT:  gets the leftmost characters
• %RIGHT:  gets the rightmost characters

The syntax is the same for them both:

Result = %left(< string or variable > : < number of characters to get > : < charcount constant >) ; Result = %right(< string or variable > : < number of characters to get > : < charcount constant >) ;

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: