Wednesday, June 29, 2022

Change to Create Table statement to stop accidental deletion of the Table

stop table delete with restrict on drop

It is always a danger that someone will accidentally drop (delete) the wrong file, not maliciously just accidentally.

02    (COLUMN1 CHAR(1)) ;


Statement ran successfully

Within the new release of IBM i 7.5 is an addition to the Create Table statement that can prevent this. This does appear to be only in 7.5, I cannot find a mention of this in the documentation for IBM i 7.4 TR6.

This addition to the Create Table statement is: WITH RESTRICT ON DROP

Tuesday, June 28, 2022

Moving content of data structure into an array

data strucutre subfields in array

I have been asked this same question a few times during the last couple of weeks: What is easiest way to move data from a data structure's subfields into an array?

Asking some other people I know how they would do it, several of them did not know of this simple way.

Here is the start of my code:

Wednesday, June 22, 2022

Removing duplicate characters from a string

rpg scan replace %scanrpl

I was asked how I would remove duplicate characters from a string just using RPG. I has been some time since I came up with an all RPG solution, so I accepted the challenge.

The string of data is in a file, TESTFILE, in the field F1. The sample string I used in F1 was:



I have more than one of these characters in the string: a, b, c, and d. And only one of these: f, g, h, i, j, k, and l. My goal is for a single character of all of these.

Tuesday, June 21, 2022

Extra parameters added to Call command to prevent decimal data error

new parameters added to call command

Anyone who has programmed on IBM i has experienced this frustration. I want to call a CL program from a command line. I type:

CALL CL_PGM ('A' 12)

And I am presented with the following error:

MCH1202 received by procedure CL_PGM. (C D I R)

For the beginner this is cryptic. When I drill down into the job log I can see what really happened:

Friday, June 17, 2022

More fun with the Boolean data type

using unknown instead of null in sql

IBM i 7.5 introduces a synonym for NULL, UNKNOWN. Having performed a few tests, I can confirm I can use it just as I would NULL.

For example I can use it when inserting data into a SQL Table. The first column in this Table is a Boolean data type, the second is character.


I have used a multiple rows insert to insert six rows with one Insert statement. Notice how I have used UNKNOWN in the data for the last row to be inserted.

I check the contents of this file with the following SQL statement:

Wednesday, June 15, 2022

New data type for SQL - Boolean

sql boolean data type

A new data type has been added to Db2 for i's Data Definition Language, DDL, as part of IBM i 7.5, but was not added to IBM i 7.4 TR6. A Boolean data type.

Being Boolean it should only contain two values, but this can contain three possible values:

  1. True
  2. False
  3. Null – when this contains no data

I love this. For many, many years I have been creating "Boolean" like columns or fields to contain a sorta kinda true/false logic. But as the columns/fields were either character or numeric they could contain any valid value of that data type. Now I can have a column to denote things like:

  • Item is in-stock
  • Item is in a backlog status
  • Certain information has been provided or accompanies whatever

You get the idea.

A Boolean column can be defined in a SQL table as simply as:

Friday, June 10, 2022

9 years and counting!

Wow! It was nine years ago I wrote the first post of this blog. Here I am almost 800 posts later and I am still writing. Fortunately, IBM has "cooperated" and has released a lot of really interesting and exciting things in this time, giving me lots of material to write about.

This where I pick my favorite five posts of the past 12 months, which is always a struggle as there is always so many things to consider:

Wednesday, June 8, 2022

New RPG op-code to send messages to job log

rpg snd-msg %msg %target

Another addition to the RPG programming language with IBM i 7.5 and 7.4 TR6 is an operation code that allows me to write to the current job's job log.

I have been using the SQL procedure LPRINTF to write to the job log since 2019. Now I have the ability to do so with native RPG.

The new operation code is called Send Message, SND-MSG, and is accompanied by two new optional Built in Functions, BiF: %MSG and %TARGET.

Just how simple it is to write to job log I will demonstrate below. Let me start with my first example program's source code:

01  **free
02  dcl-s Text varchar(50) inz('Second message') ;

03  snd-msg 'First message' ;

04  snd-msg Text ;

05  snd-msg *INFO 'Third message' ;

Wednesday, June 1, 2022

New SQL built in function allows for validating data types

sql try_cast cast

I am sure many of us have found that when we try and cast, in SQL, from one data type to another the “casting" will fail. This is especially true when moving character representations of numbers or dates to a decimal or date type. With the new release IBM i 7.5 and 7.4 TR6 there is a new SQL built in function that allows for the testing of a cast before it is performed.

Before I start explaining this new built in function let me define the table I will be using, TESTTABLE:

 COLUMN2 CHAR(10)) ;

It has two columns I will be using in this post. For now, I am only interested in the contents of the column COLUMN1, which I can see using the following SQL statement:

Monday, May 30, 2022

Updated IBM i roadmap for 2022

With IBM i 7.5 being released IBM has updated its IBM i Roadmap image:

Click on image to see larger version

I find interesting:

Wednesday, May 25, 2022

Start testing the new date rule

1970 rule window dates

Since the beginning of time on OS/400, the beloved ancestor of IBM i, six long dates have always used the "1940 rule" to determine the century.

If Year >= 40 then Century = 19
If Year < 40 then Century = 20

2039 is not that far in the future, only 17 years away, IBM has developed a replacement rule, "1970 rule".

If Year >= 70 then Century = 19
If Year < 70 then Century = 20

As part of the IBM i 7.5 announcement IBM has said that the new rule will be implemented in a future release or Technology Refresh.

IBM is giving a foretaste of the new rule in 7.5. I used the word "foretaste" as the "1940 rule" has not been replaced, but the new rule can be implemented on top of it, if you so desire. The rule can either be applied to your entire system, which I do NOT recommend, or it can be implemented just to the current job.

Tuesday, May 24, 2022

TR PTFs for 7.4 and 7.3 available today

ibmi v7r4 tr6 v7r3 tr12 ptf

The PTFS for the two new Technology Refreshes:

  • IBM i 7.4 TR6
  • IBM i 7.3 TR12

Are now able for download as PTFs from IBM Support website.

Start planning to download these into a test partition, and get ready for the testing process to validate your applications with these TRs. Don't forget the database (SQL) and RPG PTFs come separately.

<whispers> It might be time to get the latest CUM PTFs to ensure you get all the available PTFs for your release </whispers>

Wednesday, May 18, 2022

New way in RPG to monitor for errors

on-excp in monitor group

In RPG we have been able to monitor for errors, using a Monitor group, for years. Until IBM i 7.5 and 7.4 TR6 we could only monitor for program and file status codes. I have always preferred CL’s MONMSG that allowed me to monitor by message id instead.

As part of the new release, or new TR for 7.4, there is a new RPG operation code, ON-EXCP, that can be used within a Monitor group to monitor for a specific error message or messages. The syntax is very similar to that of the ON-ERROR operation code:

monitor ;
on-excp 'CPF1234' : 'RNX1234' : 'RNQ1234' ;
  Do something else to mitigate the error
endmon ;

I am going to give some examples below so you can understand how ON-EXCP works. Here is my RPG program’s source code:

Tuesday, May 17, 2022

New TR for IBM i 7.3

ibmi v7r3 technology refresh tr12

I am certain in all the videos of the announcements for IBM i 7.5 and 7.4 TR6 we were told there would be no more Technology Refreshes, TR, for 7.3.

Well, it appears that there is to be another Technology Refresh for 7.3, TR12. I found it on the Technology Refresh information page for 7.3 here.

It is available on the same day, May 24, that 7.4 TR6 is.

What is available in this TR for 7.3?

Wednesday, May 11, 2022

Making a list of journal receivers with the detach date

journal receiver information from api

I wanted to write a program that would delete old journal receivers, ones detached from the journal and more than a certain number of days old (to be determined later). I can see the detach date when I use the Display Journal Receiver Attributes command, DSPJRNRCVA, but I can only do this for one receiver at a time, and the command only allows for display or print. Looking at my chart of SQL Views and Table functions I found that is not one for Journal Receivers. Alas, this leaves me having to use an API to get to this information.

The information I desire is:

  • Journal name and library
  • Journal receiver name and library
  • Attach date (timestamp) of the receiver
  • Detach date (timestamp) for the receiver

There is an API that will return to me the information. It comes with the long name: QjoRtvJrnReceiverInformation. Before I go into the example of using this API let me get started with how I made a list of all the receivers in a library. Here I can use a SQL Table function, OBJECT_STATISTICS. I use the following statement to display a list of all the receivers in a library.


Tuesday, May 3, 2022

New version 7.5 announced

ibmi 7.5 and 7.4 tr6

Most of us knew it was coming, so there is no surprise that a new release for IBM i has been announced. The new release, 7.5, accompanies a Technology Refresh update for version 7.4 TR6. As only two versions of the IBM i are supported with TRs it does also mean the end of TRs for version 7.3. If you are using a partition with 7.3, and you can upgrade, this should be the time to consider doing so to at least 7.4.

IBM i 7.5 will only run on Power 9 and 10 servers.

Many of the new features of 7.5 will not be supported by 7.4 TR6. I will make note here of which are additions or changes that apply to 7.5 only.

Wednesday, April 27, 2022

Placing SQL functions into the SQL Insert statement

To write the posts for this website I need to have examples DDL tables. I often use a Table whose first column contains the row number and a second contains some kind of random number.

While I was illustrating how to fill one of these tables it struck me that there was an easier way to do this, having all the logic in the Insert statement itself.

This is the source code for the Table I commonly use:


Wednesday, April 20, 2022

Printing information about SQL RPG programs


I am using the word "program" in a generic way here. In reality what I am writing in this post can also be used for SQL packages (object type *SQLPKG) and service programs too.

What I wanted to know was which SQL statements executed within a program, and the tables and indexes these statements used. Fortunately, there is a command for that: PRTSQLINF, Print SQL Information. Before I show what this command can do, I need to have a table and program I can use to create the information that the command will return.

First I need a table. I know I will not win awards for originality of this table's name; I have called it TESTTABLE.


Wednesday, April 13, 2022

ACS improves by

A couple of times a year IBM gives us a new release of Access Client Solutions, ACS. It would appear that Scott Forstie, Db2 for i Business Architect, has a similar sense of humor as mine, as I was amused by his announcement of the new release:

IBM i Access Client Solutions just improved by!

The previous version was and now we have the brand new!

You can check which release you currently have from the "Access Client Solutions" window. Click on "Help":

Wednesday, April 6, 2022

List rows in IFS files that contain a certain string

listing all rows in ifs files containing a string

The question was an interesting one: How is it possible, in SQL, to list all of the files in the IFS that contain a certain string?

There are a number of SQL table functions that allow me to get to information about the files in IFS folders, and others to "read" their contents. This is a good excuse to combine the two.

I am going to place three files in my personal IFS folder, MyFolder, then search those for the desired string. I created three DDL tables in my library, MYLIB. OK, I created one table and then cloned it two times to make the three tables. I populated them with just a few rows of data. As the tables are identical, I can easily combine the results from the three in one SQL statement using the UNION clause:


Monday, April 4, 2022

When divine intervention is needed

There are times in everyone's work life when things go from bad to worse, especially when you are trying to fix something in the middle of the night.

If you feel that divine inspiration is needed you are in luck as there is a patron saint of computers and the internet: Saint Isidore of Seville.

In 1997 the head of the Catholic church, John Paul II, designated St Isidore of Seville to this role. You can learn more about him here. Today, April 4, is his saint's day.

If you are asking for help make sure you are asking St Isidore of Seville, as there is another St Isidore who is the patron saint of farmers.

Thursday, March 31, 2022

World backup day

A day like today is a good reminder of the importance of backups. I am sure we have all had to restore data from a backup many times in our lives in IT.

Do you know if your company's backup are valid? It always surprises me how many company's purchase a new device for backups, tape drive or VTL, and never try to do a test restore. Trust me you want to make sure you know how to restore data from the device before your really need to.

Not really part of backups but make sure you only retain backups for the time they might be needed, this varies by industry and would imagine by country too. Once the backup has passed the retention date destroy it. I have been through an e-discovery process, it became an almost full-time job for a month to find every backup that was on premise, off site, and even in the IT members cars and homes. It gets expensive too when you are asked to restore data from tapes you no longer have a tape drive for, and you have to contract with a third party to do it on your behalf. It is so much easier to tell the auditors that, in agreement with your company's legal department and the written company policy, all backups are destroyed after the designated period of time.

Wednesday, March 30, 2022

SQL indexes suggested from the job log

sql ddl indexes advised by debug

I have written before about how Db2 for i will suggest indexes, using the SYSIXADV view or Navigator for i, that will improve the performance of your SQL programs, etc. There are times I need to know if any indexes are advised when a program has run just once.

Fortunately this information is written to the job log, when the statement is executed in the program.

Before I demonstrate that I need a SQL DDL table I can use:


Notice that the table does not have a key.

Thursday, March 24, 2022

Changes to the RFE process

new rfe process

This morning I received an email from IBM informing that there are changes coming to the RFE, Request For Enhancement, process.

IBM uses the RFE process to drive a lot of the enhancements they add to new releases and Technology Refreshes to all of their products, including IBM i. If you have an idea you think would be a valid improvement to IBM i you would a submit an RFE. Other people can vote on the RFE if they agree it would be a worthwhile enhancement.

I am not going to repeat what IBM has already published on their Community web site. What I am going to do is to give you this link to it here.

If you have a bookmark in your browser to the RFE old web page you will need to delete your existing one and create a new one as it is now found at a different URL.

I encourage you all to submit RFEs if you have any ideas, and vote on others. If you don't IBM will never know that wonderful idea you just came up with.

Wednesday, March 23, 2022

Encrypting data with SQL

encrpyt columns with sql

Db2 for i provides us the ability to encrypt data in the database using three of the more popular methods:

There are two ways we can do this, set an encryption password within a program and use that, or give the password with every insert or update. My preference is the first scenario.

Before I start encrypting data, I need a DDL table in which to put it all:


Monday, March 14, 2022

SQL has a constant for pi

using pi in sql

Today is 3/14 in *MDY format, it is called pi day in recognition of the mathematical constant of the same name. Therefore, I thought that this would be the ideal occasion to write about the SQL scalar function PI().

Many years ago when I was at school, I was taught that pi is the fraction 22/7. This is not exactly true. The ancient Greek mathematician Archimedes stated that pi, or π in Greek, is a number be approximated as being between the fractions 223/71 and 22/7. It is not exactly 22/7, but that is considered the most popular approximation.

SQL does better than any approximation. If I use the scalar function PI() I am given the true value of pi to 15 decimal places.

I say PI() as the function is not passed any parameters. Then why should it as pi is a constant.

How does PI() compare to the approximations? As I am using ACS's Run SQL Scripts I would just use the following statement:

Wednesday, March 9, 2022

Media library information with SQL

media library information and status

A recent upgrade to a new virtual tape library, VTL, product introduced me to the concepts of a tape library and to a new command Work with Media Library Status, WRKMLBSTS. The system admins came to me asked if there was a way I could get to the same information as is displayed by WRKMLBSTS.

They had become frustrated with the command as it did not offer the ability to output its results to an outfile or a spool file, that they would then copy to a file.

"Is there something in SQL you can work your magic with?" they asked.

A quick search of IBM's documentation portal gave me what was needed, a view called MEDIA_LIBRARY_INFO.

Wednesday, March 2, 2022

Finding open cursors within a job

looking for open sql cursors

It is possible to leave a SQL cursor when you exit a program.

Personally I prevent a cursor being left open by use of the SET OPTION SQL statement in my RPG program:


Or when I create the object I make sure that the Close SQL Cursor parameter, CLOSQLCSR, is set to *ENDMOD:


Not everyone is as vigilant or as cautious as I am. If a program ends abnormally, or the cursor is not closed with the SQL close statement:

Monday, February 28, 2022

End of Local User Group Month

Today being the last day of February it is also the last day of Local User Group 2022 month.

I am please to say that last week I found a LUG based in Singapore that covers all of the operating systems that run on IBM Power: AIX, IBM i, and Linux.

ASEAN Power (AIX, IBM i, Linux) Group

They have been added to the list of user groups on the User Groups page.

Even though Local User Group month has ended I am still interested in keeping the list of user groups up to date. If you learn of a new group, or one that has closed, please contact me with their details.

A thank you goes out to everyone who contacted me about their groups, or groups they know of.

Thursday, February 24, 2022

Is this proof iNext is IBM i 7.5?

is inext 7.5

IBM's own Support Roadmap shows a new release coming this year, which they have given the marvelously mysterious name: iNext. The only unknown is would this new release be 7.5 or 8.1 .

I have heard whispers from within the IBM i community that the new release would be 7.5 . Although no-one could offer any hard proof that it would be.

I received an email from AG (full name withheld to protect his anonymity), that he had found a file in IBM's manual downloads site for QMGTOOLS a file with the name: qmgtool750.savf

Wednesday, February 23, 2022

SQL scalar function to add quote characters around your strings

delimit_name sql to delimit columns and fields

I was working on an interface between two applications. I needed to provide to the receiving application data that would be delimited with the quote character ( " ) and of varying length. The data was coming from an older application with DDS files and fixed width fields.

In my first version I used the following to provide a person's name in the desired format:

01  SELECT '"' || RTRIM(LAST_NAME) || ', ' || RTRIM(FIRST_NAME) || '"'

Wednesday, February 16, 2022

Checking for database connections between partitions

datbase connections between partititons

In Db2 for i a "database" is the equivalent of a partition in IBM i "speak". Many of us work with multiple partitions, and there are programs that fetch and send data between them. The two most common ways you can send and receive data between partitions is using a DDM file and the SQL three part name.

Over my three decades of programming I have used both many times, although in the past few years I have used the SQL three part name more often. To go from just fetching a few records from a file, to copying whole files. But how can I tell if a particular job is using these kind of connections to fetch or send data?

Fortunately there is a SQL table function, ACTIVE_DB_CONNECTIONS, that allows me to see those connections.

Wednesday, February 9, 2022

Using SQL to copy data between partitions

sql 3 part name examples

I work in a multi-partition environment and frequently use the three part name in a SQL statement to "pull" data from one partition onto another. I was recently asked if I could give some more examples of it in action.

The three part names describes a method where I can get information from another SQL database. With IBM i that translates to another partition. Using IBM i terms the format of the three part name is:


Used in its simplest it could just be:

Monday, February 7, 2022

Local User Group 2022 update

We are one week into Local User Group month, I want to thank everyone who contacted me with information about Local User Groups, LUG.

Most confirmed the details of existing groups.

But I did receive the bad news that the Vermont Midrange User Group closed its doors in 2020.

On a brighter note Common Switzerland, German language site, fixed their redirect so their site is active again.

Please keep sending in information about LUG from anywhere in the world by using the Contact Form, on the right.

Still no news from Asia. This surprises me with the prevalence of Power servers running IBM i in India and Japan.

Confirmed so far:

Wednesday, February 2, 2022

Finding which job submitted a batch job

which job submitted this one

An occasional question I get is there a way to determine which job submitted a particular batch job. Fortunately by using a SQL table function I can retrieve that information.

To be able to get to retrieve this information the batch job must be active, running, if it not what is described here will not work.

Before I starting showing SQL I need a program that will submit a job to batch.

Tuesday, February 1, 2022

Local User Groups 2022

Every February I have what I like to call Local User Group Month. During this month I maintain the list of user groups I have on this web site. The list can be found by clicking on the "IBM i User Groups" button on the top of this, or any other, page.

Why do I do this? It is because I believe that taking part in an IBM i user group benefits all of us. We can learn from one another, and share our experience too.

I call upon all of you readers to help me too. If you know of any Local User Group anywhere in the world that are not on the list, groups that have shut down, or groups that have changed their website address please contact me using the Contact form on the right.

Just going through my list from last year I found that...

Friday, January 28, 2022

It's a luggable, huggable AS/400!

finding old as400

Opposite my office at work is a closet. I finally was given a key to it yesterday. I found that it is full of many, many old things: documentation for software my employer no longer uses, tapes of various types and size, CDs, floppy disks, what looks like many lengths of twinax cable, and one bag with an IBM logo on it.

I am a curious person so I took the bag from the closet into my office, wondering what this bag could contain.

Wednesday, January 26, 2022

Deleting rows with SQL Merge statement

sql merge delete

Someone pointed out to me last week that the SQL Merge statement can delete rows from the table too. This struck me as bizarre as in my humble opinion a merge should just be the equivalent of insert and update. When I checked the documentation there it was, delete in the merge.

I have written about the update and insert of data into a table using the Merge in a previous post. I will be using the same files and tables in these examples, therefore, if you want to know more information about them please go to that article here.

I am going to start after the data from FILE1 has been inserted into TABLE1, using an Insert statement. I can view the contents of the table with this simple Select statement:


Wednesday, January 19, 2022

When was the last IPL performed?

when last ipl

One of the IBM i operators and I were talking about the contents on one of the little use Power server partitions and the following question came up: When was the last time an IPL was performed on this partition?

I know I could find that information in Service Tools. But how would I do so if I am not authorized to use the Service Tools menu?

Fortunately SQL comes to my rescue.

When an IPL starts a job with the following job number is always the first to start: 000000/QSYS/SCPF

Wednesday, January 12, 2022

Calculate different types of averages using SQL

calculating averages with sql

As we learned at school there are three types of averages:

  1. Mean:  Which is what people when they use the word "average" mean (pun is intended). It is calculated by adding several values together and dividing them by the total number of values.
  2. Median:  The midpoint of a sequence of numbers. This number may or may not be a value in the sequence.
  3. Mode: The most frequent number of the set of data.

Fortunately Db2 for i gives us scalar functions for the first two, and we can easily determine the third.

Let me start with my list of values, that are all taken from field FLD1 in the file TESTFILE.

01  SELECT * 

Wednesday, January 5, 2022

Retrieving the file and library name from a SQL alias

get name of file and library alias was built over

I use SQL aliases a lot of the time to access data from a file that contains multiple members. When an alias has an undescriptive name like ALIAS1 which file was it built over?

Fortunately there are two scalar functions that allow me to retrieve that information from the alias:

  • TABLE_NAME:  Name of the file that the alias is built over
  • TABLE_SCHEMA:  The library in which file that the alias is built over is found

Before I get to use those scalar functions I need a file with multiple members and aliases built over those members.

Let me start with the file. You will not be surprised to find that it is called TESTFILE.

01 A          R TESTFILER
02 A            FLD1         100A         VARLEN

I use the following command, CRTPF, to create my physical file.

Saturday, January 1, 2022

Happy New Year, 2022!

Last year was blighted by the COVID pandemic again. I am sure all of us are glad to see another year dominated by the virus behind us. I am keeping my fingers crossed that we might start returning to a more "normal" life this year.

Despite all the restrictions upon our working lives IBM still managed to bring us a new Power server series, Power 10, starting with the E1080 server. As well as two Technology Refreshes. You can read about it all on this website.

Every year I look to see what were the most read posts of the year. These were: