The "IBM i on Power – Performance FAQ" has been updated, effective December 2024.
This guide gives information on how to measure the performance of your IBM i partition, and how to optimize whatever tools and programming languages you use to get the best from the operating system we all love.
You can open and download the document from here.
This is a question that pops up every once in a while, therefore, I have decided to write this post to answer the question. I am sure what I am going to describe here is not the only way to execute a SQL statement that is in a column/field of a table/file. It is just the way I do it. If you know of another way please either comment, below, or use the Contact Form, in the right column, to send me the details.
In the example I am writing about I want to make SQL Aliases for each member in the multiple member file TESTFILE. I have created a CTE to do this, but what I am going to do is to show the individual SQL statements that make up the CTE before I show the CTE statement.
Firstly, I need to establish a list of members in TESTFILE. Here I can use the new SQL view SYSMEMBERSTAT. I am only interested in returning the following columns in the results:
I was asked this question about reading a two members from a DDS file in a RPG:
I'm not understanding what the definition of 'FILE1' and 'FILE2' would be if i am defining the same file in order to use 2 different members. I get the external definitions for file and member, but how do i define the same file in the dcl-f spec?
This post will describe how I would do it.
First I need a file with two members. I start with a DDS file, with one field FIELD.
01 A R TESTFILER 02 A FIELD 30A |
The PTFs for Db2 for i for the latest Technology Refreshes become available today, December 13, 2024.
The PTF for Db2, not the TR PTF, are:
You can download them by follow the appropriate link on the Db2 for IBM i 2024 PTF Group Schedule page.
I have asked RZKH to download these PTFs so I can start experimenting and start writing about the features in these TR.
The TR PTF are:
Read about:
It is often a question I am asked about SELF: What do I do with the data it generates?
Once a week I take the data and copy it from IBM's SELF table into one of my own. Why?
I am only interested in errors I can fix. I am not interested in errors SELF has reported for any of the following:
I was asked a question about using DDS Windows with display files, that is when I noticed I did not have any examples of them. IMHO that is an omission on my part, and I am going to correct here. The examples I will be giving will also answer the question I was asked.
We have been able to create "pop-up" Windows using DDS Display files for many releases, and I have used them for scenarios where a user presses F4 in a field on the display file, a window is displays containing a subfile that the user can select a record from. I am going to keep the following examples much simpler than that, enough to be able to demonstrate some of the features of DDS windows. Examples of coding subfiles in RPG can be found elsewhere on this website.
For these examples I am using one display file and one RPG program. I am going to "break" the display's code into parts so I can describe what is going on and what is different from others. The RPG program can just be shown in one place, as it is very simple. Here is that RPG program:
At a recent conference one of the attendees point out to me that my examples of using an auto-extending array only used it as the place for data when I fetched data from a SQL cursor. What about a scenario I want to load values into an auto-extending using a For group or Do loop?
I was surprised that I have not given an example of this scenario. This post remediates that oversight.
I wanted to keep my example program as simple as possible:
This morning Barbara Morris, lead developer for the RPG compiler, announced that the RPG PTFs for the latest Technology Refreshes, IBM i 7.5 TR5 and 7.4 TR11, are now available.
You can see what these are here.
I don't have the specific PTF numbers for RPG. You will want the latest PTFs for 5770WDS, ILE RPG compiler. Those you can download from Fix Central.
This does not appear to have been coordinated with the Db2 Database (SQL) PTFs. They show they will be available December 13, 2024.
The original contents of this page have become obsolete, go to this page for up-to-date information.
Starting in IBM i 7.4 it is possible to use CL Built in Functions, BiF, in the parameters of CL's Call command. This becomes even easier when I use the additional parameters in the Parameter parameters of the CALL command.
Those of you who have seen example of my RPG code have seen examples where I have used BiFs in its Call operation code parameters. When I learned that there was now similar functionality in CL I became very curious what could I do with it.
I chosen a number of scenarios to write examples for, as these are things I have done many times with RPG's Call operation code.
I do not think I have ever seen IBM do this before. The Vice President of Product Management for the IBM Power Servers, Bargav Balakrishnan, wrote a blog post in the IBM Newsroom about IBM Power11 processors being released next year, 2025.
While his post was not technical, explaining the differences from IBM Power10 and Power11, he did give some details about the performance improvement we could see when comparing these two servers.
He also wrote about integrating the IBM Spyre Accelerator to allow IBM Power servers used to be able the scale AI workloads.
The last section of his post talked about IBM's AI tool for RPG, RPG code assistant, stating that IBM intends to deliver it next year too.
You can read his blog post here.
This goes to show that 2025 is going to be an exciting year for IBM Power, IBM i and RPG.
On Tuesday, November 12 2024, IBM i Chief Architect and CTO, Steve Will, announced the updated "Fall 2024 IBM i Performance FAQ" guide had been published, and is available for download.
This guide gives information on how to measure the performance of your IBM i partition, and how to optimize whatever tools and programming languages you use to get the best from the operating system we all love.
You can open and download the document from here.
I have already downloaded and read this document this guide. And I think it would be good for you to do the same.
When I use RPG Update operation code I tend to use the %FIELDS Built in Function, BiF, with it. The %FIELDS allows me to list the file fields I want to update, all other are unchanged. As I like my code to be "self-documenting" I use it a lot if I am not updating all of the fields in the record, this allows other developers to learn that I was only interested in updating the fields listed.
These days almost all of my data file I/O is with SQL, which leaves me using RPG I/O with just display and printer files. I cannot update a record in a printer file, therefore, this post is exclusively about display files, and subfiles in particular.
Why do I feel the need to write about this when %FIELDS and subfiles have been around for several releases. It is because of a change that is in IBM i 7.5. In this release it is no longer possible to compile a program that uses the %FIELDS when updating a subfile without some extra work. But before I go into that let me show my example code, starting with the DDS source for the display file:
Last week I was presenting at COMMON's Navigate in Toronto, Canada, and I took the opportunity to hand out more of the RPGPGM.COM-unity ribbons. You can see who I gave them to here.
If I gave you a ribbon and I missed taking a photograph of you with it, please send me a photograph posed like the other people I photographed with their ribbons.
What is the RPGPGM.COM-unity? You will have to click on this link to learn what it means.
Have you ever wanted to know what is going on within an active SQL Query Engine, SQE, query?
If, like me, you do there is a table function that shows all kinds of interesting information.
The table function ACTIVE_QUERY_INFO has been around since IBM i 7.3 . It has four parameters that allow you to select the results you want returned.
A second data mart was introduced as part of the last round of Technology Refreshes that allows me to capture and store information about the special authorities given to user profiles in my partitions.
The Special Authority Data Mart is held in the table SPECIAL_AUTHORITY_DATA_MART, which resides in the SYSTOOLS. The table is a Materialize Query Table, MQT. This means that the contents of the table are not dynamic, changed when changes are made to the user profiles, it is refreshed using the REFRESH TABLE SQL statement. If you don't know what that is I will show how to use it in my examples below.
The most basic SQL statement to retrieve the data from the Table is:
01 SELECT * FROM SYSTOOLS.SPECIAL_AUTHORITY_DATA_MART |
The idea for this post came from a question. The questioner had a situation where, due to a mixed-up library list, someone had run the wrong version of a program from another library. They already had the program's name on their display files, and they asked was it possible to retrieve the library the current executing program is in, and add that to the their display files.
After some discussion we decided that any change would be needed in both RPG and CL programs. Fortunately, this is simple in both languages.
Before I start explaining my RPG and CL code I need to have a display file within which are fields for the program and the program's library. I created a simple display file to do this, called TESTDSPF:
01 A DSPSIZ(24 80 *DS3) 02 A R SCREEN 03 A 3 2'Program name . :' 04 A PGMNAME 10 O 3 20 05 A 4 2'Program library :' 06 A PGMLIB R O 4 20REFFLD(PGMNAME *SRC) |
This is something that caught my attention when the latest Technology Refreshes were announced: the ability to create a database of journal entry types entries. I had been doing something similar using the CREATE TABLE SQL statement and various of the Audit journal entry services, AUDIT_JOURNAL_xx. But these additions added functionality that makes it easier for me to extract the journal entries from the System Audit Journal, QAUDJRN.
The two new additions are:
The RPG Cafe has been updated with the four changes that will be coming in the latest Technology Refreshes, IBM i 7.5 TR5 and 7.4 TR11.
These enhancements are:
I consider these three great additions to the modern RPG language.
I am always saddened to learn that an IBM i Local User Group, LUG, stops functioning.
Yesterday, I learned the New Hampshire Midrange User Group, NHMUG, announced on their website that they were disbanding. You can read their announcement here.
This just leaves one IBM i user-related organization in the New England region, the Northeast Users Groups Conference. NEUGC holds an annual conference, see their website for details of the 2025 conference.
If you have any updates for any other IBM i related LUG please let me know via the Contact form on the right.
For a list of all the active IBM i User Groups I have found check out the user groups page.
The Fall Technology Refreshes for IBM i 7.5, TR5, and 7.4, TR11, were announced this morning.
Having a quick glance at the announcement documentation from IBM it would appear that the two have the identical changes:
I have managed to persuade the admins of the server I use to develop the code for these posts upon to give me access to the Audit Journal, QAUDJRN, this gives me the opportunity to write about several enhancements that have made to Db2 for i that makes getting information from QAUDJRN.
Over the past few releases and Technology Refreshes IBM has given us many Audit Journal Services. As QAUDJRN contains many different types of journal entries IBM has given us a specific Audit Journal Service for just about every journal entry type.
All of Audit Journal Services are called: AUDIT_JOURNAL_xx, where xx is the journal entry type. As of IBM i 7.5 TR4/IBM i 7.4 TR10 there are 41 of these Audit Journal Entry Services, out of a total of 76. That is 54% of all journal entry types covered. I do not know if the plan is to cover all of the job entry types, or not.
Someone asked me how I would write a program to copy all of the members in source files to separate files in the IFS, where they could then me imported into a GiT. I can see a program like this being more useful overtime as more people move from using source files to using a GiT repository.
In this example I will be copying all the source members from the source file DEVSRC in the library MYLIB to the IFS directory /home/MyFolder. The files in the IFS would all be named in the following way: source_member_name.source_member_type, for example: TESTRPG.RPGLE
My first attempt at creating a program to do this was to use RPG. I was going to use the CPYTOIMPF command within the QCMDEXC Scalar function. Alas, several hours of creating program I received the following message when running the RPG program for the first time:
If I wanted to get a list of members in a file with SQL I always had to work with the SYSPARTITIONSTAT View, which was not the purpose it was designed for. In the latest latest Technology Refreshes a new View has been added that is designed for information about members, SYSMEMBERSTAT.
The new SYSMEMBERSTAT View is in the library QSYS2 and contains all the columns I could want to know about members in a file.
I always recommend if this is the first time you are using a View you should look at all of the columns and determine, for yourself, which ones are important. The following statement displays all the columns in SYSMEMBERSTAT:
As part of the latest Technology Refreshes a number of improvements have been added to the SEND_EMAIL scalar function.
What are these:
All of which are useful additions to SEND_EMAIL.
Let me jump right to giving an example of using more than one email address and the new cc and bbc email parameters:
IBM published a document last month, August 2024, called "IBM i: Integrate and innovate", with the subtitle "An executive guide to the strategy and roadmap for the IBM I integrated operating environment for IBM Power servers". I think it is great that IBM publishes documents like this. I read them all, and share them with you too.
It is a 31 page document that IMHO you should share with your colleagues, managers, and executives. I will be doing so myself today.
The document can be reached on the IBM website by clicking on the link here.
I struggled with the title of this post to make it something that explains the content, without it being too long. I am not limited for length in the body of this post so I can explain in more detail my scenario: I want to have columns that are listed in my results for a Select statement that are not in the GROUP BY clause.
This is something that has been made easier with a new Scalar function added in the latest round of Technology Refreshes. First let me give you examples of my scenario.
What I am going to do is to take use my PERSON DDL Table and make a count of the places of birth of the people. As these are people from the British Isles, I will also include the columns for the county and the country (England, Ireland, Northern Ireland, Scotland, or Wales) from a second Table, TOWN.
This is one of the additions that came as part of last round of Technology Refreshes that I was excited about. Previously If I need to get information about printer files I would either have to use an API or the DSPFD command. Now I have an easy way to get information about any printer file using a SQL View.
The new SQL View is called PRINTER_FILE_INFO and is found in the SYSTOOLS library.
I always recommend that you run the run the SQL statement to see all of the columns in the SQL View for yourself. That way you can make your determination which columns you find useful to your situation. You can do that with the following statement. I have a limit of returning ten results as I found that if you want to return all the results from the View it may take some time to do so.
Within the latest database PTFs that came as part of the last Technology Refresh were a couple of new Scalar functions that can be used to do things with Environmental Variables. They are named after the APIs that can be used to put and get information from the Environmental Variables. These new SQL Scalar functions are:
The idea of this post came from a question I was asked. The questioner had a scenario where the name of an IFS file they needed read is in a data structure. They were unable to work out how to use the value from the data structure as the path name to read of the IFS file.
Let me start using ACS's "Run SQL Scripts", RSS, tool.
Before I can read a file in the IFS I need to create it. I can use the IFS_WRITE SQL procedure to do so:
01 CALL QSYS2.IFS_WRITE('/home/MyDirectory/test.txt',
02 'First line',
03 END_OF_LINE => 'CRLF')
|
I am sure I am not the only person who wants any logical files in the same library as the physical file they are created over. I do realize that join logical files and SQL views can be built over files in different libraries, and they have always been handled in a case-by-case basis.
My senior System Administrator stumbled across a number of logical files which were in a different to their physical files, and wondered how many more there may be?
SQL to rescue and with a simple SQL statement I can give her that information.
Let me explain the scenario I will be using in these examples. I have a physical file, TESTFILE that exists in the library MYLIB. I built three logical files over it, each one was in a different library:
I can display the database relationship between the physical file and these objects by using the Display Database Relations command, DSPDBR:
I have been remiss in writing about the updates in the past few Technology Refreshes to the SYSDISKSTAT View and Table function. Both the View and Table function return information regarding the spinning disk and solid-state drives, SSD, on your partition(s). While, IMHO, they can be used for different purposes, they do contain the same columns.
The columns I have identified that have been added since my previous post are:
The original contents of this page have become obsolete, go to this page for up-to-date information.
This post is a two-for-one where I will explain how two of the additions that came in the latest Technology Refreshes go together.
The first is a new scalar function IFS_UNLINK, which is found in the SYSTOOLS library, that deletes IFS objects that are passed to it as a parameter. Behind the scenes it calls the unlink() C API. The scalar function returns the integer value that is returned by the C API. If it completed successfully, IFS_UNLINK returns a value of zero. If there was an error the scalar function returns an errno value.
The second addition, ERRNO_INFO, which is also found in the SYSTOOLS library, is a Table function that will return the description of the errno passed to it.
The easiest way I know to be able to show you how it works is to show an example of using it.
While it has been possible to compare the contents of two directories in the IFS it has not been, well, simple. A new addition to Db2 for i, SQL, in the latest round of Technology Refreshes, IBM i 7.5 TR4 and IBM i 7.4 TR10. Makes it a whole lot, well, easier.
The new SQL Table function, COMPARE_IFS uses the output from the Table functions IFS_OBJECT_STATISTICS and IFS_OBJECT_PRIVILEGES to compare the objects in two IFS directories to one another. It will only check the following object types:
I created two subfolders within my home directory, /home/MyDir, called subdir1 and subdir2. I uploaded some files into each one. Some were the same files, others were different files, and some files had the same names but different contents.
The SQL Create Alias statement was enhanced in the latest round of Technology Refreshes, IBM i 7.5 TR4 and IBM i 7.4 TR10. Previously when you created a SQL Alias you had to give a specific member in the DDS Physical file. Now I can use *LAST to denote the most recent member in the Physical file, and what is really cool is if I add another member to the file the Alias will now use that member!
This will be clearer when I show my examples.
First I need a DDS physical file. I called this one MULTIMBR, and it is found in my library MYLIB.
A R RMULTIMBR A FIELD 100A |
I compiled the above DDS Physical file with the following command:
01 CRTPF FILE(MYLIB/MULTIMBR) SRCFILE(MYLIB/DEVSRC) MAXMBRS(*NOMAX) |
Have you ever wondered how many different types of objects there in IBM i? And what the types of objects are?
The latest Technology Refreshes give us a SQL View that contains that information, and we can query it to answer those questions.
The SQL View is called SYSTEM_OBJECT_TYPES, and it is found in the QSYS2 library. It has three columns:
Two years ago the Send Message, SND-MSG, operation code was added to RPG. In the latest round of Technology Refreshes a number of new options have been added to SND-MSG and the Target, %TARGET, built in function.
To oversimplify it SND_MSG consists to three parts:
For example:
SND-MSG *INFO 'Message text goes here' %TARGET(*SELF) |
Last month I wrote about a way to list all of the unused virtual devices in your partition. In response to the article I received a question: "Is there a way to then delete these devices, other than manually going through? One system I just did has over 5,000 devices...."
The answer, of course, is yes there is. Before I give my final result let me go through the steps I did to create a SQL script to delete the unused virtual devices.
I can identify the virtual devices using the Object Statistics SQL Table function. I do not want to repeat myself. For the details read my previous article.
I decide to create a Common Table Expression, CTE, as it was just "neater". In the first part of the statement I would create a virtual table of all the eligible virtual devices, and in the second I would delete them. To start with I just want to make sure I gather the data I expected, therefore, my first statement did not do any deleting.
As part of the latest Technology Refreshes, IBM i 7.5 TR4 and 7.4 TR10, comes an addition to the variable and data structure definitions. It is now possible to use the constant keyword as part of those definitions to protect them from being changed.
Why would I want to do that?
There are times I want to use a variable, rather than a constant. It might be to contain a date. I would not want this to be changed, either on purpose or by accident.
Below is an example:
01 **free 02 dcl-s StartDate date inz(*sys) const ; 03 dsply StartDate ; 04 StartDate = d'2024-07-04' ; |
It has been possible to create a Microsoft compatible Excel spreadsheet using SQL since the last Technology Refreshes. In my example I demonstrated how you could do this within a RPG program.
A few months have passed and I have been asked how to do the same within a CL program. With a minor change I will show it is possible.
Let me start by showing the code for this program:
Today marks the 36th anniversary of the launch of the AS/400 server and the OS/400 operating system on June 21, 1988.
Over this time the server has evolved, with the change from CISC to RISC chips and the improvements in chip technology, to become the IBM Power server. The operating system has been rewritten several times to include the latest evolving technologies to become IBM i, which is not the only operating system that can run on a IBM Power server.
What we have today is not AS/400 and OS/400, even though everything we could with them we can do today with the modern server and operating system.
I think I did a good job describing this history last year, 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!
It has always been a bit of a bind to look up what a SQL codes means, to be able to discover what error or warning Db2 for i is giving me for a SQL statement I have executed. There is a message file, QSQLMSG, where I can convert the SQL code into a message id and then look in the message file for the message texts:
SQL code -501 = Message id SQL0501 DSPMSGD RANGE(SQL0501) MSGF(QSQLMSG) |
With the technology refreshes for IBM i 7.5 TR3 and 7.4 TR9 comes a table function where I can pass it the SQL code, and the results include all the information I desire:
Today the PTFs for the latest Technology Refreshes, IBM i 7.5 TR4 and 7.4 TR10, become available.
There are three sets of PTFs you need to download:
The numbers for these PTFs are different for the two releases. These are the numbers I have found so far:
| IBM i 7.5 TR4 | IBM i 7.4 TR10 | |
| General TR PTFs | ||
| Database PTFs | SF99950 7 | SF99704 28 |
| RPG PTFs | 5770WDS SJ00650 TGTRLS(V7R4M0): 5770WDS SJ00653 |
5770WDS SJ00635 |
I cannot wait to start trying all the new features and functions. When I do, I will write about them on this website.
Another year has passed, and this blog is now eleven years old. It is still a labor of love, and I want to continue to bring you the news about IBM i and IBM Power, and the best examples of using IBM i. I am grateful to all of you for visiting this site, and especially to you who have reached out to me with their feedback and encouragement.
On the personal side I was grateful to be recognized by IBM as an IBM Champion for Power for the fourth year.
This was a first: I was asked by the auditors for a list of virtual devices that had not been used in the past six months. In all my years of dealing with auditors this was the first time I had been asked this question.
It is not a big deal to get this information. All virtual devices are just device descriptions, object type '*DEVD', with the object attribute of 'DSPVRT'. As they are objects I can get this information from the SQL Table function OBJECT_STATISTICS.
What columns of data am I interested in?
My SQL statement to retrieve the data the auditors are interested in would be:
In an announcement made last month, May 2024, IBM announced the withdrawal of a number of their products and tools for IBM power and IBM i, effective April 30, 2025.
Included are the following IBM Rational Development Studio for i Application Development Toolset tools:
You can read the announcement here.
I have reached out to an IBM Business Partner and asked them does this mean that these tools will still work, just they we will no longer get support for issues with them. When I receive a reply from them I will post it here.
I don't think that IBM will stop these tools from working. SEU has been off support for years, but you can still use it (if you must).
All of us who use SQL have encountered errors, and when we do a SQL code is returned. This then leaves us trying to find the text associated with the SQL code to understand what had happened.
If this is in a RPG program I always recommend that you use GET DIAGNOTISTICS, which will return all you wanted to know about the message and a whole lot more too. If I am quickly writing a RPG program that will only be used one, or maybe twice, or a CL program and I get a SQL code I need to look it up to understand what happened.
There are two types of SQL codes:
All SQL codes have an equivalent IBM i message id. Basically it is 'SQL' followed by the SQL code. For example, SQL code -423 becomes message id SQL0423.
All message ids' information in IBM i are held in message files. SQL messages have their own message file QSQLMSG in the library QSYS.
If I wanted to look up the message for SQL code -423, message id SQL0423, I could use the Display Message Description command,DSPMSGD:
Last week at COMMON Americas PowerUp2024 conference I handed some special ribbons to people. You can see who I did here.
What does this mean? You will have to click on the above link to learn.
Apologies to those people I gave ribbons too that I did not have time to photograph. If you are one of those people it is not too late. Take a photograph of yourself posing with your conference badge and RPGPGM.COM-unity ribbon, like the people in the photographs, and send it to me. I will add you to the page. My email address is RPGPGM.COM@GMAIL.COM
A friend told me about the old database he had to work with. There was one file with a field that contained two pieces of information. Any leading characters, A – Z, meant one thing and the numbers within it meant something else. Unfortunately there could be any number of leading characters, the numbers could be three of four long, and there could be characters at the end. The examples he shared with me were:
His plan was to add a new numeric field to the file, and update it with the numbers extracted from this original field.
"What would be a simple way to do this with SQL?" he asked.