Pages

Wednesday, August 20, 2025

Identifying and changing object owners

Before I delete an user profile I always want to identify which objects it owns, and then transfer them to another profile or profiles. I can see which objects they own using the Work with Object by Owner command, WRKOBJOWWN. If there are a lot of objects in the results I do not find this user interface helpful.

Db2 for i offers us an alternative, the OBJECT_OWNERSHIP table function. I have written about the OBJECT_OWNERSHIP view before, it would appear I overlooked the table function.

The advantage of using a table function is that only the results for the parameters passed are found, like calling an API. With a view the rows are selected according to the selection criteria, which is like searching a file or table. In various situations, one may have an have advantages over the other. In this scenario, I only want the information for one user profile, the table function is more efficient.

The syntax for the OBJECT_OWNERSHIP is simple, as it only needs one parameter passed to it, the user profile:

01  SELECT *
02    FROM TABLE(QSYS2.OBJECT_OWNERSHIP(' < user profile > '))

In the scenario I am going to write about I want to identify all of the objects owned by the user profile OLD_USER, and then change the objects to be owned by my user profile, SIMON.

Let me start with identifying all of the non-IFS objects owned by OLD_USER.

01  SELECT OBJECT_TYPE AS "Type",
02         OBJECT_LIBRARY AS "Library",
03         OBJECT_NAME AS "Object",
04         TEXT_DESCRIPTION AS "Description"
05    FROM TABLE(QSYS2.OBJECT_OWNERSHIP('OLD_USER'))
06   WHERE PATH_NAME IS NULL
07   ORDER BY OBJECT_TYPE,OBJECT_LIBRARY,OBJECT_NAME
08   LIMIT 5

Lines 1 – 4: I think the column names describe the contents of each of these columns. I am adding column headings to these columns to make it easier to display on this page.

Line 5: I call the OBJECT_OWNERSHIP table function, passing to it the OLD_USER user profile.

Line 6: The path name column is not null for objects in the IFS. As I am interested only in non-IFS objects this column will always be null.

Line 7: I want the results to be ordered by object type, object library and name. I just prefer working with the returned results in that order.

Line 8: I have limited results to the first five, just for this article. If I was doing this for real I would want all the results.

The first five results returned were:

Type     Library  Object      Description
-------  -------  ----------  ------------------------
*BNDDIR  MYLIB    TESTBNDDIR  <NULL>
*CMD     MYLIB    WM          WRKMBRPDM proxy
*CMD     MYLIB    SP          WRKSPLF proxy
*CMD     MYLIB    GOTO3       Go to the library MYLIB3
*CMD     MYLIB    WO          WRKOBJPDM proxy

If I want to list all of IFS objects owned by OLD_USER I would use the following:

01  SELECT OBJECT_TYPE AS "Type",
02         PATH_NAME AS "Path"
03    FROM TABLE(QSYS2.OBJECT_OWNERSHIP('OLD_USER'))
04   WHERE PATH_NAME IS NOT NULL
05   ORDER BY PATH_NAME

Lines 1 and 2: I want IFS object type and the path name of the IFS object.

Line 3: I call the OBJECT_OWNERSHIP table function, passing OLD_USER to it.

Line 4: The path name column is not null for IFS objects.

Line 5: Order the results by the path name.

I have two results, see below.

Type   Path
-----  -------------------------
*DIR   /home/MyFolder
*STMF  /home/MyFolder/test.txt

Having identified all the objects that are owned by OLD_USER I need to change the owner. For non-IFS objects I will use the Change Object Owner command, CHGOBJOWN.

I always like to test I can build the command's string before I use it. My next SQL statement does that:

01  SELECT 'CHGOBJOWN OBJ(' || OBJECT_LIBRARY || '/' ||
02                                          OBJECT_NAME ||  
03                                 ') OBJTYPE(' || OBJECT_TYPE ||
04                             ') NEWOWN(SIMON)'       
05   FROM TABLE(QSYS2.OBJECT_OWNERSHIP('OLD_USER'))
06  WHERE PATH_NAME IS NULL
07  ORDER BY OBJECT_TYPE,OBJECT_LIBRARY,OBJECT_NAME
08  LIMIT 5

Lines 1 – 4: I build the CHGOBJOWN using the object library, name, and type. The double pipe ( || ) is the equivalent of the concatenate.

Line 5: I only want the non-IFS objects.

Line 7: This time I want the results ordered by object name.

The result looks like a valid CHGOBJOWN command string:

00001
----------------------------------------------------------
CHGOBJOWN OBJ(MYLIB/ADVVIEW) OBJTYPE(*FILE) NEWOWN(SIMON)
CHGOBJOWN OBJ(MYLIB/CL_DATE) OBJTYPE(*PGM) NEWOWN(SIMON)
CHGOBJOWN OBJ(MYLIB/CL_DATE) OBJTYPE(*FILE) NEWOWN(SIMON)
CHGOBJOWN OBJ(MYLIB/DEVSRC) OBJTYPE(*FILE) NEWOWN(SIMON)
CHGOBJOWN OBJ(MYLIB/DEVSRC0) OBJTYPE(*FILE) NEWOWN(SIMON)

To perform the changing of the non-IFS objects' owner my statement would be:

01  SELECT OBJECT_TYPE AS "Type",
02         OBJECT_LIBRARY AS "Library",
03         OBJECT_NAME AS "Object",
04         TEXT_DESCRIPTION AS "Description",
05         QSYS2.QCMDEXC('CHGOBJOWN OBJ(' || OBJECT_LIBRARY || '/' ||
06                                          OBJECT_NAME || 
07                                     ') OBJTYPE(' || OBJECT_TYPE ||
08                                     ') NEWOWN(SIMON)')
09            AS "Rtn code"
10    FROM TABLE(QSYS2.OBJECT_OWNERSHIP('OLD_USER'))
11   WHERE PATH_NAME IS NULL
12   ORDER BY OBJECT_TYPE,OBJECT_LIBRARY,OBJECT_NAME

Lines 1 – 4: I want to display the columns that make up the CHGOBJOWN command.

Lines 5 – 9: I am using the QCMDEXC scalar function to execute the CHGOBJOWN command. The QCMDEXC scalar function returns a value for every row of results. If the returned value is '1' then the command contained within the scalar function completed successfully. If the value is '-1' the command failed.

Line 11: Non-IFS objects only.

This statement returned many results, too many to show here. Therefore, I have taken one from each object type it changed:

Type     Library  Object      Description          Rtn code
-------  -------  ----------  -------------------  --------
*BNDDIR  MYLIB    TESTBNDDIR  <NULL>                      1
*CMD     MYLIB    WM          WRKMBRPDM proxy             1
*FILE    MYLIB    FCUST       Customer                    1
*JOBD    MYLIB    RPGPGM      Simon Hutchinson...         1
*LIB     QSYS     MYLIB       Simon Hutchinson...         1
*MODULE  MYLIB    EG068SRVP   <NULL>                      1
*MSGQ    QUSRSYS  SIMON       Simon Hutchinson...         1
*OUTQ    MYLIB    RPGPGM      <NULL>                      1
*PGM     MYLIB    DLTRCDS     Add data to FDLT...         1
*SRVPGM  MYLIB    MYVARIABLE  SQL VARIABLE MYV...         1

To change the owner for IFS objects I need to use the Change Owner command, CHGOWN.

This is my example code showing how I would format CHGOWN's command string:

01  SELECT 'CHGOWN OBJ(''' || PATH_NAME || ''') NEWOWN(SIMON)'
02    FROM TABLE(QSYS2.OBJECT_OWNERSHIP('OLD_USER'))
03   WHERE PATH_NAME IS NOT NULL
04   ORDER BY PATH_NAME

Line 1: Make the CHGOWN command strings.

The results look good:

00001
--------------------------------------------------------------------
CHGOWN OBJ('/home/RPGPGM') NEWOWN(SIMON) SYMLINK(*YES)
CHGOWN OBJ('/home/RPGPGM/test.txt') NEWOWN(SIMON) SYMLINK(*YES)

Now to change the owner for these IFS objects:

01  SELECT OBJECT_TYPE AS "Type",
02         PATH_NAME AS "Path",
03         QSYS2.QCMDEXC('CHGOWN OBJ(''' || PATH_NAME || 
04                       ''') NEWOWN(SIMON)')
05                 AS "Rtn code"
06    FROM TABLE(QSYS2.OBJECT_OWNERSHIP('OLD_USER'))
07   WHERE PATH_NAME IS NOT NULL
08   ORDER BY PATH_NAME

Lines 1 and 2: I want to display the IFS object type and the path name in the results.

Lines 3 and 4: Using the QCMDEXC scalar function to execute the CHGOWN command string.

As the return code is '1' for both rows, the IFS objects have had their owner changed.

00001                                                            Rtn code
---------------------------------------------------------------  --------
CHGOWN OBJ('/home/RPGPGM') NEWOWN(SIMON) SYMLINK(*YES)                  1
CHGOWN OBJ('/home/RPGPGM/test.txt') NEWOWN(SIMON) SYMLINK(*YES)         1

To make the changes the way I have described above is too complicated to do more than once. Therefore, I have made a RPG program to change the owner.

01  **free
02  ctl-opt main(Main) option(*srcstmt) dftactgrp(*no) ccsid(*char : 1208) ;

03  dcl-proc Main ;
04    dcl-s OldUser varchar(10) inz('OLD_USER') ;
05    dcl-s NewUser like(OldUser) inz('SIMON') ;

06    exec sql DROP TABLE IF EXISTS QTEMP.MY_CHANGE_OWNERS ;

07    exec sql CREATE TABLE QTEMP.MY_CHANGE_OWNERS
08               FOR SYSTEM NAME "MYCHGOWN"
09             (OBJECT VARCHAR(100),
10              RETURN_CODE NUMERIC(1)) ;

11    exec sql
12      INSERT INTO MY_CHANGE_OWNERS
13        SELECT OBJECT_LIBRARY concat '/' concat OBJECT_NAME concat
14                 ' (' concat OBJECT_TYPE concat ')',
15               QSYS2.QCMDEXC(
16                 'CHGOBJOWN OBJ(' concat OBJECT_LIBRARY concat
17                               '/' concat OBJECT_NAME concat
18                          ') OBJTYPE(' concat OBJECT_TYPE concat
19                          ') NEWOWN( ' concat :NewUser concat ')')
20          FROM TABLE(QSYS2.OBJECT_OWNERSHIP(:OldUser))
21         WHERE PATH_NAME IS NULL
22         ORDER BY OBJECT_TYPE ;

23    exec sql
24      INSERT INTO MY_CHANGE_OWNERS
25        SELECT PATH_NAME,
26               QSYS2.QCMDEXC(
27                 'CHGOWN OBJ(''' concat PATH_NAME concat ''') ' concat
28                         'NEWOWN(' concat :NewUser concat ') ')
29          FROM TABLE(QSYS2.OBJECT_OWNERSHIP(:OldUser))
30         WHERE PATH_NAME IS NOT NULL
31         ORDER BY PATH_NAME ;

32  on-exit ;
33  end-proc ;

Line 2: I am using a Main procedure in this program, to not use the RPG cycle. I also have my favorite control option, *SRCSTMT, and I am not running the program in the default activation group. This is something I have only used a few times, the CCSID. I am using it here to convert the program's default CCSID to 1208. Why am I doing that? This partition's default CCSID is 65535. This is not compatible with the path name returned by OBJECT_OWNERSHIP, which is CCSID 1208. By using CCSID to change the program's character set to 1208 this is no longer an issue.

Line 3: Start of the Main procedure.

Line 4 and 5: The two variables contain the two user profiles. If this was not an example I would have passed the two user names to this program.

Line 6: I am deleting the output file, if it exists, from the library QTEMP.

Lines 7 - 10: As the QCMDEXC scalar function returns a return code I need to capture that into a table, giving me the ability to check if all the ownership changes were successful. I have given the table a short system name, MYCHOWN, to make it easy to find with IBM i commands. The file has just two columns:

  1. OBJECT:  This field will contain the object library, name, and type for non-IFS objects, and the path name for IFS objects.
  2. RETURN_CODE:  This will contain the return code from QCMDEXC.

Lines 11 – 22: This statement inserts the data from changing the object ownership for the non-IFS objects.

Lines 13 and 14: I am making data for the OBJECT column. For non-IFS objects I am using the following format:

 < object library >/< object name > ( < object type > )

Lines 15 – 19: This is the QCMDEXC scalar function to change the objects' owner. I was disappointed to find that I could not use the double pipe ( || ) to denote concatenation, therefore, I have had to use 'concat' instead.

Lines 23 – 31: This is the statement that changes the owner of the IFS objects.

Line 29: The tripe apostrophes ( ''' ) are needed so that a single apostrophe ( ' ) will surround the path name.

After running the program I can look to see what changed:

01  SELECT * FROM QTEMP.MY_CHANGE_OWNERS LIMIT 5

This returns:

OBJECT                      RETURN_CODE
--------------------------  -----------
MYLIB/TESTBNDDIR (*BNDDIR)            1
MYLIB/WM (*CMD)                       1
MYLIB/SP (*CMD)                       1
MYLIB/GOTO3 (*CMD)                    1
MYLIB/WO (*CMD)                       1

/home/MyFolder                        1
/home/MyFolder/test.txt               1

If I wanted to check for anything that was not changed I would use the following statement:

01  SELECT * FROM QTEMP.MY_CHANGE_OWNERS WHERE RETURN_CODE = -1

With my work there were no errors:

OBJECT                      RETURN_CODE
--------------------------  -----------
 

I hope you will find this program as helpful as I do. I know I will be using this in the next weeks.

 

This article was written for IBM i 7.6, and should work for some earlier releases too.

Monday, August 18, 2025

Get more RPGPGM.COM in your Google Search

I get quiet few communications from readers of this website that they have bookmarked RPGPGM.COM in their browser, or they have made it their home page. I am very flattered that people appreciate my work enough to do that.

If you are someone who finds the content of this website useful, and want to have a way to list results from it more prominently in your Google search experience, Google has added a new feature to help: Google Preferred Sources.

At the time of publication this feature is available in the USA and India, for Google searches in English. I am keeping my fingers crossed that it will eventually be rolled out globally.

What does it do? This is what Google says in their blog post announcing the release of "Preferred sources":

When you select your preferred sources, you'll start to see more of their articles prominently displayed within Top Stories, when those sources have published fresh and relevant content for your search.

This is available for Desktop, Android, and iPhone and iPad.

Rather than follow the steps described in the articles to activate Preferred sources, you can just click on the button below.

You must be signed into your Google Account before clicking.

More information can be found:

Wednesday, August 13, 2025

Prevent my user profile being used when others submit job

It is possible to submit jobs to batch that will run for another user profile by using the User parameter in the Submit Job command, SBMJOB. For example:

  SBMJOB CMD(DLTF FILE(MYLIB/INVHIST)) JOB(TEST) USER(SIMON1)

The above shows that the current job is trying to submit a job to run with the SIMON1 user profile. I would consider to be a security risk. If SIMON1 has more authority than the current user, let's say that is NOTSIMON, then NOTSIMON can submit jobs that perform tasks they are not authorized to do, or do something bad that they will not be blamed for.

Fortunately in most cases NOTSIMON would receive the message:

 SBMJOB CMD(DLTF FILE(MYLIB/INVHIST)) JOB(TEST) USER(SIMON1)
 Not authorized to user profile SIMON1.

I have encountered various companies where all the SBMJOB commands in their programs would use a "special" user profile for batch jobs, that had more authority than the average user. In this scenario when NOTSIMON ran a program that SBMJOB, and I looked at the calling job's job log I would see something like:

Monday, August 11, 2025

Useful IBM Support document for data type mapping

Someone sent me this link to an IBM Support document that maps DDS data types to SQL and ODBC data types. And I decided to share it with you as I find this information both interesting and useful.

DDS/SQL Data Type Mappings - IBM Support

Friday, August 8, 2025

New version of ACS, 1.1.9.9, is now available

Yesterday I discovered that a new version of ACS became available for download.

Wednesday, August 6, 2025

Giving the ability to see all of the objects on the system

I understand why IBM i developers should not have all object authority, *ALLOBJ, but at times I am frustrated by my inability to find objects in my partition. I do not want to do anything to them, just know that they exist. I have to find someone with a security office equivalent user profile and ask them to do a search for me.

I have found a way that this frustration can be removed. It will work on all partitions that are IBM i 7.5 or higher. I think what I am going to describe is included in the initial release. As none of the partition I have access to are running just base 7.5 I cannot check that this did not come in a Technology Refresh, TR.

Function Usages are way to be granted access to perform certain higher authorization functions, without being given that higher authorization. I was going through IBM's documentation about them when I came across:

Wednesday, July 30, 2025

Finding source members with the same name

How would I find if a source member with the same name is found in more than one source file? If there is, which one was modified most recently? I am sure those are questions many of us have asked ourselves. How could we can make this easy for ourselves to get this information? Fortunately, Db2 for i has everything I need to do it.

I start with the SYSMEMBERSTAT View, it was introduced a couple of Technology Refreshes ago, IBM i 7.5 TR4 and 7.4 TR10, and it is used in place of SYSPARTITIONSTAT when I need information about members.

What are the columns I am interested in:

Monday, July 28, 2025

SDA is not available in IBM i 7.6

The announcement of IBM i 7.6 included notice that the following application development tool set commands were no longer available:

  • STRSDA:  Start Screen Design Aid
  • STRRLU:  Start Report Layout Utility
  • STRAPF:  Start Advanced printer Function
  • MRGFORMD:  Merge Form Description
  • STRCGU:  Start Character Generator Utility
  • CPYIGCSRT:  Copy DBCS Master Sort Table
  • MRGFORMD:  Merge Form Description

The web page with this information is here.

In the past week there were rumors circulating in social media that this is not true, and it is still possible to use the STRSDA command in 7.6 .

Wednesday, July 23, 2025

Retrieve the SQL statements from multiple QM queries

The germ for this post came from a message I received:

Is there any way to retrieve sources for all QMQRY objects available inside a library in one go? My objective is, there are 100's of QMQRY objects(SQL type) inside a library but they don't have predefined source members. I need search for particular string inside all those SQL queries used inside the QMQRY objects.

I decided to work out a way I could do this.

I did not create "100's" of Query Management, QM, queries as what will work for two will work for many more too.

It is possible to retrieve the SQL statement from a QM query object by use of the Retrieve Query Management Query command, RTVQMQRYM. That command copies the retrieved statement into a source member.

Monday, July 21, 2025

CPW ratings for Power11 servers

When the IBM Power11 servers were announced, July 8, several people contacted me if I had the CPW rating for the new servers.

What is CPW? It is a number that is frequently mentioned, but I have never seen its definition. After some digging I found the following:

The CPW, Commercial Processing Workload, rating provides a measure to show how on-line transactions processing, OLTP, workloads perform on systems that run IBM i. The CPW rating is built using workloads that can utilize the full processing power of the system.

Below is a summary of the Power11 CPW. I have included the top of range Power10 equivalent too, its column is gray.

Wednesday, July 16, 2025

Moving one person's spool files to a different output queue

I was asked if there was a simple way to check an output queue, and if there is a spool file of a certain name for one user, to move it to another output queue.

Fortunately this is not as complicated as it sounds as I can use a SQL Table function to retrieve a list of those spool files, and a scalar function to move the spool file.

In this example I am going to be the user, my profile is SIMON, and whenever I find a spool file QPQUPRFIL in the output queue MYOUTQ I want to move it to the output queue OUTQ2.

First I need to produce a list of eligible spool files. Here I can use the SPOOLED_FILE_INFO SQL Table function:

Monday, July 14, 2025

Some Power11 performance and efficiency information

I had been unable to find any information comparing the performance and efficiency of the new IBM Power11 to previous IBM Power servers. I reached out for this information to people I know and they shared with me an IBM document, whose information I have copied into this post.

Before we get started I need to briefly explain what rPerf is. It is a method to approximate the difference in performance between two Power servers. rPerf is only for AIX. For IBM i performance CPW is used. I found an IBM page explaining what rPerf is here.

All of the quotes I giving below are from the document I received. They are divided into Performance and IT Efficiency. While the document did not group them together I am doing so. I am not including the disclaimers with the quotes, as that will make it difficult to read. The disclaimers can be found at the bottom of this post.

Tuesday, July 8, 2025

IBM Power11 chips and servers are announced

Today is the day! The new IBM Power servers using the new Power11 chips have been announced.

Over the years IBM has developed increasingly more power Power, chips that have been significant improvements from the previous one.

Monday, July 7, 2025

New Modernization Techniques Redbook from IBM

On May 20, 2025, IBM published a Redbook with the title "Modernization Techniques for IBM Power".

The abstract that accompanies the Redbook on their website states:

This IBM Redbook offers a high-level overview of modernization, including key concepts and terminology to guide your modernization journey. It explores the components and architectural layers of the IBM Power ecosystem, demonstrating how they create an ideal platform for running mission-critical applications in today's world. The content is designed for business leaders, architects, and application developers.

It devotes a chapter, chapter 9, to the IBM i. I think it is worth downloading this document even if you only read chapter 9.

You can download this Redbook from the link here.

Wednesday, July 2, 2025

SQL table function to list all imports for ILE program or service program

This is one of the IBM i enhancements that was released in version 7.6, but not in 7.5 TR6. This new table function, PROGRAM_RESOLVED_IMPORTS, allows me to get a list of all the imports for an ILE program or service program.

This Table function has four parameters:

  1. PROGRAM_LIBRARY:  Library that contains the ILE program or service program. "*LIBL" is not supported.
  2. PROGRAM_NAME:  Name of the ILE program or service program.
  3. OBJECT_TYPE*PGM for ILE program, *SRVPGM for ILE service program.
  4. IGNORE_ERRORS:  Optional. NO when an error is encounter an error is returned. YES a warning is returned, this is the default.

This looks like:

Wednesday, June 25, 2025

How to identify flat files

The question was is there an easy way to identify "flat files" without having to use the DSPFD command. The answer, of course, is "Yes".

The questioner explained that a "flat file" was a file that was generated without the use of DDS or DDL. In other words, just with the Create Physical File command, CRTPF. For example:

  CRTPF FILE(MYLIB/FLATFILE) RCDLEN(100)

The questioner was finding he could identify these "flat files" with the Display File Description command, DSPFD, like this:

  DSPFD FILE(MYLIB/FLATFILE)

Tuesday, June 24, 2025

Michiganders join the RPGPGM.COM-unity

At the MiTec conference earlier this month more people joined 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.

Friday, June 20, 2025

Happy birthday to IBM Power and IBM i

Tomorrow, Saturday June 21 2025, is the 37th anniversary of when the IBM AS/400 was first announced. You can watch the video of the announcement in the UK here.

What a wonderful journey we have all been on all these years as the AS/400 (1988 – 2000) begat the iSeries (2000 – 2006), which in turn gave way to the System i (2006 – 2008), and finally IBM Power server running the IBM i operating system (2008 – Present). All the time being IBM's premier server and operating system providing modern functionality, stability and robustness to their customers.

Today the AS/400 looks dated, which it is. But IBM Power can hold its own compared to any comparable business system.

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!

Wednesday, June 18, 2025

IBM makes it easier to delete old journal receivers

Old journal receivers can quickly become one of the big wasters of storage on any IBM i partition. I have written before how to identify receivers I think can be deleted. In IBM i 7.6 and 7.5 TR6 comes a new SQL procedure that makes it easy to delete the old receivers.

I need to define what I mean by "old receivers". An "old" journal receiver has been detached from its journal and saved. In my mind if it is detached and been saved, I can delete it. If I need the information contained within it, I can restore it.

I would not use the new procedure to look if I have old receivers, I would use the JOURNAL_RECEIVER_INFO View to get to the information I would want, before using the new procedure.

For example, if I want to find the ten oldest journal on my partition, I would use the following statement:

Thursday, June 12, 2025

RPGPGM.COM-unity at PowerUp2025

Last month I attended COMMON PowerUp2025. It was wonderful to be able to catch up with all my friends in the IBM i community, and hand out RPGPGM.COM-unity ribbons. You can see who I gave them too 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 and I will add you.

What is RPGPGM.COM-unity? You can click on this link to learn what it means.

Wednesday, June 11, 2025

A dozen years of RPGPGM.COM

Every year my mind is boggled each time I reach the anniversary of this blog. It is incredible to me that I have been writing for a dozen years about everything I find interesting with IBM i, and that you take your time to read it. Your encouragement is very much appreciated.

What has happened in the past twelve months?

Wednesday, June 4, 2025

Simple way to check if a file exists in the IFS

Someone reached out to me asking for a straightforward way to check if a file exists in a particular folder in the IFS. They included their program, which, IMHO, was overly complicated. This will be another example of providing a simple, easy to understand, solution using SQL and RPG.

In my example I will be checking if a file exists, and the path for the file will be passed as a parameter from a RPG program. I also want to make the part that does the checking be a procedure. As one procedure can be called by multiple programs, as there could be other files in the future I want to check on.

I am going to create this procedure in a module that I will then bind into a RPG program. In the "real" world the procedure would be added to a service program, so that the logic within the procedure could be changed without having to recreate all the programs that use it.

Wednesday, May 28, 2025

Increment a number held in a character field

The title is simplistic for what this post is really about. The question that was asked was:

I have a character field in a DDS physical file that is 20 characters long. There can be various numbers in that field of any length. I want to retrieve the value from the field, increment it by one, and update the field in the file.

If all the "numbers" in the field in the file were 20 characters long it would be easy, but as the "numbers" can be of different lengths it makes the problem more interesting.

First I need a file for this 20 character field. I decided to call it TESTFILE and the DDS code for it is:

01 A                                      UNIQUE
02 A          R TESTFILER
03 A            KEY            5A
04 A            FIELD1        20A
05 A          K KEY

Wednesday, May 21, 2025

SQL scalar function to break apart IFS path name

As part of the new new release, IBM i 7.6, and the latest Technology Refresh for IBM i 7.5, TR6, I can break apart an IFS path name into its parts:

  • File extension
  • File name
  • File prefix (file name without the file extension)
  • Path prefix (path without the file name)

This new SQL scalar function is called IFS_PATH, and it is found in the library SYSTOOLS.

Before I start with my examples I need to check if there are objects in the IFS I can use. This is a simple task using IFS_OBJECT_STATISTICS:

Wednesday, May 14, 2025

Selecting updated and deleted values when using SQL

Have you ever wanted to confirm that a SQL Update or Delete statement ran the way you expected?

In the new release, IBM i 7.6, I can do this using what IBM describes as:

  1. Selecting updated values
  2. Selecting deleted values

This was not added to the latest Technology Refresh for IBM i 7.5, TR6.

Tuesday, May 13, 2025

SELF default changes

While playing with the new release, IBM i 7.6, I noticed that the default for the SELF, SQL Error Logging Facility, SELFCODES global variable has changed.

When it first came out, IBM i 7.5 TR1 and 7.4 TR7, the default for the SELFCODES was null.

With IBM i 7.6 and 7.5 TR6 the default is '*NONE'.

Why the difference?

Both null and '*NONE' are acceptable as the default. The difference is that '*NONE' will stop SELF monitoring immediately. Null does not, only when new SQL 'jobs' start.

 CREATE OR REPLACE VARIABLE SYSIBMADM.SELFCODES VARCHAR(256) DEFAULT '*NONE' ;

This is another good enhancement to SELF.

Wednesday, May 7, 2025

Where on the subfile is the cursor

I was asked how is it possible to determine where the cursor is on a subfile when the F4 key is pressed.

I have written about the parts needed to achieve this in separate posts, and I thought it would be a good idea to put it all together into one new post.

What I would need is:

  1. A subfile program
  2. Determine when the F4 is pressed
  3. Retrieve the record format name
  4. Retrieve subfile relative record number, RRN, of the record the cursor is on
  5. Retrieve the name of the field the cursor is on in the subfile

First off, I need a subfile display file, a simple one. The code for a minimalist subfile could be:

Monday, May 5, 2025

IBM Performance documents updated for Spring 2025

Steve Will, IBM i Chief Technology Officer and Chief Architect, announced this week that new versions of the following documents had been updated by the IBM performance experts:

  • IBM i on Power - Performance FAQ – see here
  • IBM Power10 performance optimization for IBM i – see here

IMHO I think they both are worth checking out.

Wednesday, April 30, 2025

Report of the success of the nightly saves

My manager asked me if I could provide him with a report of all the libraries that were backed up as part of the night process job, NBACKUP. "You know what I mean: one line for each library" was his final statement to me.

I did want to browse a different job log for each day. I wanted to design something that was simple. I decided to use the HISTORY_LOG_INFO table function. I could select results using:

  • Starting timestamp of 1 day (24 hours) ago
  • I know the job name

I started with this SQL statement:

Wednesday, April 23, 2025

Quickest way to determine which day of the week today is

In a program I wrote recently I needed to return which day of the week today is. My first thought was to use SQL, as it would only be one line of code. But is that the fastest way to return today's day name?

I thought about writing my own RPG routine to calculate the day name, but decided to use Google to see if there was a better example. I found an article by Rafael Victoria-Pereira for MC Press from 2015 that gave what I think is an efficient way to do this, you can read his article here.

Using his example I refactored the RPG code up to 2025 standards and created this procedure:

Monday, April 21, 2025

Useful manuals

I have few more recent IBM manuals that I wanted to share with you, including the one I have been waiting for since the IBM i 7.6 announcement:

I hope you find these links interesting and useful.

Friday, April 18, 2025

IBM i 7.6 available today

The new release of the IBM i operating system, IBM i 7.6, is available today. While 7.6 will steal the headlines today, there is a Technology Refresh for IBM i 7.5, TR6, that is also available.

If you have missed all the news, you can find out what is in the new release and the new TR, here.

Wednesday, April 16, 2025

LISTAGG scalar function can return distinct results

I did not realize it had been so long since I first wrote about the LISTAGG scalar function. I mentioned very briefly in that article something that became very useful to me just a short while ago.

The LISTAGG scalar function aggregates the results from a one or more rows into a single new column, separated by a character of my choice.

In my scenario I have a table, TESTTABLE, that contains a column of colors, COLOR:

01  SELECT COLOR,COUNT(*) "Count" 
02    FROM TESTTABLE
03   GROUP BY COLOR

Tuesday, April 15, 2025

Award for this website

I was notified yesterday that this website, RPGPGM.COM, had won an award.

Anuj Agarwal, founder of FeedSpot, informed me of this:

I would like to personally congratulate you as your blog Rpgpgm.Com has been selected by our panelist as one of the Top 45 IBM Blogs on the web.

https://bloggers.feedspot.com/ibm_blogs/

This is the most comprehensive list of Top 45 IBM Blogs on the internet and I'm honored to have you as part of this!

RPGPGM.COM is ranked in fifth place.

Thank you Anuj, and the panel at FeedSpot, for this honor.

Monday, April 14, 2025

IBM i technical resource

Last week I found a web page I want to share with you all. It is called the "IBM i technical resources roadmap", and is curated by Robert Cecco and Scott Forstie.

The page provides links to all kinds of useful IBM resources about IBM i, including this website.

I think this is such a good resource that I am adding it to the "Links to useful sites", which is found in the right column of all the pages and posts in this website.

You can also reach that web page by using the link here.

Thursday, April 10, 2025

ACS 1.1.9.8 out now

 

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

 

Wednesday, April 9, 2025

More information about IBM i 7.6

I found some more interesting information about IBM i 7.6, the new release that was announced yesterday.

The first is what IBM called a "data sheet". It is only three pages long an gives a very high level insight into the new release. This might be something to share with your superior, so that they have an idea of what is in 7.6.

Tuesday, April 8, 2025

Version 7.6 announced

The awaited IBM iNext was announced today as IBM i 7.6. Also announced is a new Technology Refresh for IBM i 7.5 TR6.

IBM i 7.6 will only run on IBM Power 10 servers, and, I presume, on the Power 11 too whenever that is announced.

It would appear to be the end of TRs for IBM i 7.4. IBM has said they will provide fixes for 7.4, but nothing new. If you are on 7.4 on a Power 10 my recommendation it is time to start making plans to move up to at least 7.5.

The new release, IBM i 7.6 and the PTFs for IBM i 7.5 TR 6 will be available April 18, 2025.

Every release in recent years has a "theme". The "theme" of this release is security, with many of the new features and functions being to do with keeping your data and server secure.

Wednesday, April 2, 2025

Easier way to check if member exists

Amended April 23, 2025:  To use CHKOBJ command.


I was asked: What is the easiest way to check if a member exists in a file?

They had been recommended to perform the check in a CL program, but wondered if there was a way to do the same in a RPG program.

In a "pre-SQL" world I would have used the Check Object command, CHKOBJ, in a CL program to check if the member exists. My program would look something like this:

Thursday, March 27, 2025

One thousand posts

Yesterday another milestone was reached for this website, its one thousandth post!

Personally I think this is a remarkable achievement, to have been able to provide you with information about IBM Power and IBM i for the past 11 years, 9 months, and 17 days.

I want to thank all of you for helping make this possible. Without your encouragement, via social media, email or in-person, this blog would not have become what it has.

Wednesday, March 26, 2025

For RPG program where can I see the path name?

This post is in answer to a question I was asked:

We have compiled an RPG program from an IFS location but we are unable to determine the source of the compilation. We attempted the DSPPGM command and APIs, but they only show the member location when the program is compiled from a library, not for the IFS.

All modern RPG programs contain modules, even if I used the CRTBDNRPG command to create the program object. In this example I have a program TESTRPG, that is in the library MYLIB, that I created using CRTBDNRPG.

Wednesday, March 19, 2025

MTI_INFO now includes member name

As part of the latest round of Technology Refreshes, IBM i 7.5 TR5 and 7.4 TR11, the MTI_INFO Table function was enhanced with the addition of the member or Table partition name the MTI was created over. I have to admit I was surprised this was not included when the Table function was first introduced.

The new column is called TABLE_PARTITION, it is the third column in the results, and is a VARCHAR of a maximum of 128 characters.

If I just wanted to view all of the MTIs in my partition I would use the following statement:

01  SELECT TABLE_SCHEMA,TABLE_NAME,FILE_NAME,
02         TABLE_PARTITION
03    FROM TABLE(QSYS2.MTI_INFO())
04   ORDER BY 1,2
05   LIMIT 5

Wednesday, March 12, 2025

New parameter added to Audit Data Mart procedure

Audit Data Marts were added to IBM i in the previous round of TRs, and in the latest round of TRs comes what I can think is a significant improvement.

IMHO Audit Data Marts are a big improvement in the way I can manage Audit journal data. It allows me to copy the data from the QAUDJRN journal into a series of Audit Data Mart Tables. Each Audit Data Mart Table contains the data for one particular audit journal entry types. I am not going to go into more detail about creating Audit Data Marts as I wrote, in detail, about how to use them in my post: Audit Journal Data Mart in October of last year.

An additional parameter was added to the procedure MANAGE_AUDIT_JOURNAL_DATA_MART that makes it possible to select the information when I create or reload the Audit Data Mart. It is the DATA_MART_FILTER parameter. With this I can add additional selection criteria, in SQL format, to the MANAGE_AUDIT_JOURNAL_DATA_MART statement. Up to a thousand characters can be entered. Be warned the statement entered here is not validated, therefore, if what you enter is not valid it will cause an SQL syntax error when the statement is executed.

Monday, March 10, 2025

RPGPGM.COM-unity reaches India

I handed out a lot of RPGPGM.COM-unity ribbons at last month's COMMON India conference in Pune.

You can see who I gave them to here.

And you can learn about what the RPGPGM.COM-unity is here.

Wednesday, March 5, 2025

Deleting all spool files with a certain form type

The idea for this post came from a question I was asked in a presentation last month. The more I think about the solution I developed I am of the opinion that many of you might find this useful.

The question was: Is there a way, using SQL, to delete all the spool files that have a particular form type?

I have been using the DELETE_OLD_SPOOLED_FILES SQL procedure to delete old spool files on my partition. Alas, the Procedure does not have a parameter for form type.

Another idea sprung to mind, I could use the OUTPUT_QUEUE_ENTRIES_BASIC SQL View to select the spool files I want to delete, and do the deleting in the same statement using the appropriate CL command in the QCMDEXC Scalar function. I am sure some of you are wondering why I picked OUTPUT_QUEUE_ENTRIES_BASIC View rather than OUTPUT_QUEUE_ENTRIES View. The "basic" version has less columns than the "full" version, which makes it faster to return results.

The crucial part of my design is that I must get the selection criteria correct when I use the OUTPUT_QUEUE_ENTRIES_BASIC.

Tuesday, March 4, 2025

Inaugural Common India a great success

Last week, Monday 24 – Wednesday 26 February, was a truly historic event. The first COMMON event in India. I was lucky to be invited to make some presentations at the event.

I was most impressed by the attendees' thirst for knowledge for the latest about the IBM Power server and the IBM i operating system. While helping at the registration desk I was surprised how many people recognized me and the other USA based IBM Champions who were attending the conference. I soon lost count of the number of times I was asked to appear in someone's selfie. There was a range of experience there, from people who has been working with IBM i for decades, to students who want to learn about whether this was an environment they could have a career in.


IBM Champions: Gajendar Tyagi, Simon Hutchinson, Ranga Deshpande,
Marina Schwenk, Nirmal Kumar Khatri, Charlie Guarino