Technology Refresh PTFs now available

I am not sure why this did not publish on Friday, which is when all the PTFs became available.

On Friday (May 14, 2021) the PTFs for IBM i 7.4 TR4 and 7.3 TR10 are now available for download from IBM.

The PTFs for these TRs can be found here:

• 7.4 TR4
• 7.3 TR10

You will need to get and install latest PTFs for Database to get the TR enhancements for Db2 for i/SQL.

And check for any new RPG PTFs. Some of them were released in April, see here for information on those.

For information on what is in these new TRs check out the post I wrote when the announcement was made last month.

I am pleased to say that the guys at RZKH have already loaded the new PTFs, a big Vielen Dank to them. I will writing about all new features and functions them in the following weeks.

Using a result of a substring for the length value of another substring

I am sure that the person who asked me if this was possible is not the first person to have tried, and was unable to get it to "work".

They had been presented with a "flat" file where the first two characters denoted the length of the key value. What they need to do was to extract the variable length key into another value they could use.

The file was like this:

DATA ---------------------------------- 10<-- 10 -->XXXXXXXXXXXXXXXXXXX... 05<-5->XXXXXXXXXXXXXXXXXXXXXXXX... 20<------- 20 ------->XXXXXXXXX...

It is easy to extract the first two characters from DATA using the following SQL Select statement:

01 SELECT SUBSTR(DATA,1,2) AS "Length", 02 DATA 03 FROM TESTFILE ;

Which returns:

Seeing which index or logical file is being used by a SQL statement

I was asked if it was possible to see which DDS file or SQL index was used by a SQL statement?

Fortunately it is easy to see. But before I show how to do it, let me set up my "test data".

I have my physical file, TESTFILE, which has no key:

A R TESTFILER A FLD001 6P 0 A FLD002 10A A FLD003 L

I built two logical files built over this physical file. TESTFILEL0 has the field FLD001 as its only key:

A R TESTFILER PFILE(TESTFILE) A K FLD001

And TESTFILEL1, which has the key field FLD003:

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.