IBM i new logo

I have been writing this blog for 7¾ years and during that time IBM has used the blue circular "i for business" logo for our favorite business operating system, IBM i.

Yesterday IBM launched a new logo for IBM i.

IBM i support roadmap update

I was at a presentation by Allison Butterill, Offering Manager for IBM i, where she shared the latest IBM i and Power support roadmap.

I always like to share these to counter the nay-sayers claiming that the "AS400 is dead", AS400 has been dead for more than a decade but IBM i and POWER live on.

Looking at the chart, below, you will notice that the dark blue bars are followed on by a light blue bar. This signifies when a release goes off support, but support can be prolonged by purchasing extended support.

Which IBM commands have been changed?

Someone asked me how to detect if any of their system commands have been changed. By "system commands" they meant any of the commands in the library QSYS. I can think of two ways to do this, and I will describe both below.

I have written before about how to retrieve the defaults of a command, so I will not repeat that here.

When I tested both methods in the IBM i partition I use when writing these posts I found that only one command was returned as changed, therefore, the results I am going to show will be limited to that one command.

• Has command's defaults changed? Using SQL
• Has command's defaults changed? Not using SQL

SQL function to truncate timestamps

I stumbled across this SQL function and thought it might be something I could use in the future, and that some of you might find it useful too. TRUNC_TIMESTAMP will truncate a timestamp to give another based on the type of truncation. For example if I wanted to truncate to the month it returns a new timestamp of the lowest value for the year and month.

It is not new, I can find it in the IBM KnowledgeCenter back to IBM i 7.1, it just surprises me that I have not discovered it before.

Rather than try to explain how this function works I think it is easier to show with examples. In these examples I am going to use the special register CURRENT_TIMESTAMP as the timestamp I will be truncating.

Calculating difference between timestamps in SQL

Update: In 2023 an improved function to do the same was released. You can read about it here.

---

Having discussed how to make timestamp values from numbers, the next step in what I need to do was to determine the difference between the start and the end times. In this case there is a SQL function I can use: TIMESTAMPDIFF

The basic syntax of the statement is:

SELECT TIMESTAMPDIFF(< number >,CHAR(< end timestamp > - < start timestamp >)) FROM < file >

The numbers you can use are for the following time intervals:

• 1 = Microseconds
• 2 = Seconds
• 4 = Minutes
• 8 = Hours
• 16 = Days
• 32 = Weeks
• 64 = Months
• 128 = Quarters
• 256 = Years

Let me jump straight to some examples. In this first I am only going to calculate the differences as time values, but not microseconds. Microseconds has such a small range they could not be calculated for the timestamps used in these statements.

Convert numbers to times and then into timestamps

I am sure that most of us work with ERP databases where the date and time fields are really numbers. Finding ways to convert these numbers into "real" dates and times allows us to make use of all the rich date and time features within the IBM i operating system.

I have described in a previous post how to convert various numeric versions of a date to a real date. So I won't bother to repeat that.

But what about converting numbers into a real time?

Before I start giving examples let me show the file that contains the data I will be using. Yes, I am using a file as in my experience if there are numbers pretending to be dates and times then they are in DDS files, rather than DDL Tables. The file contains four fields:

Calculating differences between dates in SQL

I was asked if there is an easy way in SQL to calculate the difference between two date fields.

I am sure there are many different ways to do this. In my example I have a file, TESTFILE, that contains a date field, WKDATE. I want to compare this date to today's date and produce a string that tells me the number of years, months, and days difference. Let me start off with the calculation of the difference:

01 SELECT WKDATE, 02 CURRENT_DATE AS "Curr date", 03 CURRENT_DATE - WKDATE AS "Diff" 04 FROM TESTFILE ;

User Group month 2021 kicks off

I am a big believer and supporter of IBM i Local User Groups, LUG, as they are always a great place to swap and share experiences, as well as learning from your peers. This website has a page dedicated to IBM i user groups, and you can use this to find whichever one is closest to you.

Once a year I go through this list and purge from it all of the groups that are no longer active, or their website is inactive. I also ask you if you know of any groups, anywhere in the world, who are not listed, changed their web address, Twitter and LinkedIn accounts that are not on the list please contact me via email, Facebook, LinkedIn, Twitter with their details. You will find all of my contact information on the About page, or you can use the Contact form on the right.

If you learn of any information about a LUG outside of this month please let me know, and if it is new or has a new website I will mention it on this blog.

Thank you in advance for helping to spread the word about LUGs.

For groups in CL

This comes under the category of: I thought I had already written about this. The DOFOR command in CL allows me to construct a For group in CL, like I can in RPG when I use its FOR operation code. The same information has to be given to both:

RPG === for Count = 1 to 10 by 1 ; CLLE ==== DOFOR VAR(&COUNT) FROM(1) TO(10) BY(1)

1. What I call the count variable
2. Starting value
3. End value
4. Increment value, this is optional as it is in RPG. If it is not given 1 is assumed

Determine if Exit points or programs have been changed

Exit points are a feature of the IBM i operating system. When certain processes are run an Exit point is where an Exit program can be called. After the Exit program has completed the process continues. You can create your own Exit programs, and insert them into these Exit points. Even though Exit points and programs have been around forever many people do not use them, nor do they monitor them. As they can capture system information, their misuse must be considered a security breach.

In this post I am not going to describe how to add or make changes to Exit points and programs. I am going to give a simple example how they can be monitored. Notifying me when they have been changed.

This work includes two new SQL Views that came as part of the latest Technology Refresh, IBM i 7.4 TR3 and 7.3 TR9:

1. EXIT_POINT_INFO:  Lists all of the Exit points and information about them
2. EXIT_PROGRAM_INFO:  List all of the Exit programs and information about them, including the Exit point data

SQL table function to view all open files

One of the many additions to SQL in the latest round of Technology Refreshes, IBM i 7.3 TR3 and 7.3 TR9, is a Table function that displays a job's open files.

I can see the same information using the Display Job command, DSPJOB, option 14. But there are times I might want to access the same information in a manner that I can get to easily in a program.

The Open Files table function, OPEN_FILES, is found in the QSYS2 library. It has only one parameter, the job name. If you want to retrieve the information for the current job you can use "*" rather than give the full job. Retrieving the current job's name requires minimal effort as it is a "built in" global variable, JOB_NAME.

The following SQL statements would return the results for current job:

Stop SQL DROP statement object not found error

SQL's DROP statement is pretty universal. It is used to delete just about anything that can be created in SQL: tables, indexes, views, procedures, etc. As with all delete statements there will be an issue if you try to delete an object that does not exist. For example if I drop a table:

DROP TABLE QTEMP.DOES_NOT_EXIST

I will get an error message. This one is from ACS's "Run SQL Scripts":

SQL State: 42704 Vendor Code: -204 Message: [SQL0204] DOES_NOT_EXIST in QTEMP type *FILE not found.

While this is not a big deal in "Run SQL Scripts" as I can just ignore the message, it can be a significant issue if this situation is encountered in a program.

Fortunately this issue has been overcome in the latest Technology Refreshes released last week, IBM i 7.4 TR3 and 7.3 TR9, with the ability to add the words "IF EXISTS" to the Drop statement. For example if I use the following statement in "Run SQL Scripts":