Log4j vulnerabilities for IBM i

The Log4j vulnerabilities came to light earlier this month. I have not written about it as others have a better understanding of how this effects the operating system we love, and have written good articles about it too.

I was sent this link to an IBM Blog entry that describes what you can to remediate these vulnerabilities. And I thought I would share it with you. The blog post is general to all IBM products, not just IBM i and Power systems.

https://www.ibm.com/blogs/psirt/an-update-on-the-apache-log4j-cve-2021-44228-vulnerability/

Please share this link with your IBM i system administrators, and ask them to check if any updates need to be applied to your IBM products and environments.

Update

Mike Mayer sent me a couple more links that might help too:

• Security bulletin for Power HMC
• IBM Product Security Incident Reponse blog (PSIRT)

While these are not related to Log4j, Peder Udesen shared these vulnerabilities in RDi:

• Security bulletin for RDi
• How to get the patches and install them

Searching for member information with SQL

Db2 optimizes very large SQL tables by dividing the data contained within into partitions. These partitions store rows of data separately from other rows. In Db2 for i these are implemented using something we are all familiar with, members. This allows us, IBM i users, to use the Db2 view SYSPARTITIONSTAT to get information about members in the files and tables in our systems.

Where do we use members? The two scenarios that I am sure spring to all of our minds are in source files and in data files.

I often use SYSPARTITIONSTAT to search for source members that have the same name in multiple source files. This allows me to determine if a member name has already been used. Or if I have multiple versions of the same source member in different source files.

Using SQL to retrieve information about output queues

I have written so many posts there are times when I think I have written one about something useful, and I find to my surprise that I have not. I use the SQL view OUTPUT_QUEUE_INFO often, and I found that I have not written about it. This post makes amends for that oversight.

OUTPUT_QUEUE_INFO has been around since IBM i 7.2. It returns similar information to the Work Output Queue command, WRKOUTQ, whose output is familiar to everyone who uses IBM i.

Work with All Output Queues Type options, press Enter. 2=Change 3=Hold 4=Delete 5=Work with 6=Releas 9=Work with Writers 14=Clear Opt Queue Library Files Writer Status OUTPUT #SYSLOADX 0 RLS CGIDEV2 CGIDEV2 0 RLS DATAOUTQ DATALIB 0 RLS IASAUDIT IASUSR13 0 HLD

Taking one string and breaking it apart into five consecutive rows

The question was: How can I break apart a hundred long field into five twenty long fields and then have them returned, one after another, as separate result rows using SQL?

Obviously substring is going to be used, but how to return the resulting rows one after another?

Fortunately the easiest solution for this scenario came in earlier Technology Refreshes for the currently supported releases of IBM i, 7.4 TR4 and 7.3 TR10.

Before I show that I need to have a file with a field that is a hundred characters long:

01 A R TESTFILER 02 A FLD001 100A

Converting a spool file with bar code to PDF

I keep getting asked this question: If I generate a spool file with a bar code in it and I copy it to PDF will the bar code still display?

I wrote about copying spool files to PDF in the IFS in March 2014. What I am going to describe here is based on that post.

If the people who ask the question had read that post they would have discovered how simple this is.

Let me start with the externally described printer file, as I have not used Output specifications for prints in nigh on 30 years. The printer file's definition only needs two lines:

Adding 1 month to the end of the month and always get the end of the next month

I was asked how to make sure if I added 1 month to the last day of this month I would get the end of next month. If I take a date from a month and add one month to it I get the end of the next month.

In RPG if I add one month to January 31 I get February 28, the last day of the next month.

wkDate = d'2022-01-31' + %months(1) ; dsply wkDate ; DSPLY 2022-02-28

When I add one month to February 28 I do not get the last day of March, I get March 28.

wkDate = d'2022-02-28' + %months(1) ; dsply wkDate ; DSPLY 2022-03-28

Using SQL LIKE with a Subselect

This is the second part of a two part story. In the first part I described how I had downloaded a list of libraries from an IFS file to a DDL table just using SQL. In this part of the story I want to check any of job descriptions on this IBM i partitions contain one of these libraries.

In the "real world" there were 58 libraries. In this example I am only using five. To check what these libraries are called I can using the following SQL statement over the file I built in part one:

SELECT * FROM OLDLIBS

The libraries I will be using are:

Copying data from IFS file using SQL, real life example

This is the first of a two part story. I had found a number of libraries on an IBM i partition from the 2000s and 2010s for applications that had been used, but had been replaced by the main ERP application. These libraries were just wasting disk space, as during the conversion to the ERP all of the historical information had been copied from them to the ERP. I wanted to delete these libraries.

The list of these libraries had been circulated to all of the interested parties to confirm that no-one was using any of them. I copied the list from the email to Notepad, and saved as the file name: old_libraries.txt.

I uploaded the text file to my IFS folder using ACS's "Integrated File System" tool.

Searching for empty IFS folders using SQL

I was asked how to make a list of empty IFS folders using SQL. My first thought was to go to the table function IFS_OBJECT_STATISTICS. By using this table function I can retrieve a list of, for examples, just folders or the objects in a particular folder.

My thought was to do this in two steps:

1. Make a list of all the folders
2. Generate a count of the objects in each folder

New columns added to SYSCOLUMNS2 SQL View

I need to keep reminding myself to use the SYSCOLUMNS2 SQL View rather that SYSCOLUMNS as IBM tell us that it gets the results faster than the old View. IBM keeps enhancing the new View, and as part of the latest Technology Refreshes, IBM i 7.4 TR5 and 7.3 TR11, they added 14 new columns.

These new columns are particularly useful when working with DDS files as some of these columns deal with field attributes that are not available in DDL Tables and Views.

Prior to these additions I would use the Display Field Description command, DSPFFD, to generate an output file for the files I desired. This meant that this was always a two-step process:

1. Create the output file
2. "Read" the data in the output file

Now I can get the information I want in just one step, by accessing the data directly from the SYSCOLUMNS2 View.

User index information by SQL

As part of the latest Technology Refreshes, IBM i 7.4 TR5 and 7.3 TR11, we received a SQL View and Table function to use to get information about User Indexes.

I do not recall ever using a User Index, but some of the ERP I have worked with through the years had them. The IBM documentation describes them as:

A user index is an object that allows search functions for data in the index and automatically sorts data based on the value of the data...
They have an object type of *USRIDX and a maximum size of 1 terabyte. They help streamline table searching, cross-referencing, and ordering of data.

Until these TRs I would need to use APIs, ILE C, or MI instructions to access a User Index and the information contained within. Now IBM has provided us with the following:

• USER_INDEX_INFO View
• USER_INDEX_ENTRIES Table function

Retrieving the source of SQL objects

GENERATE_SQL_OBJECTS is a SQL procedure I have used which I have found very useful, but have not written about. It allows me to recreate the SQL statement that was used to create a SQL object or objects into a source file member. I could change many of SQL objects using the ALTER statement. But, in my opinion, it is easier to change the statement in a source member. I make sure that the create statement is CREATE OR REPLACE, create the changed object and old one is replaced.

There are two things I consider minor inconveniences to using this SQL procedure:

1. I cannot pass the name of the object to the procedure. All of the objects I wish to retrieve the create statement for must be placed in a DDL Table, that procedure then "reads".
2. The source member that is generated contains the create statements for all of the objects listed in the above Table.

I can use this with most SQL object types. In this example I am only using it with a few I have mentioned in previous posts.