Tuesday, May 17, 2022

New TR for IBM i 7.3

ibmi v7r3 technology refresh tr12

I am certain in all the videos of the announcements for IBM i 7.5 and 7.4 TR6 we were told there would be no more Technology Refreshes, TR, for 7.3.

Well, it appears that there is to be another Technology Refresh for 7.3, TR12. I found it on the Technology Refresh information page for 7.3 here.

It is available on the same day, May 24, that 7.4 TR6 is.

What is available in this TR for 7.3?

Wednesday, May 11, 2022

Making a list of journal receivers with the detach date

journal receiver information from api

I wanted to write a program that would delete old journal receivers, ones detached from the journal and more than a certain number of days old (to be determined later). I can see the detach date when I use the Display Journal Receiver Attributes command, DSPJRNRCVA, but I can only do this for one receiver at a time, and the command only allows for display or print. Looking at my chart of SQL Views and Table functions I found that is not one for Journal Receivers. Alas, this leaves me having to use an API to get to this information.

The information I desire is:

  • Journal name and library
  • Journal receiver name and library
  • Attach date (timestamp) of the receiver
  • Detach date (timestamp) for the receiver

There is an API that will return to me the information. It comes with the long name: QjoRtvJrnReceiverInformation. Before I go into the example of using this API let me get started with how I made a list of all the receivers in a library. Here I can use a SQL Table function, OBJECT_STATISTICS. I use the following statement to display a list of all the receivers in a library.

SELECT OBJNAME,OBJLIB
  FROM TABLE(QSYS2.OBJECT_STATISTICS(<library name>,'*JRNRCV','*ALLSIMPLE'))

Tuesday, May 3, 2022

New version 7.5 announced

ibmi 7.5 and 7.4 tr6

Most of us knew it was coming, so there is no surprise that a new release for IBM i has been announced. The new release, 7.5, accompanies a Technology Refresh update for version 7.4 TR6. As only two versions of the IBM i are supported with TRs it does also mean the end of TRs for version 7.3. If you are using a partition with 7.3, and you can upgrade, this should be the time to consider doing so to at least 7.4.

IBM i 7.5 will only run on Power 9 and 10 servers.

Many of the new features of 7.5 will not be supported by 7.4 TR6. I will make note here of which are additions or changes that apply to 7.5 only.

Wednesday, April 27, 2022

Placing SQL functions into the SQL Insert statement

To write the posts for this website I need to have examples DDL tables. I often use a Table whose first column contains the row number and a second contains some kind of random number.

While I was illustrating how to fill one of these tables it struck me that there was an easier way to do this, having all the logic in the Insert statement itself.

This is the source code for the Table I commonly use:

01  CREATE TABLE MYLIB.FIRST_TABLE
02    FOR SYSTEM NAME "TABLE1"
03  (FIRST_COLUMN FOR COLUMN "FIRST" VARCHAR(10),
04   SECOND_COLUMN FOR COLUMN "SECOND" SMALLINT,
05   UNIQUE (FIRST_COLUMN)) ;

Wednesday, April 20, 2022

Printing information about SQL RPG programs

prtsqlinf

I am using the word "program" in a generic way here. In reality what I am writing in this post can also be used for SQL packages (object type *SQLPKG) and service programs too.

What I wanted to know was which SQL statements executed within a program, and the tables and indexes these statements used. Fortunately, there is a command for that: PRTSQLINF, Print SQL Information. Before I show what this command can do, I need to have a table and program I can use to create the information that the command will return.

First I need a table. I know I will not win awards for originality of this table's name; I have called it TESTTABLE.

01  CREATE TABLE MYLIB.TESTTABLE
02  (FIRST_COLUMN FOR COLUMN "COL1" CHAR(10),
03   SECOND_COLUMN FOR COLUMN "COL2" INT) ;

Wednesday, April 13, 2022

ACS improves by 0.0.0.2

A couple of times a year IBM gives us a new release of Access Client Solutions, ACS. It would appear that Scott Forstie, Db2 for i Business Architect, has a similar sense of humor as mine, as I was amused by his announcement of the new release:

IBM i Access Client Solutions just improved by 0.0.0.2!

The previous version was 1.1.8.8 and now we have the brand new 1.1.9.0!

You can check which release you currently have from the "Access Client Solutions" window. Click on "Help":

Wednesday, April 6, 2022

List rows in IFS files that contain a certain string

listing all rows in ifs files containing a string

The question was an interesting one: How is it possible, in SQL, to list all of the files in the IFS that contain a certain string?

There are a number of SQL table functions that allow me to get to information about the files in IFS folders, and others to "read" their contents. This is a good excuse to combine the two.

I am going to place three files in my personal IFS folder, MyFolder, then search those for the desired string. I created three DDL tables in my library, MYLIB. OK, I created one table and then cloned it two times to make the three tables. I populated them with just a few rows of data. As the tables are identical, I can easily combine the results from the three in one SQL statement using the UNION clause:

SELECT 'TABLE1',A.* FROM TABLE1 A
UNION
SELECT 'TABLE2',B.* FROM TABLE2 B
UNION
SELECT 'TABLE3',C.* FROM TABLE3 C 
ORDER BY 1,2

Monday, April 4, 2022

When divine intervention is needed

There are times in everyone's work life when things go from bad to worse, especially when you are trying to fix something in the middle of the night.

If you feel that divine inspiration is needed you are in luck as there is a patron saint of computers and the internet: Saint Isidore of Seville.

In 1997 the head of the Catholic church, John Paul II, designated St Isidore of Seville to this role. You can learn more about him here. Today, April 4, is his saint's day.

If you are asking for help make sure you are asking St Isidore of Seville, as there is another St Isidore who is the patron saint of farmers.

Thursday, March 31, 2022

World backup day

A day like today is a good reminder of the importance of backups. I am sure we have all had to restore data from a backup many times in our lives in IT.

Do you know if your company's backup are valid? It always surprises me how many company's purchase a new device for backups, tape drive or VTL, and never try to do a test restore. Trust me you want to make sure you know how to restore data from the device before your really need to.

Not really part of backups but make sure you only retain backups for the time they might be needed, this varies by industry and would imagine by country too. Once the backup has passed the retention date destroy it. I have been through an e-discovery process, it became an almost full-time job for a month to find every backup that was on premise, off site, and even in the IT members cars and homes. It gets expensive too when you are asked to restore data from tapes you no longer have a tape drive for, and you have to contract with a third party to do it on your behalf. It is so much easier to tell the auditors that, in agreement with your company's legal department and the written company policy, all backups are destroyed after the designated period of time.