Handling pipe separated data into a Db2 file

It started with a question:

If my file.txt is pipe filed separated, how can I make the records fall into the physical file to the corresponding fields.

A good question. Most of the time my incoming files are comma separated. The same methods I would use for those will work for pipe separated files too.

First I need a file with pipe separators. I made a copy of some of the columns from my PERSON DDL table to an output file in QTEMP:

01 CREATE TABLE QTEMP.OUTFILE AS 02 (SELECT PID,LNAME,FNAME FROM PERSON) 03 WITH DATA

Spring 2026 Performance Guide available

A new version of IBM's "IBM i on Power – Performance FAQ" was published at the beginning of this month, and is available for download.

The guide provides us with tips on how to measure, and improve, the performance of your IBM Power server, IBM i partitions, Db2 database, and programs that run within it.

You will find the document here.

I always download it and read the passages I feel are relevant to my situation. I recommend you do the same.

Migrate While Active Redbook

Migrate While Active, MWA, is one of the more exciting features recently introduced for IBM Power hardware and the IBM i operating system. It is tool that helps me to move IBM i workloads to new servers, data centers, or the cloud with near-zero downtime.

Basically MWA is performed in three steps:

1. Background Copy: A copy of the partition is made, while the source partition is fully online and active.
2. Data Sync: It constantly synchronizes all data changes from the live system to the copy.
3. Cutover: Once both match, I make the switch to the new partition.

This results in almost no interruption in service.

Saving using the ZLIB algorithm

ZLIB was introduced as an improved save compression algorithm as part of IBM i 7.4. With IBM Power10 processors the algorithm uses the on-chip Nest Accelerator (NX) GZIP, therefore, it is faster, less CPU intensive, and produces a smaller save file than other compression routines. I wanted to try it out to see how big a difference it would make.

First thing I need to do is to check if the IBM Power server and model I will be performing my tests upon is at least a Power 10. I can get the model number from the system values, but I cannot get the system type. The most convenient place I know to get that is from the API QLZARCAPI. After running the following:

01 CALL QSYS/QLZARCAPI

And I look in the job log I can see information on the first line of data:

SYSTEM INFO -> SYSTEM SERIAL NUMBER: XX-XXXXX . SYSTEM TYPE-MODEL: 9105-22A.

I admit I do not know all the system types and model numbers, therefore, I googled it and found that I am on an IBM Power S1022.

Prevent my user profile being used when others submit job

It is possible to submit jobs to batch that will run for another user profile by using the User parameter in the Submit Job command, SBMJOB. For example:

SBMJOB CMD(DLTF FILE(MYLIB/INVHIST)) JOB(TEST) USER(SIMON1)

The above shows that the current job is trying to submit a job to run with the SIMON1 user profile. I would consider to be a security risk. If SIMON1 has more authority than the current user, let's say that is NOTSIMON, then NOTSIMON can submit jobs that perform tasks they are not authorized to do, or do something bad that they will not be blamed for.

Fortunately in most cases NOTSIMON would receive the message:

SBMJOB CMD(DLTF FILE(MYLIB/INVHIST)) JOB(TEST) USER(SIMON1) Not authorized to user profile SIMON1.

I have encountered various companies where all the SBMJOB commands in their programs would use a "special" user profile for batch jobs, that had more authority than the average user. In this scenario when NOTSIMON ran a program that SBMJOB, and I looked at the calling job's job log I would see something like:

Giving the ability to see all of the objects on the system

I understand why IBM i developers should not have all object authority, *ALLOBJ, but at times I am frustrated by my inability to find objects in my partition. I do not want to do anything to them, just know that they exist. I have to find someone with a security office equivalent user profile and ask them to do a search for me.

I have found a way that this frustration can be removed. It will work on all partitions that are IBM i 7.5 or higher. I think what I am going to describe is included in the initial release. As none of the partition I have access to are running just base 7.5 I cannot check that this did not come in a Technology Refresh, TR.

Function Usages are way to be granted access to perform certain higher authorization functions, without being given that higher authorization. I was going through IBM's documentation about them when I came across:

Updates to the SYSDISKSTAT View and Table Functions

I have been remiss in writing about the updates in the past few Technology Refreshes to the SYSDISKSTAT View and Table function. Both the View and Table function return information regarding the spinning disk and solid-state drives, SSD, on your partition(s). While, IMHO, they can be used for different purposes, they do contain the same columns.

The columns I have identified that have been added since my previous post are:

• HARDWARE_STATUS:  The status of the "disk" unit. There are many different statuses, refer to the link at the bottom of this post to the IBM Documentation for what they all are
• IS_ZERO:  Returns whether all the pages on the disk unit are zero
• HOST_WWPN:  Hexadecimal decimal string to represent the resource's host world wide port name
• REMOTE_WWPN:  Hexadecimal decimal string to represent the resource's remote world wide port name
• UNIT_NVME:  Whether this is a NVMe unit. 1 = It is, 0 = It is not

Check if license product installed

