Presentations in February 2023

This month I am keeping up with my goal of promoting IBM i Local User Groups, LUG, by making the following presentations:

• Thursday February 9 @ 3:00PM (ET) with MAGIC: Tricks with spool files using SQL. On-line, open to everyone.


• Tuesday February 14 @ 1:30PM - 4:30M (CT) with COMMON: RPG wellness check. On-line. COMMON members only.


• Tuesday February 21 @ 6:00PM (ET) with SEMIUG: Tricks with spool files using SQL. On-line, open to everyone.


• Thursday February 23 @ 8:00AM (ET) with WMSUG: Looking at you data with a different view point. On-line, open to everyone.

For more information about these events check the LUG's websites, and contact them directly.

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.

Finding all the fields that contain certain characters

The germ for the idea for this post came from a question I saw in a Facebook Group. The question was how to identify records in a file where a particular field contains "special" characters, using SQL.

As the question was specially about a DDS file, I created a file, TESTFILE, with one field, FIELD1. I am not going to give the code for this file, just its contents. I would use the following SQL statement to list the contents of this file:

SELECT FIELD1 FROM TESTFILE

The results from this file are:

Finding a way to monitor for more than one message with RPG's monitor

Perhaps this title is a little misleading, I struggled to come up with something that adequately describe this scenario in the space allowed.

I was asked if there was an easy way when using a Monitor group to perform some kind of action if the message id starts with something like 'RNX12'.

Let me get start with my example RPG code:

Creating a User Index with SQL

When I wrote about a SQL procedure that allowed me to add data to a User Index, ADD_USER_INDEX_ENTRY, I created the User Index with the QUSCRTUI API. I received an email from an IBM employee telling me that there was a SQL procedure that would allow me to create the User Index.

I am going to do here is to create the same user index as I did with QUSCRTUI in that other post.

In this first example I am going to create the User Index with a fixed length entries of 100 bytes.

Getting an accurate count of characters in a variable RPG

Over recent years many of us are dealing with data in different character sets, not just the default CCSID of the partition we are using. The most common of these I encounter is UTF8.

UTF8 can contain double byte characters, which take two bytes for the character, as opposed to the standard single byte characters. If I am using a variable I have defined as UTF8 it is difficult to calculate the number of characters within, as the double byte characters result in an over count of the number of character present.

One of the additions to the RPG language as part of IBM i 7.5 Technology Refresh 1 and 7.4 TR7 are several things to make it possible to get a real character count from an UTF8 variable.

I am going to show several example programs to demonstrate how these new features work. Let me start with what I consider the most basic way, that is not bad thing I just me that IMHO the easiest. I am going to break this program into parts so it will be easy to explain what is happening. Let me start at the "top":

Procedure parameter option to convert data type

Another addition to RPG within the IBM i 7.5 TR1 and 7.4 TR7 updates was a new option in the OPTIONS parameter when defining the call to a procedure.

Previously if you passed, for example, a date to a procedure the parameter within the procedure's interface would have to be a date too. If the wrong data type was passed it would error.

The addition to the OPTIONS parameter is:

OPTIONS(*CONVERT)

Happy New Year 2023!

The old year is behind us, and I am always so hopefully and excited what the New Year will bring us all. Perhaps the new year will feel a bit more like the world returning to a kind of normal we have not seen for a couple of years.

In the IBM i world 2022 brought us:

• A new version of IBM i, 7.5
• Two new Technology Refreshes for 7.3 and 7.4, and the first TR for 7.5
• Merlin
• Midsized Power10 servers

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

SQL View gives ability to see information about all commands

IMHO it has been problematic to get information about more than one IBM i command. The Display Command command, DSPCMD, will only output to display or print one command at a time. If I want to get information about more than one, or all, commands then I would need to use an API.

Fortunately with the fall 2022 Technology Refreshes, IBM i 7.5 TR1 and 7.4 TR7, comes a SQL View that contains all the information that the DSPCMD command does for all commands.

This new View, COMMAND_INFO, is simple to use. For example, if I want to list all the information about all the commands in this partition I could just use:

Removing data from a User Index using SQL

In a previous post I wrote about how to add and change data in a User Index. As I can do that, I also need is to delete data from the User Index too.

In IBM i 7.5 and 7.4TR6 introduces a couple of Table Functions that allow me to remove entries from a User Index:

• REMOVE_USER_INDEX_ENTRY
• REMOVE_USER_INDEX_ENTRY_BINARY

Both work in the same way, and I am going to describe how REMOVE_USER_INDEX_ENTRY works.

IBM Power10 server industry standard server of the year

In their annual awards of products are services The Channel Company, via their website CRN.COM, recognized a Power10 server as the Product of the Year in the Industry-standard servers category.

The Power10 chip and servers first announced in September 2021. Followed in July 2022 by the mid- and small-size servers.

SAP, probably the world's best known ERP vendor, performed their own tests pitting the top end Power10 server against the top-of-the-line Dell Intel based servers. The Power10 outperform the Dell server. You can see their findings: