Friday, February 26, 2021

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.

Thursday, February 25, 2021

IBM i support roadmap update

ibm i support roadmap for march 2021

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.

Wednesday, February 24, 2021

Which IBM commands have been changed?

find changed ibm i commands

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.

Wednesday, February 17, 2021

SQL function to truncate timestamps

truncate timestamp

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.

Thursday, February 11, 2021

Calculating difference between timestamps in SQL

calculate differences between timestamp variables 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.

Tuesday, February 9, 2021

Convert numbers to times and then into timestamps

convert number to dates and timestamps in sql

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:

Wednesday, February 3, 2021

Calculating differences between dates in SQL

calcuate difference between 2 dates and then show it in words

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 ;

Monday, February 1, 2021

User Group month 2021 kicks off

ibm i user group month is february 2021

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.