I was performing some testing on a different IBM i partition where I wanted to take a spool file and generate a PDF in my IFS folder. I received an error saying that "Transformation services not loaded". Before I move my testing to another partition I wanted an easy way to check is "IBM Transformation Services for i" is installed.

I could check using the Work License Information, WRKLICINF, as the message suggested. But I my profile does not have sufficient authority to use it.

Fortunately there is an alternative I can use. I need to thank IBM Db2 for i chief architect, Scott Forstie, for bringing to my attention a SQL View that provides me with an easy to way to accomplish what I want.

Testing the new save compression ZLIB

After applying the latest round of CUM PTFs to a partition with IBM i 7.4 I noticed that the new save compression algorithm, ZLIB, is available. I was surprised as I was under the impression that ZLIB was only available for IBM i 7.5 . There is no mention of ZLIB in the help for the Save Objects command, SAVOBJ, data compression parameter's help. Was this an oversight by IBM introducing something that was not compatible 7.4? This made me want to test it to see if it worked in 7.4, and if it did how much were the savings compared to the other types of data compression.

I have before compared the savings I could get with the various compression algorithms. Now I could compare them all to the new ZLIB.

Copying save file from PC to PUB400.COM

If like me you are a user of PUB400.COM you will probably find this video useful. There are times where I have a save file I copied from another IBM i partition that I want to restore onto PUB400.COM, this video explains how to do it.

Click on the image below for it to open in YouTube.

 

November 2022 updated Performance FAQ

New versions of the Performance document and the Power10 performance optimization white paper were released last month.

You don't have to download, print, or read the whole documents. But I do think it is a good idea to peruse the sections to do with your favorite programming languages.

You can open the documents as PDF using these links:

• IBM i on Power - Performance FAQ (updated November 1, 2022)
• IBM Power10 performance optimization for IBM i (white paper)

IBM to end unencrypted downloads

Last week, November 23, 2022, IBM announced that on February 15, 2023, it will no longer be possible to download from the IBM Electronic Fix Distribution, IBM Electronic Customer Care, and IBM Fix Central using an unsecure connection:

• HTTP
• FTP
• DDP

IBM's servers already support the encrypted download protocols:

• HTTPS
• FTPS and SFTP
• DDPS

Your System Administrator should be able to easily make these changes well before the deadline. If they do not know how to make the change the announcement document gives them information of where they can go to get help.

IBM's announcement document can be found here.

IMHO it is about time IBM makes this change from unencrypted to encrypted downloads, as just about all other vendors only offer encrypted downloads.

Java errors with ACS

I want to start this to say that this is NOT an issued caused by the new version of ACS.

I use Microsoft Windows 11 on the computer I write these posts upon. Last week I applied the latest round of Window updates to my computer. After that whenever I opened any part of ACS I was presented with a Java error window, and then ACS would open.

Copying multiple spool files into one PDF

The task came in: I needed to take many spool files from an output queue and convert them into just one PDF. I have written about converting individual spool files into PDF, but now there was the need for the to be only one PDF.

My solution would need to:

1. Merge all of the spool files into one spool file
2. Convert the one spool file into a PDF in the IFS

Both of these I have done separately on many occasions; this was the first time to do them together.

I cannot use the spool files I used for this task, but I have two spool files in my personal output queue:

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.

When was the last IPL performed?

One of the IBM i operators and I were talking about the contents on one of the little use Power server partitions and the following question came up: When was the last time an IPL was performed on this partition?

I know I could find that information in Service Tools. But how would I do so if I am not authorized to use the Service Tools menu?

Fortunately SQL comes to my rescue.

When an IPL starts a job with the following job number is always the first to start: 000000/QSYS/SCPF

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

Latest IBM i roadmap

I just want to show the latest roadmap for IBM i. It shows how IBM has plans for the operating system we love beyond 2031 with the next two planned releases.

It also shows the extended support for releases 7.1 and 7.2, identified as SE1 for 7.1 and SE for 7.2 .

If you are using 7.1 or 7.2 I would seriously consider moving to the latest release supported by your Power server. There have been a lot of security upgrades in later releases, not having these could leave your IBM i partitions vulnerable to hacking or malware attacks.

What time was that subsystem started?

The question was simple:  Can you tell when a subsystem started?

I am going to give two examples. The first will show what I would do if you asked me what time the QPGMR subsystem was started. I only need to give an answer one time, and I do not need to write a program or code to do this.

In the second example I do want some code to show when the QPGMR subsystem was started. This would be used in a scenario where I would need to know frequently what time it started. I would add it to the Job Scheduler, run the job periodically, and it would send me an email with the date and time it started included within.

 

IBM i support roadmap update

I was at a presentation by Allison Butterill, Offering Manager for IBM i, where she shared the latest IBM i and Power support roadmap.

I always like to share these to counter the nay-sayers claiming that the "AS400 is dead", AS400 has been dead for more than a decade but IBM i and POWER live on.

Looking at the chart, below, you will notice that the dark blue bars are followed on by a light blue bar. This signifies when a release goes off support, but support can be prolonged by purchasing extended support.