Getting the hours, minutes, and seconds from a difference in timestamps with SQL

I have timestamp columns in a Db2 Table that I need to give the difference in hours, minutes, and seconds. Preferably I would put these into separate columns that I can then use in various ways in subsequent jobs and programs.

All you really needs to know about the Table I will be using is that it is called TESTTABLE, and the timestamp columns I need to calculate the difference between are:

1. FROM_TIMESTAMP
2. THRU_TIMESTAMP

I can easily determine the difference between the two with the following SQL statement:

Improved SQL built in function for calculating difference between timestamps

I had written about the Db2 built in function TIMESTAMPDIFF, which is used to calculate the difference between two timestamps and return the difference in various different units of time. In the latest Technology Refreshes, IBM i 7.5 TR1 and IBM i 7.4 TR7, a new and improved built in function, TIMESTAMPDIFF_BIG, was introduced.

TIMESTAMPDIFF had issues in being able to correctly return the difference in microseconds. IBM's documentation says that the new TIMESTAMPDIFF_BIG will do that correctly. The syntax of the two built in functions, BiF, is the same:

Copying data with an Identity column from one table to another and not change the value

It has always been a problem with copying data from one SQL DDL table to another if the table contains an identity column. Identity columns are controlled by the database itself. When a new row is inserted into the table the database assigns the Identity column in the new row the next sequence number. They are a great way to ensure that the table has a unique key, and they can be shared with other files as an external key to link back to the original table.

The issue has always been that if I copy data from one table to another with an Identity column of the same name the database will assign the inserted rows new identity values using the sequence of the copied to table. This has been a great frustration to us all, and has led to many forsaking Identity columns altogether.

How to change someone's PDM defaults

I have been asked this question twice this week:

Is there a way to change everyone's PDM settings without them having to into their own settings and making the change themselves?

Both people want to change the "file" that contains these setting either with a SQL statement or they would write a program to do so.

I do not know where the PDM settings are stored. I assume they are in a file, or possibly even files. As the data is IBM's data I would be very wary to change it as I have no idea of what the consequences are if I was to make a mistake.

CTXiUG meeting this month

The next meeting of the Central Texas IBM i User Group will be on Tuesday March 21 at 6:30 PM (US CT). The meeting is online.

We are fortunate to welcome Patrick Behr as our speaker to this meeting. Patrick is a COMMON Speaker of Merit, and will be making two presentations:

• Why RPG procedures are better than subroutines
• What the heck is Binder Source and why do I care?

The meeting is free and open to everyone. To learn more and register go to https://ctxiug.blogspot.com

Presentations in March 2023

This month I get to talk to a non-USA based IBM i Local User Group, LUG, and you can meet and see me in action at my first in person conference of the year:

• Friday March 10 @ 10:00 AM (SGT) with ASEAN (Singapore): Looking at you data with a different view point. On-line, open to everyone.
  If you are in North America you can still attend this meeting as the above date and time is Thursday March 9 @ 8:00 PM (CT)


• Wednesday March 15 - Thursday March 16 with WMCPA for their in-person Spring Conference in Delavan, WI. I will be giving three presentations:
  • Tricks with Spool Files using SQL
  • Look at your data from a different view-point
  • Simon’s top 10 SQL Favorites

For more information about these events check the LUG's websites, and contact them directly.

There is also a meeting of the Central Texas IBM i User Group, CTXiUG, on Tuesday March 21 @ 6:30 PM (CT). We will be joined by Patrick Behr who will be giving two presentations that will be of particular interest to RPG programmers. It is on-line and free. You can learn more and register at the CTXiUG website.

Easy way to validate if user has a certain group profile

This is another example of something I found by accident. Checking IBM's documentation I can find that this is found in IBM i releases 7.5 – 7.1, it might even be in earlier releases but IBM's online documentation only goes back to IBM i 7.1.

VERIFY_GROUP_FOR_USER is a SQL function that allows me to verify if the user of the current job is a member of any group profiles.

I could use the Display User Profile command, DSPUSRPRF, to display which groups and supplemental groups an user profile has. What is the fun in that when I can get the same information using SQL?

Finding out if I am authorized to a function usage identifier

Function Usage Identifiers are a way to be authorized to a particular function, without having the authority changed on my user profile. I have found it is a lot easier to persuade the sys admins to grant me authority to the Function Usage Identifier for something, than it is to have my user profile changed.

In this post I am going to show ways you can find the Function Usage Identifiers, see which ones someone is authorized to, and finally have a way to check in a program whether or not you are authorized to one of the functions. Fortunately all of this can be achieved simply using SQL. What I will be using are:

Using SQL to get the first of the month for a date

I have used the LAST_DAY scalar function to return last day of the month many times. I was interested to see in IBM i 7.5 TR1 and IBM i 7.4 TR7 that a new matching scalar function was added, FIRST_DAY.

As the name suggests FIRST_DAY will return the fist day of the month of the date passed to the scalar function. For example:

01 VALUES FIRST_DAY(CURRENT_DATE) || ' thru ' || LAST_DAY(CURRENT_DATE)

I have used FIRST_DAY and LAST_DAY to return the first and last days of the month for the current date. Rather than have this information in two columns I have concatenated these together to produce one result. I know the SQL purist are going to criticize me as I have used the double pipe symbols ( || ) rather than CONCAT. They will, rightly, point out that this using the double pipe will not work on other Db2 platforms. As I will only ever run this code in an IBM i partition, I am not concerned by this lack of cross-platform compatibility.

February 2023 Power announcement

On Valentine's day, February 14, Ken King, General Manager for IBM Power severs, published a blog describing the latest IBM Power announcements.

Not a lot about IBM i, but I am always interested to learn what is going on with the IBM Power server family of hardware and the operating systems it hosts.

You can read his blog, titled "Fueling Digital Transformation with IBM Power", here.

Discovering remote journals with SQL

I have an ongoing "battle" to keep the journals and their journal receivers under control in the IBM i partitions I am responsible for. Part of this has been to find and report on various features and functions of the journals. A recent addition to SQL, as part of IBM i 7.5 TR1 and 7.4 TR7, has been a View that returns results about the remote journals in a partition.

What is a remote journalling?

Remote journalling, in very simple terms, will send journal transactions to other partitions. I could use it to replicate changes made to file on the master partition to other partitions, and use those to update the remote files and keep all of the copies in synch.

Identifying profiles with disabled Netserver access

If you have users using your partition's IFS there are times when one of them manages to disable their access. In 2019 I wrote about how they can re-enable their access themselves using the QZLSCHSI API. What if I want to be more proactive and identify the profiles who already have disabled their IFS access?

Fortunately the information is attached to their user profile, but not in a place that I can get to with any of the User Profile commands, for example CHGUSRPRF. But I can use the SQL View USER_INFO. This View contains the column NETSERVER_DISABLED, if the User Profile has disabled their Netserver access then this column will contain 'YES'.

The statement I would use is:

Finding all the fields that contain certain characters

The germ for the idea for this post came from a question I saw in a Facebook Group. The question was how to identify records in a file where a particular field contains "special" characters, using SQL.

As the question was specially about a DDS file, I created a file, TESTFILE, with one field, FIELD1. I am not going to give the code for this file, just its contents. I would use the following SQL statement to list the contents of this file:

SELECT FIELD1 FROM TESTFILE

The results from this file are:

Finding a way to monitor for more than one message with RPG's monitor

Perhaps this title is a little misleading, I struggled to come up with something that adequately describe this scenario in the space allowed.

I was asked if there was an easy way when using a Monitor group to perform some kind of action if the message id starts with something like 'RNX12'.

Let me get start with my example RPG code:

Creating a User Index with SQL

When I wrote about a SQL procedure that allowed me to add data to a User Index, ADD_USER_INDEX_ENTRY, I created the User Index with the QUSCRTUI API. I received an email from an IBM employee telling me that there was a SQL procedure that would allow me to create the User Index.

I am going to do here is to create the same user index as I did with QUSCRTUI in that other post.

In this first example I am going to create the User Index with a fixed length entries of 100 bytes.

Getting an accurate count of characters in a variable RPG

Over recent years many of us are dealing with data in different character sets, not just the default CCSID of the partition we are using. The most common of these I encounter is UTF8.

UTF8 can contain double byte characters, which take two bytes for the character, as opposed to the standard single byte characters. If I am using a variable I have defined as UTF8 it is difficult to calculate the number of characters within, as the double byte characters result in an over count of the number of character present.

One of the additions to the RPG language as part of IBM i 7.5 Technology Refresh 1 and 7.4 TR7 are several things to make it possible to get a real character count from an UTF8 variable.

I am going to show several example programs to demonstrate how these new features work. Let me start with what I consider the most basic way, that is not bad thing I just me that IMHO the easiest. I am going to break this program into parts so it will be easy to explain what is happening. Let me start at the "top":

Procedure parameter option to convert data type

Another addition to RPG within the IBM i 7.5 TR1 and 7.4 TR7 updates was a new option in the OPTIONS parameter when defining the call to a procedure.

Previously if you passed, for example, a date to a procedure the parameter within the procedure's interface would have to be a date too. If the wrong data type was passed it would error.

The addition to the OPTIONS parameter is:

OPTIONS(*CONVERT)

Happy New Year 2023!

The old year is behind us, and I am always so hopefully and excited what the New Year will bring us all. Perhaps the new year will feel a bit more like the world returning to a kind of normal we have not seen for a couple of years.

In the IBM i world 2022 brought us:

• A new version of IBM i, 7.5
• Two new Technology Refreshes for 7.3 and 7.4, and the first TR for 7.5
• Merlin
• Midsized Power10 servers

What did you find interesting last year? These were the top five popular posts from 2022: