Today IBM has announced the latest Technology Refreshes for the current IBM i releases, 7.3 TR10 and 7.4 TR4. While the PTFs for some of these will be available on Friday (April 16, 2021) most of them will not be released as a PTF until May 14.
What's in these TRs?
For 7.4 TR4 only: Blocked insert is extended to include tables containing generated columns.
I was asked:
What about referenced tables when you specify a foreign key when creating a table? I can check this when I run DSPFD, it is in Parent File Description but I still can't find an SQL to elaborate all tables in our Application.
I have to admit this one took me some time to find the information I need to provide the example in this post.
A foreign key is one of the many constraints that can be used with Db2 for i tables and DDS files. Doing a quick search of the IBM KnowledgeCenter I found the following:
In the western Christian calendar today is doubly special. It is Easter day, and it is Saint Isidore of Seville saint's day.
St Isidore of Seville is special to the Information Technology community as in 1997 he was made patron saint of all things related to computers and IT by Pope John Paul II. You can learn more about Isidore here.
Happy St Isidore of Seville day, and let's hope for a little divine intervention so all of our IT projects in the next year complete successfully, on time, and on budget.
Yesterday afternoon I found that IBM had replaced their documentation website, IBM KnowledgeCenter, with a new one, IBM Documentation.
While the home pages of the KnowledgeCenter for the various releases of IBM i redirect to the equivalent page in the new IBM Documentation, the subpages do not. This means that the links to KnowledgeCenter pages at the bottom of the posts in this blog will not redirect to their equivalent in the new site.
What am I going to do? The links in the menu on the right of all pages now link to the new site. If you find a link in this blog that does not link to the Documentation site you can use the links on the right, and then search the Documentation site for the relevant information.
The links to the Documentation pages for each release are:
Barbara Morris, lead developer for the RPG compilers in the Toronto labs, posted a comment in this post:
I tried clicking on the link for %LIST at the end of this article https://www.rpgpgm.com/2020/11/new-rpg-features-for-arrays.html and it correctly redirected. So maybe the redirects were not all in place yet at the time you tried them, Simon.
I have clicked on an assortment of links in this blog too and it would appear that the old URLs do redirect to the new URLs. This makes me a very happy man.
Thank you IBM for putting the redirections in place.
The next meeting for the Central Texas IBM i User Group, CTXiUG, will be on Tuesday April 13 at 6:30 CT.
CTXiUG is fortunate to have Ramaj Kasamsetty presenting: Consume REST APIs from IBM i.
The presentation is online and is free. Everyone is welcome. All you have to do is to sign up, which you can do here. Space is limited so sign up while there are still places available.
More information about the meeting can be found on the CTXiUG's website: ctxiug.blogspot.com
The question was simple: Can you tell when a subsystem started?
I am going to give two examples. The first will show what I would do if you asked me what time the QPGMR subsystem was started. I only need to give an answer one time, and I do not need to write a program or code to do this.
In the second example I do want some code to show when the QPGMR subsystem was started. This would be used in a scenario where I would need to know frequently what time it started. I would add it to the Job Scheduler, run the job periodically, and it would send me an email with the date and time it started included within.
Many years ago, and yes it is so long ago I cannot remember exactly how long ago, when I was first embedding SQL into my RPG/400 programs I was introduced to the WHENEVER SQL statement. This statement can be used to cope with SQL errors and warnings returned from the previously executed statement. Personally I do not like what it does, and I do not use it in my programs.
In the announcement for the latest Technology Refreshes, IBM i 7.4 TR3 and 7.3 TR9, was mention of enhancements made to this SQL statement, which I think has won me over.
Before I explain what these enhancements I need to show the way the WHENEVER statement works without these enhancements. Even after the TRs you can still use the same old way.
The WHENEVER allows you to handle three types of exceptions:
One of the problems of using the SQL Views to list the columns of SQL Tables, Views, etc. is that they do not include objects in the library QTEMP. The latest round of Technology Refreshes, IBM i 7.4 TR3 and 7.3 TR9, has brought us a View that allows me to get to the information of those SQL objects in QTEMP.
Most people use the SQL View SYSCOLUMNS to learn about the columns in their SQL objects. For several IBM i releases has had a similar View SYSCOLUMNS2 that contains all the same columns, even using the same column names, and with ten additional columns not found in SYSCOLUMNS. If you compare the list of columns in both Views the last column common to both is HAS_FLDPROC.
IBM recommends us to use SYSCOLUMNS2 rather than the other view as:
... a query that uses SYSCOLUMNS2 will typically perform better than querying SYSCOLUMNS.
The question was: How can I check if job is still on the job queue and if it is held?
Fortunately the solution is simple using SQL. I can check a specific job queue or all for the job. The gotcha is that the job name does need to be unique. If the job was submitted to batch with the default job name, which is the user profile of the job that submitted it, it will be hard to determine which job is the one I want. But as I always submit jobs with a distinct name it is easy. In this example I want to check for a job called TRIAL_BAL that is submitted to the QPGMR job queue, which is in the QGPL library.
I have given examples of using the JOB_INFO SQL Table function in a previous post, but I did not write about how I can use it to get details of jobs waiting in job queues.
JOB_INFO Table function has a parameter called JOB_STATUS-FILTER that can be used to limit the type of jobs returned in the results. When *JOBQ is used it will return all of the jobs waiting on job queues.
If I use this SQL statement I get every job that is waiting on a job queue returned in the results:
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.
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.
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.
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.
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.
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:
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.
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:
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 ; |
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.
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) |
By now you should be aware of my liking and promotion of IBM i local user groups, LUGs. I am proud to announce that a new LUG, that I am involved with, has its first meeting next month.
The Central Texas IBM i User Group, CTXiUG, will have its inaugural meeting on Tuesday February 9, 2021, at 6:30 PM CT (click here to see what time that is in other places).
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:
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:
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":
I am often asked how is it possible to find the IBM i release and Technology Refresh level on a partition. In previous posts I have given ways to display the release, which PTFs are loaded, and the partition name. So I thought it was time I rolled this all into one SQL statement, from which I would get:
I can hear a number of you say: "Isn't the partition and database name the same?"
With the vast majority of IBM i and earlier models I have used when the partition was installed the database name was changed to be the same as the partition name. But there are occasions they are not, or it was given a different name. One of the IBM i partitions I use to write these posts has a different database name, and so do two of the four partitions at my work. This post will use RZKH's DEV730, which is the one where the partition and database names are different.
All the celebrations to welcome in the New Year are drawing to a close, and we say farewell to an annus horribilis. I am sure I am not the only person wishing for 2021 to be the year where we can overcome this virus, and return to living more normal lives.
Despite the lock downs, social distancing, and working from home IBM has still brought us two Technology Refreshes for the supported releases of IBM i, 7.4 and 7.3. They have done a great job bringing many new features and functions. You can read about them here on this web site.
This brings me to the five most popular posts of 2020. These are the ones you thought were the most interesting:
