RPGPGM.COM - From AS400 to IBM i

New pseudo columns added for triggers

2026-08-12T05:00:00.000-05:00

I have written before about how to create triggers, RPG trigger and SQL trigger, and as I have written more, and talked with other people, SQL triggers are the better trigger.

This has been reinforced an addition to Db2 for i (SQL) as part of the latest rounds of Technology Refreshes, IBM i 7.6 TR2 and 7.5 TR8, what have been called "trigger pseudo columns":

TRIGGER_FILE_NAME: File that caused the trigger to execute
TRIGGER_FILE_LIBRARY_NAME: The library that contains that file
TRIGGER_FILE_MEMBER_NAME: Member in that file

I am going to add a trigger to the DDS file TESTFILE. First, I need to "clone" the file to make the trigger output file. I can do this simply with SQL:

01  CREATE TABLE MYLIB.TESTFTRG AS 
02  (SELECT * FROM MYLIB.TESTFILE) DEFINITION ONLY

ACS release 1.1.9.14 out now

2026-08-10T05:00:00.000-05:00

On Saturday, August 8, my ACS alerted me a new release, 1.1.9.14, is available.

IBM's ACS page stated that the new release remedied some vulnerabilities that have been found in all prior releases of ACS, and a number of CVE security fixes.

There are no new enhancements included.

QCMDEXC scalar function can write to job log

2026-08-05T05:00:00.000-05:00

The QCMDEXC SQL scalar function is one of my favorite SQL features. I have used it many times in my work, and here in articles on this website. As part of the latest Technology Refresh, IBM i 7.6 TR2 and 7.5 TR8, it is further enhanced by now being able to write the command performed to the job log.

QCMDEXC scalar function now has two parameters:

COMMAND: The CL command to perform
PRINT: Whether the CL command should be written to the job log. This can contain the following:
- ERROR: Only write to the job log if the command fails
- NONE: No command is written to the job log. This is the default
- VERBOSE: The command is always written to the job log

Before I start describing in details what this change does, I need to have something I can do using the QCMDEXC scalar function. In this scenario I have a DDL table, TESTTABLE, that contains a list of files that I want to delete using a Delete File command, DTLF, in this scalar function.

I can show the contents of this table with the following:

01  SELECT * FROM TESTTABLE

Handling pipe separated data into a Db2 file

2026-08-04T05:00:00.000-05:00

It started with a question:

If my file.txt is pipe filed separated, how can I make the records fall into the physical file to the corresponding fields.

A good question. Most of the time my incoming files are comma separated. The same methods I would use for those will work for pipe separated files too.

First I need a file with pipe separators. I made a copy of some of the columns from my PERSON DDL table to an output file in QTEMP:

01  CREATE TABLE QTEMP.OUTFILE AS 
02  (SELECT PID,LNAME,FNAME FROM PERSON)
03  WITH DATA

RPGPGM.COM-unity on the West Coast

2026-07-30T05:00:00.000-05:00

I was at OCEAN’s TechCon26 conference earlier this month, and I was able to enroll a few more people into the RPGPGM.COM-unity. You can see who these people are here.

If you see me at an IBM i event feel free to introduce yourself to me, ask for your own RPGPGM.COM-unity ribbon, and you will become part of this community. All I ask in return is a photograph of you with it.

If you would like to learn more about the RPGPGM.COM-unity click here.

It is now so easy to view CVE using SQL

2026-07-29T05:00:00.000-05:00

Before I start describing how to use this new SQL Table Function, I need to explain what CVE are.

Common Vulnerabilities and Exposures, CVE, are standardized, publicly identified cybersecurity flaws in software or firmware. When IBM, or a researcher, find a flaw in IBM i, or related software, it is given a unique id. These ids are cataloged globally by the MITRE Corporation, and published with their severity and impacts.

You can learn more about CVE on IBM's "What is CVE?" page.

I cannot find a CL command that allows me to view CVE details. I would have to search for a particular CVE using the IBM Product Security Central page.

With the latest Technology Refreshes, IBM i 7.6 TR2 and 7.5 TR8, is a new SQL Table Function that allows me view, and search, all of CVE from my IBM i partition.

The CVE_INFO Table Function is found in the library SYSTOOL, it has one parameter the IBM i release number. I went through and checked for each release to find that V5R3 is the earliest there are results for:

First Technology Refreshes of 2026 released today

2026-07-24T05:00:00.000-05:00

The PTFs for the Technology Refreshes, IBM i 7.6 TR2 and 7.5 TR8, announced two weeks ago are released today.

When you ask your Sys Admin to download and apply the PTFs don't forget to tell them that they need to download the latest PTFs for Database (Db2) and RPG too.

I am looking forward to "playing" with all the new and enhanced features added, and will be writing about them here, on RPGPGM.COM.

Program to alert when elapsed CPU percentage becomes too high

2026-07-22T05:00:00.000-05:00

When I published my article about Program to alert when jobs' CPU percentage becomes too high someone messaged me asking could I think of something that would do the same when the total CPU exceeded a certain percentage. The answer is, of course, "Yes".

Before I create a program we need to consider the whether the partition that the program will be run on is "capped" or "uncapped". What does that mean?

When a partition is capped it cannot use more than its allocated processing units, in other words it can never exceed 100% CPU usage.

While an uncapped partition has its base entitlement of the processing units, but can borrow unused processing power from the other partitions if available. This means that its CPU usage can exceed 100% of the partition's resources.

This means that 90% CPU utilization could be more concerning on a capped than an uncapped. Keep this in mind when you look at my example program.

I always use ACS's Run SQL Scripts, RSS, to develop the SQL statements I will be including in a RPG program. I can get the elapsed CPU percentage from the SYSTEM_STATUS SQL table function:

New model of IBM Power 11 announced

2026-07-15T05:00:00.000-05:00

Along with the announcement of the latest Technology Refreshes came another announcement for the smallest of the IBM Power11 servers, S1112.

I am not a hardware geek, more of a software one, but having read the announcement document I think it is something worth reporting upon.

S1112 is in the P05 software tier that can run IBM i AIX, Linux, or hybrid workloads on one server.

It comes in two configuations:

First TR of 2026

2026-07-14T05:00:00.000-05:00

The deferred first Technology Refreshes of 2026 PTFs, IBM i 7.6 TR2 and 7.5 TR8, have been announced.

The first thing I do when any new TR announcement is made is to check out what enhancements have been made to Db2 for i (SQL) and RPG. Do note that there are a number of changes and additions made to IBM i 7.6 that have not been made to 7.5.

The SQL that have caught my eye are:

Spring 2026 Performance Guide available

2026-07-13T05:00:00.000-05:00

A new version of IBM's "IBM i on Power – Performance FAQ" was published at the beginning of this month, and is available for download.

The guide provides us with tips on how to measure, and improve, the performance of your IBM Power server, IBM i partitions, Db2 database, and programs that run within it.

You will find the document here.

I always download it and read the passages I feel are relevant to my situation. I recommend you do the same.

More information regarding IBM Bob Premium Package

2026-07-10T05:00:00.000-05:00

IBM made an announcement yesterday, Thursday July 9, with more information about the new IBM Bob Premium Package for i. This includes some more information about the additions to it.

I am not going to repeat what is says. Just provide you with the link to it here.

Using SQL to retrieve information from the internet

2026-07-08T05:00:00.000-05:00

There are many different web sites that offer information that would be useful to consume in IBM i. I have seen several examples of doing this, and I thought I would show my example, which I think, shows how simple it can be.

In my example I want to retrieve the share prices for the three biggest technology companies in the city I live in:

Dell, stock symbol: DELL
IBM, symbol: IBM
Tesla, symbol: TSLA

Retrieving the value of a column from a previous row

2026-07-01T05:00:00.000-05:00

I was sent a copy of a job log from weeks ago, and had been asked to identify which parts of the job had run the longest. Fortunately, the spool file of the job log was still available so I could do some SQL magic to extract useful information from it. The first thing I want to do is to determine is how long each program or command took. I can extract the timestamp from the spool file, but I need to be able to retrieve the timestamp for the previous entry too. How can I retrieve that information for the previous entry?

After a bit of searching I found a SQL function that would allow me to do this. Rather than go straight into showing what I did with the joblog spool file, I am going to start with a simpler example. I have a DDL table, TESTTABLE, that has a decimal column, TEST_NUMBER, and nine rows of data. I can show the data using the following SQL statement:

01  SELECT TEST_NUMBER
02    FROM TESTTABLE

Which returns:

Migrate While Active Redbook

2026-06-29T05:00:00.000-05:00

Migrate While Active, MWA, is one of the more exciting features recently introduced for IBM Power hardware and the IBM i operating system. It is tool that helps me to move IBM i workloads to new servers, data centers, or the cloud with near-zero downtime.

Basically MWA is performed in three steps:

Background Copy: A copy of the partition is made, while the source partition is fully online and active.
Data Sync: It constantly synchronizes all data changes from the live system to the copy.
Cutover: Once both match, I make the switch to the new partition.

This results in almost no interruption in service.

IBM Bob Premium Package for i released

2026-06-25T05:00:00.000-05:00

Today the first significant update for IBM Bob, the AI-development tool for IBM i has been released.

It is called IBM Bob Premium Package for i

You can read the announcement, that gives you all the details on the new features, here.

There is also an Overview page, that includes the link to download it, here.

You do have to be using IBM Bob 2.0.0 to use IBM Bob Premium Package for i.

After the first paragraph in the Overview document is a button to "Copy Markdown". I strongly recommend you do. I clicked the button and then pasted what was copied into Notepad. The markdown contains all kinds of useful information to successfully install the new version.

Within markdown are some paths to further documentation. As paths do not include the domain I have for your convenience done so below:

Calculating the number of seconds for a time

2026-06-24T05:00:00.000-05:00

I have to admit this was a strange request. Interfacing data to another application is no big deal, but the way it wanted its date and time data formatted was. It wanted a character date, in *MDY format with the date separator characters, character time, with dots as the separator character, and an integer of the number of seconds that the time represented.

I could calculate the seconds as:

TotalSeconds = (Hours x 3600) + (Minutes x 60) + Seconds

Fortunately I stumbled across a SQL scalar function that would do this for me: MIDNIGHT_SECONDS. This returns the number of seconds that the time represents. Which is exactly what I am looking for.

I can demonstrate this with the following SQL statement:

Another anniversary for IBM i and IBM Power

2026-06-22T05:00:00.003-05:00

Yesterday, Sunday June 21 2026, was the 38th anniversary of when the ancestor of the IBM Power and IBM i, AS/400, was first announced. Excitement was high as the advancements made to create AS/400 were ground breaking, many other maufactures spent years developing the equivalent technologies into their hardware and software.

Over the past 38 years a lot has changed, the AS/400 evolved through different forms, and names, before becoming the IBM Power server back in 2008.

Does this mean that the "AS/400" is old and outdated? The Power servers can run several different operating system, including IBM i. With the modern IBM i operating system we can emulate the AS/400, and this advanced operating system can do so much more than the AS/400, and its operating system OS/400, ever could.

I think I did a good job describing this history for the 35th anniversary. If you are interested in learning more what AS/400 was, and what it has become, read the story here.

Happy birthday IBM Power and IBM i! May you have many more!

Program to alert when jobs' CPU percentage becomes too high

2026-06-17T05:00:00.000-05:00

I was asked if there was a way to alert if a job exceeded a certain threshold of CPU percentage. The IBM i operating system collects data through Collection Services. Navigator for i and Performance Data Investigator, PDI, can be used to analyze CPU heavy jobs, but this is all historical data there is no real time alerting.

There are various tools from ISV that can do this. For this website I always write about solutions using just what comes in the IBM i operating system, with no third party software.

This problem allows me to use one of my favorite Db2 for i table function, ACTIVE_JOB_INFO. This table function allows me to retrieve information about all of the active jobs on my partition.

RPGPGM.COM becomes a teenager

2026-06-10T04:00:00.000-05:00

Another year milestone has been reached as I celebrate the 13th birthday of RPGPGM.COM. It has been an incredible personal journey since I have published that first post in 2013. Your feedback and encouragement have made all the effort I have spent worthwhile.

What has happened to me in the past twelve months?

Determining the Ordinal date with SQL

2026-06-03T05:00:00.000-05:00

A friend asked me if there was a simple way to calculate the "Julian" date when retrieving dates from a DDL table or DDS file with a date type field.

Before I continue I need to describe the difference between the Julian and the Ordinal dates. While we all call a date in YYYYDDD a "Julian" date, that is not correct. The Julian date is a count of days since January 1, 4713 BCE. I found this helpful link describing why it is this date. The correct name for the YYYYDDD date is the Ordinal date, which is recognized by the ISO 8601 standard.

Db2 for i includes a Julian day scalar function. The statement below show me using it with the date June 1, 2026:

01  VALUES JULIAN_DAY('2026-06-01')

The result is:

ACS 1.1.9.13 is replaced

2026-05-28T05:00:00.000-05:00

The original contents of this page have become obsolete, go to this page for up-to-date information.

Calculating the power and square root of a number

2026-05-27T05:00:00.004-05:00

There are times I need to calculate the dimensions of objects, and to do that I need to use things like x squared, or the square root of y. It all brings back memories of sitting in mathematics lessons back in high school, a very long time ago.

It is simple in RPG, but I want to do it in SQL so that I can calculate the information I need in my SQL View.

I thought it would be a good reminder to show how to calculate the square root and how to use exponentiation (x^y, x to the power of y) in both RPG and SQL.

Determine the length of a string when using SQL

2026-05-20T05:00:00.005-05:00

The title is not as descriptive as I would have liked, my original title was too long to appear reasonably at the top of this page. I am going to describe how you can determine the length of the string of characters within a column from a DDS file or DDL table using SQL. I have found this most useful when using it within a Select statement.

This SQL function can be called by one of two names: CHARACTER_LENGTH or CHAR_LENGTH.

There is also a related function, LENGTH, that I will describe later in this post.

As its name suggests, CHARACTER_LENGTH will return the length of a string expression. In the following example I am using the VALUES SQL statement with CHARACTER_LENGTH:

01  VALUES CHARACTER_LENGTH('A             ')

Generating help text using an IBM i command

2026-05-13T05:00:00.025-05:00

This is another post about something I did not know existed until a few days ago. I have been building some commands to help automate certain processes, and I thought it would be a good idea to create help text for the commands so that anyone using it could use it instead of asking me how to use the command.

Help text for commands must be in the form of a panel group object, *PNLGRP. The programming language used to create the contents of a panel group is User Interface Manager, UIM. Don't worry if you have never used UIM, I think it is a simple language to understand.

Several years ago I wrote a post about creating help panel groups with UIM, therefore, if you want more information than I am going to mention here please refer to that post.

I "stumbled" across the command GENCMDDOC, Generate Command Documentation, which will create a template for my command that I can then add details to. I admit I have never heard of it before, and having used it I found it saved me a lot of time.

First I need a command. I just randomly copied some parts of commands I had created earlier into one new command, TESTCMD.