Renaming existing table with a short system name

Someone created a SQL DDL Table with a long name and with long column names only, and did not give short or system names. I have discussed in an earlier post how to add the short/system names to the Table and columns when you are creating the table. But in this example the programmer who created just created the Table with the long names only:

01 DROP TABLE IF EXISTS MYLIB.THIS_IS_A_LONG_NAME ; 02 CREATE TABLE MYLIB.THIS_IS_A_LONG_NAME( 03 FIRST_LONG_FIELD_NAME CHAR(7), 04 SECOND_LONG_FIELD_NAME TIMESTAMP 05 ) ;

Line 1: This deletes the Table if it already exists. A nice addition in the latest round of Technology Refreshes is the addition of the IF EXISTS, it prevents the DROP TABLE from generating an error if the Table does not exist.

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

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