Problem with long SQL object names when using IBM i commands

In my opinion the long SQL names I can give to Tables, Views, and Indexes allows me to give these objects good descriptive names. I also give them short system names, so I can easily use IBM i commands with them. But this can cause me problems.

I have a table, you will not be surprised to learn I have called it TESTTABLE and it resides in my library MYLIB, that I want to make a copy of to the library MYLIB2. I am sure I am not the only person who would use the Create Duplicate Object command, CRTDUPOBJ, to do this:

CRTDUPOBJ OBJ(TESTTABLE) FROMLIB(MYLIB) OBJTYPE(*FILE) TOLIB(MYLIB2) NEWOBJ(@CRTDUPOBJ) CST(*NO) TRG(*NO)

I get the following message to confirm that the file was created in MYLIB2.

Object @CRTDUPOBJ in MYLIB2 type *FILE created.

Support for IBM i 7.2 comes to an end

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.

Handling variable length variables in CL

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:

See you at COMMON NAViGATE 2021

Twice a year the COMMON user group holds conferences, one in the spring and the other in the fall. Due to the ongoing COVID crisis this year's spring conference is a "hybrid", some people will be presenting and attending in person and others will be doing so virtually.

I will be presenting at this year's spring event, virtually, on Tuesday May 25 4:00 to 4:20 PM US EDT (click here to find what time that is for you). Demonstrating many of the SQL services I have been using to "cleanup" and monitor partitions, and a few extra tips to make it easier.

If you are attending, whether in person or virtually, I would love to see you at my presentation.

Useful links:

• NAViGATE home
• About
• Registration
• Session schedule (does take a bit of time to load)
• My session

Display values of SQL variables in ACS 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:

Unlocking objects locked by 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:

New ACS 1.1.8.7

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 : ibm.biz/IBMi_ACS.

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

RPG PTFs for 7.4 TR4 and 7.3 TR10

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.

Blogger's Follow by Email ending

Yesterday I received the following message in my Blogger account:

FollowByEmail widget (Feedburner) is going away

You are receiving this information because your blog uses the FollowByEmail widget (Feedburner).

Recently, the Feedburner team released a system update announcement , that the email subscription service will be discontinued in July 2021.

After July 2021, your feed will still continue to work, but the automated emails to your subscribers will no longer be supported. If you'd like to continue sending emails, you can download your subscriber contacts.

If you have subscribed to the "Follow This Blog By Email" feature you will stop receiving the email notifications from this site in July.

I am removing this feature as there seems little point in allowing people to signup for a service that is going away, and I will look for another similar application. If I find one that meets my expectations I will add it as the new "Follow By Email".

Other links Blogger supplied explaining what is going to happen are:

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.