Friday, April 30, 2021

Support for IBM i 7.2 comes to an end

end of support for ibmi 7.2

Today is the end of regular support for IBM i version 7.2.

Launched in May 2014, 7.2 has been a supported operating system for seven years. With its coming off support there remains only two releases of IBM i that are still supported, versions 7.3 and 7.4.

If you are still using 7.2 and you want continue support ask your IBM Business Partner if there is a Service Extension agreement. I have seen one mentioned as "TBA", but I have not seen any details. As an incentive to move to a more modern release IBM extended support tends to be more expensive than support was before.

If are using 7.2 and you can upgrade to a new release then you ought to consider doing so sooner rather than later, so that you will remain on IBM support. Being off support is not a good idea as you can no longer expect PTFs if any security updates, etc., are needed to keep your business safe and secure.

Wednesday, April 28, 2021

Handling variable length variables in CL

cl pogram coping with varchar data

More and more of us are using SQL to modernize our databases from DDS files to SQL tables, view, and indexes. When you rework your files into tables you quickly realize the benefit of converting that 50 alphanumeric field in the DDS file to a variable length character, VARCHAR, column in the SQL equivalent. This conversion saves disk space as the data in that column is only stored as the length it is, not the whole 50 characters.

RPG and SQL easily copes with this type of column. Alas, CL does not. If I try to declare file, DCLF, that contains variable length columns the program will not compile without using a special option in DCLF.

Before I start showing CL code I need to start with a SQL table, that contains a VARCHAR column:

Wednesday, April 21, 2021

Display values of SQL variables in ACS Run SQL Scripts

values statement in run sql scripts

Db2 for i comes with a host of built-in global variables and special registers that provide us with some of the basic information about the environment of the IBM i we are using.

When I am using ACS's Run SQL Scripts it is useful to be able to quickly check these values. Or if I am doing a presentation I need to be able to show which IBM i partition I am using to help people to understand what I am doing.

If I wanted to see which partition I am using I could use the following SQL statement:

I am sure more of you are familiar using the SYSDUMMY1 table in the library SYSIBM to do this kind of thing.

I am all for shortcuts and making things easier for myself. I need to give credit to Paul Tuohy for showing this particular shortcut.

Rather than using the Select statement I can use the VALUES statement in its place. By using the VALUES I do not have to define a file. If I want to display which database server my Run SQL Scripts session is attached to I could just use the following:

Tuesday, April 20, 2021

Unlocking objects locked by ACS Run SQL Scripts

unlock file or object in acs run sql scripts

It is 2021 and I hope we should all be using IBM's ACS with our IBM i partitions. I have the Run SQL Scripts tool open all day as it makes it so easy to develop SQL code that I will copy and paste into in a program, procedure, etc. or to test any SQL Views or Tables I have built.

In my opinion the Run SQL Scripts tool is so much better than the STRSQL command as I can see my statement and in the case of a Select my results on the same screen.

The problem is that this locks the object(s) I used in the Select statement. If I need to recreate those objects I get a CPF3202 message:

Monday, April 19, 2021


new acs

Update September 20, 2021: New version is currently available for download here

A new version of IBM's Access Client Solutions, ACS, has been made available. It is free so why would you want to use another tool? ACS is so much more than just 5250 emulation.

I can get the new version in two ways

I can go to the IBM website directly :

Or I can go to my ACS main window, click Help > Check for updates.

Friday, April 16, 2021

RPG PTFs for 7.4 TR4 and 7.3 TR10

IBM i 7.4 tr4 and 7.3 tr10 rgp ptfs out now

Looking on various social media feeds it would appear that the RPG PTFs for the latest Technology Refreshes enhancements have been made available today. These are for RPG's new %UPPER, %LOWER, and %SPLIT Built in Functions.

The RPG PTF numbers will be different for the two releases you will have to go to the IBM Support center and check which are the ones relevant for your release, or ask you System Admin to do so. PTF numbers:

  • IBM i 7.3 TR10:
    • Compiler = SI76100
    • Run time = SI76098
  • IBM i 7.4 TR4:
    • Compiler = SI76101
    • Run time = SI76099
    • Run time *PRV support = SI76109

I hope to get my hands on these soon, and applied, so I can start writing about them on this blog.

Tuesday, April 13, 2021

7.4 TR 4 and 7.3 TR10 announced

ibm i technology refreshes announced 7.4 tr4 and 7.3 tr10

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.

Wednesday, April 7, 2021

Displaying foreign key constraints using SQL

foreign key information 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

Friday, April 2, 2021

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:


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 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.