7.4 TR 4 and 7.3 TR10 announced

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.

Displaying foreign key constraints using SQL

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:

• SYSCST:  Every constraint, can be considered the "header" file for constraints
• SYSCSTCOL:  Columns upon which the constraints have been defined
• SYSCSTDEP:  Tables upon which the constrains have been defined
• SYSKEYCST:  Every unique, primary, and foreign key that has been defined
• SYSREFCST:  Foreign keys that have been defined

Special religious day for IT

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.

IBM KnowledgeCenter replaced

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:

• IBM i 7.4
• IBM i 7.3
• IBM i 7.2
• IBM i 7.1

 

Addendum – April 5, 2021

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.

Next CTXiUG meeting

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

What time was that subsystem started?

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.

 

SQL's whenever gets modernized

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:

View columns for SQL tables, etc. located in QTEMP

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.

Check if submitted job is still on job queue

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:

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.