Thursday, March 16, 2023

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:

Wednesday, March 15, 2023

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:

Wednesday, March 8, 2023

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.

Tuesday, March 7, 2023

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.

Monday, March 6, 2023

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

Thursday, March 2, 2023

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:

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.

Wednesday, March 1, 2023

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?