July's presentations

July is the month of OCEAN’s TechCon 2026. TechCon is one of my favorite events of the year, and it is the IBM i event of the year in the western United States. The event consists of two days, a virtual day, Thursday July 23, and an in-person one, Friday July 24. I have been asked to speak on the Friday, and will be giving four presentations.

If you want to learn more about the "best in the west" you can check out the TechCon website to see who else is presenting, and register here.

Earlier in the month, Tuesday July 14, I will be virtually visiting with the IBM i Midwest User Group, iMWUG, to make two presentations about things to make tracking changes to your database easier.

You can register by clicking on the link on this page.

Later the same evening, Tuesday July 14, the Central Texas IBM i User Group, will be hearing from IBM Champion Gregory Simmons, who will be debuting a brand-new presentation to us, about using the three-part name in SQL. Come and learn what this is, and how you can use it.

The event is online, free, and you can register here.

I hope to see you at one of these events.

Retrieving the value of a column from a previous row

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

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:

1. Background Copy: A copy of the partition is made, while the source partition is fully online and active.
2. Data Sync: It constantly synchronizes all data changes from the live system to the copy.
3. 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

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

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

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

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

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

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 version 1.1.9.13 available

Yesterday, Wednesday May 27, I noticed that a new release of ACS, 1.1.9.13, is now available for download.

This is an update to remediate a number of CVE, and no enhancements have been added.

Calculating the power and square root of a number

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

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

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.

PowerUp attendees rally to join RPGPGM.COM-unity

The RPGPGM.COM-unity continues to grow. I handed out more of the ribbons at this year's COMMON PowerUp than I have at any other event. You can see who I gave them to here.

If you see me at an IBM i event feel free to introduce yourself to me. In all likelihood I will have a RPGPGM.COM-unity ribbon on me, and you can join the 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.

Determining the number of entries in a list parameter from a command

I was creating a command with what I am going to call a "list parameter", to allow the entry of up to ten libraries. While writing the Command Processing Program, CPP, I encountered something I could not find a good description of how to handle. Therefore, I am writing this post to give you a good example of how I did it.

I am not going to go into too much detail on how to create commands and their parameters as I covered many of the basics in two earlier posts: Creating your own commands, part 1 and Creating your own commands, part 2.

My example command, MYCMD, has just one parameter, which will be the list of up to ten libraries. The source for the command is as follows:

01 CMD PROMPT('List of libraries') 02 PARM KWD(LIBRARY) TYPE(*NAME) MIN(1) MAX(10) + 03 PROMPT('Libraries')

Line 2 and 3: The command parameter LIBRARY has a number of keywords:

• KWD:  The keyword name for the parameter
• TYPE:  It is a name type parameter, which means it is equivalent of ten long character
• MIN:  The minimum number of entries that must be given
• MAX:  The maximum number of allowed entries
• PROMPT:  The description that appears on the screen

Checking if there are updates for your Group PTFs

One of the duties of a good System Administrator is to keep the group PTFs up to date on the IBM i partitions you are responsible for. To assist with this IBM has "Group PTF Currency". The first time I encountered this phrase I was confused as I could not equate how PTFs related to money. The confusion was cleared up when I found out the "currency" refers to how current your PTFs are.

In the past the System Administrator would have manually check IBM's Fix Central website and compare the version of the PTFs on their partitions to the ones shown.

To make life easier IBM created a SQL view, GROUP_PTF_CURRENCY, in IBM i 7.1 that would retrieve the latest information about the group PTFs from the IBM "Preventive Service Planning", PSP, website. This View shows the PTF group level installed and the latest group level available. By comparing the two the System Administrator can decide whether it is time to get and apply the latest PTF group.

We can see the data on the PSP website as is it an XML file and can be reached at:

Checking the Electronic Service Agent

The Electronic Service Agent, ESA, is part of the IBM i operating system. Its purpose is to monitor your system's health, detect potential hardware failures, and report issues directly to IBM Support. If it encounters problems it can "call home" to report the issue to IBM.

To be able to "call home" it must be able to connect to IBM. How can we test that it can?

We can either use:

• VFYSRVAGT the Verify Service Agent command
• ELECTRONIC_SERVICE_AGENT_INFO SQL View that shows the same information

I am going to explain both of these below.

ACS version 1.1.9.12 available

 

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

 

Track Group PTFs with SQL

I have mentioned the SQL View GROUP_PTF_INFO a couple of times in passing in other posts on this website, I have not explained what this View gives us.

A PTF Group is a bundle of related PTFs which are designed and managed as a single group. This simplifies PTF updates as all the necessary individual PTFs for a specific group are applied together. Examples of PTF groups are:

• SF99760 for cumulative PTFs
• SF99960 for Db2 for i
• SF99962 for the IBM HTTP server

There are several ways I can check on the PTF Groups on my IBM i partition:

• DSPPTFGRP:  Display PTF Group command
• WRKPTFGRP:  Work with PTF Group command
• GROUP_PTF_INFO:  SQL View

In all of my examples I am going to use the PTF Group for Db2 for i, SF99960, on a partition that is running IBM i 7.6 .

When I use the commands DSPPTFGRP and WRKPTFGRP I am frustrated that I cannot get an output file of the information it retrieves. With WRKPTFGRP I can only display the results:

New Englanders flock to join the RPGPGM.COM-unity

I handed out more of the RPGPGM.COM-unity ribbons at the NEUGC 2026 conference. You can see those I gave them to here.

If you see me at an IBM i event feel free to introduce yourself to me. In all likelihood I will have a RPGPGM.COM-unity ribbon on me, and you can become a member. 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.

Spring 2026 IBM i TRs coming later

On the usual schedule there is a Technology Refresh at about the time of the COMMON PowerUp conference. This year will be different.

I was told by an IBM insider, last week, that the release date of the TRs has been postponed until July of this year. They would not say why. I assumed that news of the delay was business proprietary information, and was not going to share it.

This morning I see a couple of other websites running the story that the TRs are being delayed until the time more of the new IBM Power11 servers are announced.

As others are reporting this news, I am publishing what I know.

New Job columns added to the Record Lock View

In my last post I explained how the parts of the IBM i job name had been added as separate columns to the OBJECT_LOCK_INFO SQL View. I am pleased to say that the same three columns have been added to the RECORD_LOCK_INFO View too:

1. JOB_USER:  User profile of the job
2. JOB_NAME_SHORT:  Name of the job
3. JOB_NUMBER:  Number of the job

Before these columns were added, if I wanted to list my jobs, with the user profile "SIMON", I would need to extract it from the full job name or use a wildcard in the Where clause. With the addition of these three columns, I will show much easier this becomes.

If I wanted to check for record locks, not using RECORD_LOCK_INFO, I would use the Display Record Locks command, DSPRCDLCK. For example, if I wanted to check for record locks on TESTFILE, in my library, I would use the following:

01 DSPRCDLCK FILE(TESTFILE)

Which will show me the following:

New columns added to the Object Lock View

While the addition of these three new columns to the OBJECT_LOCK_INFO SQL View may not appear to be significant, I know it will make it easier for me to process data for jobs.

The three new columns are all part of the long job name, and now have their own columns:

1. JOB_USER:  User profile of the job
2. JOB_NAME_SHORT:  Name of the job
3. JOB_NUMBER:  Number of the job

Prior to the addition of these columns if I wanted to list my jobs, with the user profile "SIMON", I would need to extract it from the full job name or use a wildcard in the Where clause.

Before I show any SQL statements I need to explain the scenario I created. I have two programs:

1. Program 1: This RPG program opens the file TESTFILE for update and then pauses for ten minutes
2. Program 2: The second program uses a display file, TESTDSPF, and uses the EXFMT operation code to show it on the screen

Viewing the job's overrides

I have been burned before by file overrides, which point input, output, update, etc. both to an expected file. Anything new that comes along to help me show overrides makes me happy. The Fall 2025 Technology Refreshes, IBM i 7.6 TR1 and 7.5 TR7, has done that with a new SQL View: OVERRIDE_INFO. This view will return information about the overrides for the current job only.

I can override all kinds of files:

• OVRDBF:  database files
• OVRDSPF:  display files
• OVRMSGF:  message files
• OVRPRTF:  printer files
• OVRSAVF:  save files
• OVRTAPF:  tape files

I readily admit I have not used all of these commands.

There are three different levels I can scope an override:

Update to the technical resources roadmap

Yesterday an updated version of the IBM i technical resource roadmap was published. This web page contains a list of useful links for all things IBM i, from both IBM and other sources too. You will even find a link to this website in the Blogs section.

You can visit the page using the link here.

I have also updated the link, number 6 in the "Links to useful sites", on the right.

I am sure you will find something of interest on that page.

Using SQL to view system problems

Just a few weeks ago someone asked me if there was a way, using SQL, he could view problems on his system. What we mean by "problems" are not just error messages. These are the kinds of problems/errors that create a "problem" on your partition that you would use the DSPRPB, Display Problem, command to view.

Fortunately in the Fall 2025 Technology Refreshes, IBM i 7.6 TR1 and 7.5 TR7, was a new SQL View that displays this information: PROBLEM_INFO

Before I describe PROBLEM_INFO, I want to explain the methodology that was used before. IBM i has a Problem Management function that alerts when there are issues with hardware and system software. If I wanted to see what problems there are on this partition I would use the DSPPRB, Display Problem, command. I can enter the following on any command line:

DSPPRB

Wisconsinites join the RPGPGM.COM-unity

At my first conference of the year, WMCPA iCon, I handed out 51 ribbons to the newest members of the RPGPGM.COM-unity.

You can see photographs of these new members here.

If you see me at an IBM i event feel free to introduce yourself to me. In all likelihood I will have a RPGPGM.COM-unity ribbon on me, and you can become a member. 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.

Reducing the amount of unnecessary Index Advisor data

I do check the Index Advisor on a regular basis to see what is recommended. I have found that this perspective can become overwhelmed by old data. Who cares what index were advised over a year ago? I either addressed that advice, or the program(s) were changed that used the files in that way.

Every six months I go into the tool and clear the data. This way I will only see the recent recommendations. I have an entry in my calendar to remind me to do this. Wouldn't it be nice if this could be automated.

As part of the latest Technology Refreshes, IBM i 7.6 TR1 and 7.5 TR7 a new Global variable is introduced that will automate the purging for me.

Limiting the amount of data SELF retains

While I love the amount of information that the IBM i will retain for us, there are times where it can become overwhelming, or redundant. An example of this is how much SELF, SQL Error Logging Facility, data do I wish to retain.

Prior to the latest Technology Refreshes, IBM i 7.6 TR1 and 7.5 TR7, I was responsible for purging the SELF error log table, QSYS2/SQL_ERRORT. As part of these TRs a new Global variable was introduced.

Finding Table functions with SYSFUNCS

In every IBM i recent release and Technology Refresh a few new Audit Journal Table extract functions are added. I wanted to see which one were on the IBM i partition I was using. I was tempted to use the OBJECT_STATISTICS Table function to get a list of them:

01 SELECT OBJNAME,OBJLONGNAME 02 FROM TABLE(QSYS2.OBJECT_STATISTICS('SYSTOOLS','*ALL','AUD*'))

Line 1: I am only interested in the system object name, OBJNAME, and the long (SQL) object name, OBJLONGNAME.

Line 2: The Audit Journal Table functions are in the SYSTOOLS library. I am using the wild card for the object name, in the third parameter, to find all the objects that start with 'AUD'.

The results were:

Retrieving the description for the Audit Journal Entry Types

I have, to date, been disappointed that I could not programmatically retrieve a description for the 420 Audit journal entry types. Referring to IBM's documentation was just not practical. A new SQL View in the Fall 2025 Technical Refreshes has given me what I wanted, a list of all the journal entry types with their descriptions.

The View is JOURNAL_CODE_INFO, which is found in the library QSYS2. It contains the following columns:

1. JOURNAL_CODE:  The journal code
2. JOURNAL_CODE_DESCRIPTION:  Description of the Journal code
3. JOURNAL_ENTRY_TYPE:  Journal entry type
4. JOURNAL_ENTRY_TYPE_DESCRIPTION:  Description for the journal entry type

The journal codes are for a specific "action", while the Journal Entry Type is the type of information. You can see this with the new View with the following statement:

Improvements to GENERATE_SPREADSHEET

The Fall 2025 Technical Refreshes brought some significant improvements to the GENERATE_SPREADSHEET SQL scalar function. These being:

• Execute SQL statement within an IFS files
• Append results from another SQL statement to an existing spreadsheet
• Create a new worksheet, and start the output at a specific row and column
• End the Java program that is started to generate the spreadsheet
• No longer have to use STDOUT to capture information from GENERATE_SPREADSHEET

I am not going to repeat what I have said in previous posts. But, I am going to give an example, below of generating a spreadsheet as a reminder:

IBM Bob formally announced

On Friday February 20 IBM's AI Bob was formally announced with a planned availability date of March 24.

You can read the announcement here.

IBM document: IBM i 7.5 vs 7.6 comparison

On January 30, IBM Support published a page called "IBM i V7R5 versus V7R6 comparison" (shame on IBM Support for not using the proper names of 7.5 and 7.6).

The page is to answer the question: What are the differences between 7.5 and 7.6?

I thought it was something useful to share because, as well as the comparison, there are links to many useful pages of information. You can reach the page here

I hope you find it as interesting as I did.

Find all the services programs used by a program

Most of the time it is my curiosity, but there have been occasions, where I needed to know if a program calls a particular service program. It is easy to discover which service a programs call a service program, but what about the service programs that that those service programs calls? Now there is way to do that.

A new SQL Table function, PROGRAM_RESOLVED_ACTIVATIONS, has been added to IBM i as part of latest Technology Refreshes, IBM i 7.6 TR1 and IBM i 7.5 TR7.

PROGRAM_RESOLVED_ACTIVATIONS has five parameters:

1. PROGRAM_LIBRARY:  Library that contains the object
2. PROGRAM_NAME:  Name of the program or service program
3. OBJECT_TYPE:  *PGM if the object is a program, *SRVPGM if it is a service program
4. DEFERRED_SERVICE_PROGRAMS:  NO do not follow deferred activations, these are service programs that had *DEFER in the BNDSRVPGM parameter of the CRTPGM or CRTSRVPGM command. YES follow the deferred activations, this is the default
5. IGNORE_ERRORS:  NO if an error is found an error happens. YES a warning is returned; this is the default

I have only used the first three.

Using SQL TRANSLATE in place of REGEXP_REPLACE

I encountered something last week that I think may be a "bug" in either RPG or in Db2 for i. I was using the REGEXP_REPLACE scalar function, and I received an error message that made no sense. I ran out of ideas of how to overcome this error using REGEXP_REPLACE, and I went on to use the TRANSLATE scalar function. Before I tell you what the error is I need to explain my situation.

I was working upon a partition which was running IBM i 7.5 with the latest PTFs for Technology Refresh 7, TR7, and the latest PTFs for Database and RPG:

• Database (SQL) = SF99950 Level 11
• RPG = 5770WDS SJ08064

The partition's CCSID is:

01 SELECT SYSTEM_VALUE_NAME,SYSTEM_VALUE 02 FROM QSYS2.SYSTEM_VALUE_INFO 03 WHERE SYSTEM_VALUE_NAME = 'QCCSID' SYSTEM_VALUE_NAME SYSTEM_VALUE ----------------- ------------ QCCSID 273

2026 Fortra survey published

This year's Fortra 2026 IBM i Marketplace Survey results has been published. This is the twelfth year of the survey, and I for one, always find it an interesting read to learn what the other members of the IBM i community are up to.

If you did not participate in this year's survey I would like to strongly encourage you to participate in the next, as it is a good insight as to what is important to people and companies using IBM i.

You can read it here.

You can also watch the discussion about the survey with Steve Will, IBM i CTO and Chief Architect, Tom Huntington, Fortra, Kris Whitney, IBM i Chief Engineer, Tomothy Prickett Morgan, IT Jungle, here.

SEND_MESSAGE can now send message to any message queue

When SEND_MESSAGE SQL procedure was introduced in 2021, as part of IBM i 7.3 TR10 and 7.4 TR4, it would be used to send messages to the System operator message queue. I have used this many times to do so, but I have always wondered how I could do the same to any message queue? Well, in the latest Technology Refreshes, IBM i 7.6 TR1 and 7.5 TR7, this functionality was added.

Two new parameters were added to the procedure to make this possible:

• MESSAGE_QUEUE_LIBRARY:  The library that contains the message queue I want to send the message to
• MESSAGE_QUEUE:  The message queue I want to send the message to

If I omit these parameters the message will be sent to the System operator message queue.

The first example I am going to show is using the procedure as it was intended, sending a message to the System operator message queue. I am going to use the message id CPF9898, which is a general escape message, i.e. I can provide any text I want for the message to use. I can show this using the MESSAGE_FILE_DATA SQL View:

End of support for small Power9 effective tomorrow

Tomorrow, January 31, 2026, is the End of Support, EoS, for the small Power9 servers:

• L922 (9008-22L)
• S922 (9009-22A)
• S914 (9009-41A)
• S924 (9009-42A)
• H922 (9223-22H)
• H924 (9223-42H)

You can read the announcement for this from January 2025 here.

What does end of support mean?

After End of Standard Service, IBM no longer provides preventive service, new updates or fixes, or development of new machine code updates, patches, or fixes (including security fixes).

Extended Support is available for these models, see here.

I do not see any information for the EoS of the larger Power9s, E950 and E980.

Better examples for creating and consuming JSON

Whenever I am asked for an example of how to generate a JSON array or how to consume JSON data I have to point people to different posts, rather than have one that covers both. I have written this post to be that, examples of how to create and how to consume JSON.

I will be using Db2 for i to generate JSON, as I find it simpler to use than doing the equivalent in RPG.

I am going to give four examples in this post to show how to generate a simple JSON array, one with labels and the other without. Then consume the generated JSON into a format that can be easily processed by RPG. All of these examples are going to use embedded SQL in RPG programs.

I will be using my PERSON DDL Table for the input for these examples. What I want to do is to create a JSON containing:

RPG BiFs that now convert CCSID

As the computing world has become more "open" we need to process data that comes in different character sets, not just the EBCDIC CCSID in the IBM i partitions we are working on. Modern RPG has been able to handle all of the character sets, with various Built in Functions, BiF, to convert the character set from one to another.

The RPG PTFs that accompanied new Technology Refreshes, IBM i 7.6 TR1 and IBM i 7.5 TR7, included an improvement to character handling. Some BiFs will now automatically match the character types of the data in the variables used. This post is going to explain what they are, and any errors I found.

The BiFs that do this implicit CCSID conversion are:

New ACS update, 1.1.9.11, fixes vulnerability

 

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

 

Another way to list files defined in Querys

After writing the post about Retrieving the print file name from a Query it struck me that I could use the same methodology to answer a question I have been asked on several occasions: Which Querys are using a particular file?

I cannot answer which Querys are using a file. I can list all the files that Querys are defined to use.

What are the steps I need to perform to get that information:

1. Make a list of all the Querys in a particular library
2. Print their definitions using PRINT_QUERY_DEFINITION
3. Extract the file names from the spool file, and write them to an output file

I am going to show you how I manually executed the above steps. And then I will show a program I created to do the same thing.

First, I need to make a list of all the Querys in a library, MYLIB. I will use the OBJECT_STATISTICS SQL Table function.

01 SELECT OBJLIB,OBJNAME 02 FROM TABLE(QSYS2.OBJECT_STATISTICS('MYLIB','*QRYDFN','*ALL'))

Line 1: I only want the Query library and name in my results.

A new way to list columns for SQL insert

The latest Technology Refreshes introduced a new way to give the columns I am inserting with an SQL Insert statement into a Table or file.

This is the DDL Table I will be inserting into:

01 CREATE TABLE RPGPGM1.TEST_TABLE 02 (IDENTITY BIGINT GENERATED ALWAYS AS IDENTITY NOT NULL, 03 LAST_NAME FOR COLUMN "LNAME" VARCHAR(30), 04 FIRST_NAME FOR COLUMN "FNAME" VARCHAR(20), 05 ADDRESS_CITY FOR COLUMN "CITY" VARCHAR(20), 06 ADDRESS_STATE FOR COLUMN "STATE" CHAR(2), 07 ADDRESS_COUNTRY FOR COLUMN "COUNTRY" CHAR(3)) ;

Happy New Year 2026 to you all

New Years is one of my favorite times of year, as we are filled with the excitement for what 2026 will bring us all.

Before we put our feelings and thoughts of 2025 behind us, what did you find interesting last year? These were the top ten popular posts from last year: