Wednesday, May 27, 2020

Improvements to RPG's timestamps

improvements to rpg timestamp

Ever since timestamps were introduction to RPG I have always felt aggrieved that they never contained the entire timestamp. Timestamps have six decimal places following the seconds and only the first three positions, milliseconds, were filled. The last three positions were always zero. Occasionally I have found in timestamp fields in two records with same value. I would unable to determine which record is older, without using other fields or the file's relative record number.

The latest round of new Technology Refreshes, IBM i 7.4 TR2 and 7.3 TR8, brought improvements to the way RPG does timestamps.

Some of what I am going to describe here may have been available in earlier releases or TRs.

Tuesday, May 26, 2020

Key data structure big improvement

change to RPG %kds

I do not use the Keyed data structure, but I can appreciate this change that occurred as part of the latest new Technology Refreshes, IBM i 7.4 TR2 and 7.3 TR8, was significant.

Before the latest TRs the second parameter of the %KDS built function had to be a number to denote how many of the key fields were to be used. The change in the new TRs now allows for that second parameter to also allow a variable.

The old way would look something like this:

01    if (NbrKeyFlds = 1) ;
02      chain %kds(FileKeys:1) TESTFILER ;
03    elseif (NbrKeyFlds = 2) ;
04      chain %kds(FileKeys:2) TESTFILER ;
06    endif ;

Wednesday, May 20, 2020

Retrieving information about a library using SQL

new table function library_info to get library data using sql

As part of the new Technology Refreshes, 7.4 TR2 and 7.3 TR8, came a table function to return information about a single library. I have to admit that I don't really care about some of the information returned in the results, but there are three columns I am going to find very useful.

The new table function LIBRARY_INFO is found in the library QSYS2. It combines data elements from the RTVLIBD and DSPOBJD commands. When calling this table function there are two parameters, one mandatory and the other is optional:

  1. Library name:  No surprise that this is the mandatory parameter
  2. Ignore errors?  What to do when an error is encountered. This is the optional parameter. Valid values are: YES and NO

In these examples I am not going to use the second parameter.

Right, let's jump right in and see what information we can retrieve about one of my libraries:

Friday, May 15, 2020

Today is 7.4 TR2 and 7.3 TR8 day

today 7.4 tr2 and 7.3 tr8 ptf are available

Today is the day the PTFs for the latest Technology Refreshes are available for download, IBM i 7.4 TR2 and 7.3 TR8.

The relevant PTFs are:

You can find links to what are in these Technology Refreshes here.

I am now going to generate a support ticket asking RZKH to load these TRs on their partitions. When they have I can start sharing how to use these enhancements.

Wednesday, May 13, 2020

Discovering the database's limits

using sql to see the maximum sizes of the ibm i database

Every so often I stumble across something in Db2 for i that I may not have a practical use for, but I find interesting. The SQL table SQL_SIZING is a good example of this. It contains one row for each limit of the IBM i Db2 database manager.

The table contains just four columns:

  1. SIZING_ID:  The ANSI, and ISO, number that defines the database sizing id
  2. SIZING_NAME:  The ANSI, and ISO, database sizing id name
  3. SUPPORTED_VALUE:  Sizing limit (value). If null the sizing limit is not applicable for IBM i
  4. COMMENTS:  Even though the sizing name is big enough to contain an adequate description of the sizing id this column gives a more detailed description

Wednesday, May 6, 2020

Adding RPG compiler parameters into the SQL Option statement

sql option compileopt in sql rpgle program

This feature was brought to my attention by someone who was converting a fixed format SQLRPGLE program to modern all free RPG code, and wondered the purpose of this code snippet.

03  C+             COMPILEOPT = 'DBGVIEW(*ALL)'

She understood the purpose of line 2. It was line 3 she had not seen before. Neither had I, and was intrigued as to what its purpose was and how else could it be used.

Tuesday, May 5, 2020

IBM performance FAQ update

The latest updated version of IBM's "IBM i on Power – Performance FAQ" was published at the beginning of this month, May 1.

While it includes the usual system information, in the latter half it contains advice for:

  • Db2 for i
  • RPG
  • Cobol
  • C
  • Java

To view the guide, which is a PDF, click here.

Wednesday, April 29, 2020

View the most recent Identity column value

in sql using identity_val_local to get last identity number generated

In my opinion identity columns are one of the cool things in Db2 for i. Basically an identity column is a column in a table that is updated by Db2 with the next sequential value. Depending upon how I code the identity column I can guarantee that they will never repeat, and provide a great unique key that can be used as a foreign key in another table. Most of time I do not have to bother with what the identity column's value as I know it will be unique and the only way I can reuse numbers is to reset the identity column.

I have to admit at times I am curious as to what the last value used in a table's identity column. I stumbled across a SQL function that will return to me the last used identity column for the table. But before I describe how to use this function I need a table and some data within it to show how to use the function.

My table is simple, it just contains two columns:

  1. Identity column
  2. Another column that will contain some data

Wednesday, April 22, 2020

Inserting one row with every columns' default value

sql insert 1 row with default values only

I wrote before how to SQL Insert a row into a table with the columns' defaults. Included within the last Technology Refreshes for IBM i 7.4 TR1 and 7.3 TR7 was an enhancement to the SQL Insert statement that would add a new row to the table with the columns' defaults without having to say "DEFAULT" for each column.

Before I show the enhanced Insert statement I need a table to insert the row into:

07   COLUMN3A DATE DEFAULT '01/01/1900',
09   COLUMN4A TIMESTAMP DEFAULT '0001-01-01-',
11  )

Tuesday, April 14, 2020

IBM i 7.4 TR2 and 7.3 TR8 announced

more information about ibmi 7.4 tr2 and 7.3 tr8

After last night's publication of the enhancements in the forthcoming Technical Refreshes, IBM i 7.4 TR2 and 7.3 TR2, this morning the announcement documents have been published.

From this we learn that the projected availability date for the PTFs for these TRs will be May 15, 2020.

Monday, April 13, 2020

Db2 enhancements in IBM i 7.4 TR2 and 7.3 TR8

ibmi 7.4 tr2 and 7.3 tr8 enhancements

The Db2 enhancements for the new Technical Refreshes of IBM i were published tonight, 7.3 TR8 and 7.4 TR2.

I have only had the opportunity for a quick glance at the pages for the two TRs and I find that there are some additions that have been made to 7.4, and not to 7.3:

  • Functional enhancement: Weakly typed distinct types
  • Functional enhancement: HASH_ROW built-in function

The same additions to the RPG language were made for both versions. These are:

Wednesday, April 8, 2020

System Reply List and using SQL to view the list

using system reply list

My original plan was to answer a question about the best way to retrieve the data from the system reply list on a particular IBM i partition. Before I wrote that post I asked my colleagues whether they knew about reply lists and had they used them? From their reactions I think I need to discuss what the system reply list is, and include the answer to my question within this post.

What the system reply list allows me to create a list of entries that will handle inquiry messages. On my travels I seen a few sites that use this feature, while most do not.

Reply lists have been around forever, all the way back to the days of the AS400. From what I remembered while creating the examples for this post I don't think anything has changed since I first played with them.

Saturday, April 4, 2020

Patron Saint of Computers day

In 1997 Pope John Paul II made Saint Isidore of Seville the patron saint of computers, computer programming, and the internet, and today is his feast day.

Saint Isidore was a sixth century scholar famed for his wisdom and writings at a critical time for the Catholic Church in Spain. He was bishop of Seville for 32 years, which this is used as part of his name to distinguish him from Saint Isidore the Farmer.

You can learn more about Saint Isidore of Seville by clicking here.

Do any other faiths have saints, spirits, or deities that are associated with computers? If you know of any please leave a comment below.

Wednesday, April 1, 2020

Playing with RPG's indicator array

changing indicators within the *in array

This all started with a conversation with a colleague. He asked about how he could change multiple RPG's numbered indicators without having to hard code the indicators. As the indicators are held in an array he was asking couldn't he just change a number of elements in one statement, rather than the individual indicators? He gave this example of fixed format RPG which changes indicators 10 - 13.

C                   MOVEA     '1001'        *IN(10)

When we place a debug breakpoint after this line we could see that indicators 10 – 13 contained the values he desired:

> EV *IN
*IN(10) = '1'
*IN(11) = '0'
*IN(12) = '0'
*IN(13) = '1'

Before I go any further I want to give my opinion about numbered indicators. I am not against indicators. I use them all the time in my work, and you have seen many examples of me using indicators in posts in this blog. But these are named indicators, not the numbered indicators those of us old enough to have programmed in RPG2 or RPG3 had to use. In RPG4 there is no excuse to use numbered indicators. They are confusing as a numbered indicator gives me no idea as to its function. Do you know what *IN80 is used for? If I use a named indicator instead then everyone knows its purpose.

Wednesday, March 25, 2020

Customizing email address for SNDSMTPEMM

giving each snsmtpemm user their own email address

I was asked is it possible for each person who sends email using the Send SMTP Email command, SNDSMTPEMM, to have their own unique email address. This command is used to send SMTP emails from IBM i. I am not going to go into details on how to use and configure your IBM i, I am going to refer you to the following posts:

When writing this post I had to use a Security Officer, *SECOFR, equivalent user profile to do what I needed to do. I do not have an System Operator, *SYSOPR, equivalent profile, therefore, I do not know if that gives you the necessary changes I will describe below.

Wednesday, March 18, 2020

Check if program is running interactive or batch

interactive or batch using sql rpg

In the past I wrote about a CL program that submit itself to batch, to do that the CL program must be able to determine if it running interactive or batch. The post describes how you can determine that in a CL program. But what about a RPG program or in SQL?

In this post I will give examples in both RPG and SQL how I can determine if the program is interactive or batch. This is not to replace my tried and trusted CL method. It is just an alternative that might prove useful in certain scenarios.


Wednesday, March 11, 2020

Variable length field in DDS file

This all started as a question from a member of another programming team at work. They had been asked to add a new field to an existing file. Most of the time this field would be empty, but it could contain up to 2,000 characters of data in some cases.

"It would be easy with a SQL table," they said "I could just make a new column VARCHAR (variable length character field) and make the default value null. But I don't know how to do that with a DDS field."

By using a variable length character field will mean that the field will not always be 2,000 characters. It will be as long as the data within it. And when there is no data in the field it will take up zero space. What a disk space savings.

Fortunately doing this with a DDS file is as easy as it is with a SQL table using the right keywords when defining the file. And it is just as easy to handle the variable length and null value in a RPG program. I decide to create my example file and the program on an IBM i partition that is running 7.2 just to show that there is nothing from a newer release need to do this.

Wednesday, March 4, 2020

Adding subtotals and a total to a SQL Select

adding subtotals and total to sql select

I have often looked at the results returned to me by a SQL statement and wondered to myself "Wouldn't it be nice if I could add a subtotal to these results".

Yesterday I decided to have a search using my favorite search engine to see if there is an easy way to do this. I could find examples in other flavors of SQL, but not in Db2 for i. Feeling piqued I decided to try one of these examples with one of my SQL Select statements, and was really pleased to find that what was given in the example also worked in Db2 for i too. Thank goodness for SQL interoperability and standardization.

I am not going to give the source code for the SQL DDL table I will be using in these examples. My table, TABLE_OF_THINGS, has three columns:

Monday, March 2, 2020

User Groups month is over

As the end of February has passed so has my IBM i User Groups month. During the month of February I reach out to you, the readers of this website, and ask for help to find new IBM i User Groups websites and social media accounts to add to my user groups page.

This year, with your help, I was able to add the following websites to the list:

  1. Common Slovensko (Slovakia)
  2. Georgia IBM Power Systems User Group Meetup which appears to have replaced the IBM i Tech Community of Atlanta
  3. Delaware Valley Computer User Group which is now found at a new URL

And the following social media accounts too:

Wednesday, February 26, 2020

Checking if Job has error, and sends an email

rpogram to monitor job for error message and send email

The germ for this post came from a question I was asked about if it is possible to monitor a job, and if it errors to send an email. In previous posts I have given examples of the pieces need to do this, and in this post I will just put it all together, like assembling pieces of Lego.

The one perquisite that is needed to make this work is that the name of the Job has to be unique, and always the same. Each time the job is submitted to batch it has to be submitted with the same unique name. In this example the job I want to monitor will always have the name: SIMON_JOB


If the name of this job is not unique then there is no way I can identify it, and I will be able to check its status.

The program is quite simple I just need to do two things:

Wednesday, February 19, 2020

Additions to Object Statistics table function

Changes to the OBJECT_STATISTICS table function

One of my favorite SQL table functions, OBJECT_STATISTICS, was introduced in IBM i 7.2 and it is something I use on a regular basis to identify unused objects that can be deleted. Over the last two releases of IBM i and numerous Technology Refreshes new columns have been added to this table function making it, in my opinion, better. In this post I am going to describe three of these enhancements, and how I can see them helping me.



One of the things I found using OBJECT_STATISTICS was certain object types' Last Used Timestamp was not updated when the object is used. This would fool me into thinking that it was an unused object that could be deleted as it was not being used. Then I would find all of the other objects of the same type were missing the Last Used Timestamp too. I ended up building a complicated select statement, referencing certain object types and conditioning the values in columns, to make an output table I could then use to find obsolete objects.

Wednesday, February 12, 2020

Calculating prime numbers in RPG

calculating prime numbers in rpg

The germ for this post is from a question that was asked in an interview. This person, a self confessed RPG "fresher", had been asked to write a "RPG free" program to calculate the prime numbers between 1 and 11. This request intrigued me, and made me think what kind of program the interviewer probably wanted, and what I would write if I need to use this at work.

Let me start with the basics, the definition of what is a prime number:

Any integer other than 0 or 1 that is not divisible without remainder by any other integers except 1 and the integer itself

Taken from the Merriam Webster dictionary.

What are the prime numbers in the desired range:

Thursday, February 6, 2020

Migration of developerWorks Technology Refresh information

The migration of content from IBM's developerWorks portal, which was sunset at the end of 2019, continued with Scott Forstie, IBM Db2 for i Business Architect, announcing the migration of two new sites:

As usual great information from IBM, and two sites I will be visiting on a regular basis.

Wednesday, February 5, 2020

Object owner information from SQL View

object_ownership sql view rather than wrkobjown

Tucked within the latest round of Technology Refreshes for IBM i for 7.4, TR1,and 7.3, TR7, was the addition of a SQL View, OBJECT_OWNERSHIP. This View returns the same information as the Work Object Owner command, WRKOBJOWN. I do not use WRKOBJOWN frequently, but I do before deleting an user profile to see which objects are owned by the profile, and an intermittent check on the production libraries to make sure that all of the objects are owned by the generic profile created for this purpose.

Alas, I am restricted on the IBM i partition I use to write these posts to only being able to see objects I own. Therefore, my examples will only show these objects.

The WRKOBJOWN is one of the easier commands to use. I can either just type it at a command line and press Enter, or press F4 to prompt to view the command's parameters.

Monday, February 3, 2020

Second annual IBM i User Group month

looking for information about ibm i user groups

If you are a regular readers of this blog you will know I am great fan of IBM i User Groups, and getting everyone involved with their local group. So much so I created a page of worldwide user groups, and added a link to it at the top of every page of this website.

Last year I started a campaign to update this list of User Groups. For the month of February I asked you, members of the IBM i community, to examine the list and if you knew of any groups that were not listed to let me know about them.

This year I am having the Second annual IBM i user group month. This month, February, I am asking you to do the same: review the User Groups page for any groups that are not listed. I am looking for any of the following information for any user groups:

Wednesday, January 29, 2020

Using SQL to look for record locks

view all jobs locking an object using sql

This is almost embarrassing. I was asked if there is an easy way to programmatically check if an object is being used by another job. I knew the name of the SQL View, but when I searched this site I could find I mentioned it when IBM i 7.2 TR3 was announced, but I did not write anything more about this View. Today I am making amends of that oversight.

The question was is it possible to know which users are using an object. I could just use the Work Object Lock command, WRKOBJLCK, at the command line:


But I needed to be able to retrieve within a program the same information.

Wednesday, January 22, 2020

Copying source statements from IFS files into programs

copy and include source code from members and ifs files into another member

I am sure we are all familiar with using a copy compiler directive to copy source code from a "copy book" source member into another. As we can now edit and compile individual source files in the IFS, how can I insert code from those members into the source code of my source file members?

Below I am going to give examples in my three favorite IBM i programming languages:

I have three files in an IFS folder, MyFolder, that contain snippets of RPG, CL, and SQL that I want to copy into programs in a source file, DEVSRC, in my library, MYLIB. I also have code snippets in other source members I want to include too. How to do it?

Wednesday, January 15, 2020

Retrieving the Host's name using SQL view

getting the system name from a sql view

When I run SQL queries, especially when retrieving system information, it is useful to include the IBM i partition name. I have been using a User Defined Function, UDF, I created to call a CL program to get this information. But looking at my trusty poster of Db2 for i I found the View that gives me the partition's host name and more interesting information.

This View also gives me the IBM i operating system version and release numbers, so I don't have to use the QSS1MRI data area and the DATA_AREA_ table function to retrieve it.

Tuesday, January 14, 2020

IBM Rational Developer for i Hub website

The next part of the process of moving the contents from IBM's now closed developerWorks to other new IBM sites occurred last week, with the announcement of the new Rational Developer for i Hub.

This is wonderful looking website that has put a wealth of information just a few clicks away from the home page. It appears to have everything that would help an existing RDi user, and for someone new to this tool. I have to say excellent work, and thank you, to all involved with creating this website.

You can reach this new site by clicking on this short URL:

This follows the RPG Cafe's new site, which was opened in December of last year.

Monday, January 13, 2020

Delete many spool files with one command

delete many spool files all at once

Have you ever wanted to delete many, many spool files with just one command? You know the scenario someone decided to print every invoice that had ever been generated, and now I have several thousand spool files in an output queue. I use the WRKSPLF command to view all of the spool files in the output queue, and starting deleting them one at a time by putting a '4' next to them. As I do this I am thinking there must be a better way?

Could I just clear the output queue? No, that's not possible as there are many other spool files in the same output queue, belonging to other users, that need to be printed or retained.

Wednesday, January 8, 2020

Checking authority to a file using SQL

using sql scalar function to determine if user is authorized to use a file

It was another one of those examples of a find when I was looking for something else. This is a SQL scalar function, SQL_CHECK_AUTHORITY, that returns a single value to inform me if I am authorized to use a file or not.

What is a scalar function? A scalar function is passed one or more parameters and returns a single value.

The syntax is very simple:


It returns one of the following values:

Friday, January 3, 2020

IBM YouTube series on PowerSystems myths

I stumbled upon a YouTube series about PowerSystems myths given by Skip Garvin, IBM Lab Services Expert. The goal of this series is to dispel the myths we have all heard, many times, about the perceived short comings of the PowerSystems servers.

These videos are about PowerSystems, and not IBM i. I thought I would share these so we all learn that a public cloud running on x86 servers is not the only future.

The series, to date, comprises of:

Wednesday, January 1, 2020

Happy New Year, and welcome to 2020

The last of the Christmas presents have been opened, and all of the decorations have been taken down and stored away. At the beginning of a new year I always like to reflect on the old year, and look forwards to what is coming.

2019 was another exciting year for the IBM i community. A new release of the operating system became available to us. Accompanying the new release there were another two Technology Refreshes for IBM i 7.3, and the first TR for the new 7.4. These added all kinds of new and exciting features and functions to our favorite operating system, which I have written about in this web site.

The only way I can gauge what you, the readers of this blog, considered the most interesting posts I wrote last year is by looking at the most read. These were: