Using SQL to make a list of defective PTFs

This is something I know that System Admins should be checking, defective PTFs in their partitions. As part of the latest Technology Refreshes, IBM i 7.5 TR2 and 7.4 TR8, is a SQL View that allows me to generate a list of defective PTFs. Previously this was only available in QMGTOOLS, alas I am not authorized to that in any of the partitions I have access to, therefore, you'll have to take my word that option 24, PTF Menu, then option 3, "Compare defective PTFs from IBM", takes you to the same information.

The View DEFECTIVE_PTF_CURRENCY is found in the SYSTOOLS library. It returns a list of defective PTFs that do not have the corrective PTF applied.

If this is the first time you have encountered this View I suggest you use the following statement to see what defective PTFs there are in your partition, and what columns of information are returned to you:

Creating a unique value across multiple partitions

This is another case of stumbling upon something that I am likely to use in the future. As I work in a multiple partition environment I need to keep rows of data unique not only with each file but also across partitions. Using an identity column will keep the rows unique within one table, but not across the same table in different partitions.

While searching for something else I found the SQL scalar function GENERATE_UNIQUE. This creates a 13 long bit data character value that is made up of an UTC timestamp and the system serial number. This can be used for a unique value in the table as each successive row that is added has a different timestamp value.

I can how what this looks like using the following statement:

Date arithmetic functions added to SQL

In the latest round of Technology Refreshes, IBM i 7.5 TR2 and 7.4 TR8, a number of date arithmetic functions were added to SQL.

I have to admit I was surprised by this as I did not consider the way I had been doing date arithmetic with SQL as lacking.

These new SQL functions are:

• ADD_YEARS
• ADD_MONTHS
• ADD_DAYS
• ADD_HOURS
• ADD_MINUTES
• ADD_SECONDS

They all have the same syntax:

Lookup IP address host using SQL

Added as part of the recent Technology Refreshes, IBM i 7.5 TR2 and 7.4 TR8, is a new scalar function that returns the hostname for an IP address.

The syntax for this scalar function, DNS_LOOKUP_IP, is simple, and can be used in one of two ways:

01 VALUES DNS_LOOKUP_IP(IP_ADDRESS => '123.456.789.12') ; 02 VALUES DNS_LOOKUP_IP('123.456.789.12') ;

Service extension for old releases of IBM i

I am grateful to the person who sent me the link to the IBM Support document "Service Extension for IBM i 7.3, 7.2, and 7.1", dated July 10, 2023.

I am not going to repeat what the document says, I will provide a link to it below.

The last dates that you can have support for these old releases is:

Use new RPG BiFs to see if input parameter was passed

I cannot remember how long ago it was when IBM introduced the %PARMS RPG Built in Function, BiF, that would return the number of parameters were passed to the program. This has been "refined" in IBM i 7.5 TR2 and 7.2 TR8 with two new BiFs:

• %PASSED:  Returns logical true if the parameter was passed
• %OMITTED:  Returns logical true if the parameter was omitted

To show how these BiFs work I created a procedure, that contains the new, and a RPG program to call the procedure.

Let me start showing by showing the procedure, Procedure:

Using SQL to get Network Attributes

This new View, NEWORK_ATTRIBUTE_INFO, was introduced as part of IBM i TR2 and 7.4 TR8. It shows the same information as the Display Network Attributes command, DSPNETA, and allows you to retrieve the same information as you would using the Retrieve Network Attributes command, RTVNETA.

I will admit I don't use this information much, in fact the only attribute I use is the Current System name. In a CL program I retrieve it using RTVNETA:

01 DCL VAR(&SYSNAME) TYPE(*CHAR) LEN(10) 02 RTVNETA SYSNAME(&SYSNAME)

Finding which fields were defined using a reference field

In my opinion one of the best things about the DDS database is the use of Reference fields. I can define a field in a one file, a "Reference file", and then use it to define fields in other files. These fields will inherit the properties of the "Reference field".

If I need to make a change to the database, changing the size on one field, I can compile all the files that use the "Reference field" and the change will made to the file.

But how can I know which files use a particular "Reference field"?

Fortunately a SQL View gives me the information I need to do this.