I often find myself needing to search through tape catalogs to locate specific libraries or datasets, and manually reviewing DSPTAP spool files for each tape becomes tedious very quickly. So I decided to wrap the DSPTAP command in a SQL table function to make batch searches possible.
In IBM i environments, the traditional workflow for searching tape contents involves:
Running `DSPTAP` for each tape volume
Reviewing the resulting spool file
Manually noting which tapes contain the desired library
Repeating for the next tape
This process is time-consuming and error-prone, especially when you need to search across dozens of tapes to find where a specific library was saved.
So, I created a User-Defined Table Function (UDTF) that executes the `DSPTAP` command and returns the results as a SQL result set. This allows you to query tape contents using standard SQL, making batch searches and filtering much easier. Here’s the function signature:
Now you can search for a library across multiple tapes with simple SQL:
SELECT * FROM TABLE(SQLTOOLS.DISPLAY_TAPE('TAP01', 'VOL001')) WHERE SEQUENCE_LABEL LIKE '%MYLIB%';
One of the most critical aspects of this implementation is the call to `QSYS2.END_IDLE_SQE_THREADS()` at the beginning of the function. This call is essential due to a fundamental limitation of the IBM i tape management system. The `QSYSTAP` file, which underlies all tape operations including `DSPTAP`, cannot be opened by multi-threaded processes. The DB2 for i SQL Query Engine (SQE) typically maintains a pool of idle threads for performance optimization. However, these threads would cause the `DSPTAP` command to fail when attempting to access tape resources, as the tape subsystem requires single-threaded access. By calling `END_IDLE_SQE_THREADS()`, we force the database engine to terminate any idle SQE threads before executing the tape command. This ensures that the `DSPTAP` operation runs in a single-threaded context, allowing it to successfully open and read from the `QSYSTAP` file. Without this call, the function would fail with file access.
The function is declared with `NOT FENCED`, which means it runs in the same thread as the invoking SQL statement rather than in a separate thread. According to IBM documentation, FENCED functions run in a separate thread, while NOT FENCED functions may run in the same thread as the invoking SQL statement.I As we discussed earlier, the `QSYSTAP` file used by tape operations doesn’t support multi-threaded access. Since we’re already forcing single-threaded execution with `END_IDLE_SQE_THREADS()`, using `FENCED` (which would create a separate thread) would be counterproductive and could potentially cause issues. By using `NOT FENCED`, we ensure the function runs in the same single-threaded context where we’ve already terminated the idle SQE threads, maintaining compatibility with the tape subsystem’s threading limitations.
The function dynamically bulds the `DSPTAP` command with the provided parameters:
The command is executed using `QSYS2.QCMDEXC()`, which returns a result code. If successful (THERESULT = 1), the function queries the output file created in QTEMP and transforms the raw tape label data into a structured result set.
This UDTF demonstrates how SQL table functions can modernize traditional IBM i operations by wrapping CL commands in a SQL interface. The careful handling of multi-threading constraints through `END_IDLE_SQE_THREADS()` and the use of `NOT FENCED` to maintain single-threaded execution show the importance of understanding both the database engine and the underlying system architecture.
The result is a powerful tool that transforms a manual, time-consuming process into an automated, SQL-queryable operation, enabling better tape library management and faster data location workflows.
System security is becoming one of the most important issues that IT managers have to deal with. This also applies to IBM i systems. In fact, the days when the only access to systems was via a terminal are long gone. Now systems are at the center of complex ecosystems that communicate with each other in various ways (REST APIs, remote commands, database queries, etc.).
One of the enabling factors for establishing secure communication is undoubtedly the use of SSL/TLS certificates. In a previous post, we saw how to download and import them using DCM or the tools provided by the QMGTOOLS library. For those who don’t know, internet standardization bodies have decided to gradually (but at the same time drastically) reduce the duration of these certificates. Consider that today the standard is about one year for the duration of SSL certificates, while in 2029 the target is to make them last ONLY 47 days…
As you can imagine, if a service uses these certificates and they expire in the meantime, this causes a blockage of services and, consequently, of the business connected to them. This is why it is essential to have a monitoring system that, beyond the expiration date, also provides visibility of the applications affected by the certificate change…
First, let’s extract the list of certificates with private keys (those for server or client applications that require authentication):
SELECT CERTIFICATE_LABEL, VALIDITY_START, VALIDITY_END FROM TABLE (QSYS2.CERTIFICATE_INFO(CERTIFICATE_STORE_PASSWORD => 'XXXXX')) WHERE private_key = 'YES'
In this way you can also put a where condition on the days between current date and expiration date:
SELECT CERTIFICATE_LABEL, VALIDITY_START, VALIDITY_END, TO_CHAR(TIMESTAMPDIFF(16, CHAR(VALIDITY_END – CURRENT_TIMESTAMP))) AS DAYS_REMAINING FROM TABLE (QSYS2.CERTIFICATE_INFO(CERTIFICATE_STORE_PASSWORD =>’XXXXX’)) WHERE PRIVATE_KEY = ‘YES’ ORDER BY VALIDITY_END;
With this query you are able to extract every system service that is using SSL/TLS:
SELECT DESCRIPTION, APPLICATION_ID, APPLICATION_TYPE, CERTIFICATE_STORE, CERTIFICATE_LABELS FROM QSYS2.CERTIFICATE_USAGE_INFO WHERE CERTIFICATE_STORE = '*SYSTEM' AND CERTIFICATE_LABEL_COUNT > 0
Now let’s print the applications and associated certificates:
SELECT DESCRIPTION, APPLICATION_ID, APPLICATION_TYPE, CERTIFICATE_STORE, CERTIFICATE_LABELS, CERTIFICATE_LABEL, VALIDITY_START, VALIDITY_END FROM QSYS2.CERTIFICATE_USAGE_INFO X INNER JOIN TABLE (QSYS2.CERTIFICATE_INFO(CERTIFICATE_STORE_PASSWORD => 'XXXXXX')) Y ON X.CERTIFICATE_LABELS LIKE '%' CONCAT Y.CERTIFICATE_LABEL CONCAT '%' WHERE CERTIFICATE_STORE = '*SYSTEM' AND CERTIFICATE_LABEL_COUNT > 0
And you, do you have any service with SSL enabled and a real certificate monitoring tool?
A few articles ago I had talked about the pros and cons of Ubuntu on Power solutions, promising in some comments to do an article outlining the installation steps, and here it is.
Requirements
To proceed with the installation, it is essential to have created the partition. In my example, the partition was created with 0.1 core, 16 GB RAM and 50 GB on IBM storage presented to the partition via SAN. These are clearly my figures, you can change them if you want to give more resources or if you use different technology for storage access such as storage pools.
This is the detail on the disk created on the storage:
Now, once the infrastructure setup is done, so the partition sees the storage and the disk presented to it, we can proceed with downloading the Ubuntu image directly from the official site and upload it to the VIOS (see this documentation if you don’t know how). The last step before being able to turn on the machine involves the creation of a virtual optical, so again from HMC in the ‘Virtual Storage’ panel we go to select the tab for virtual optical and click on the add button selecting the VIOS on which the file with the operating system image has been uploaded.
Installation
Connect to HMC using SSH, choose the correct server in which partition is with VTMENU command and after that choose your partition:
Start partition in SMS mode (SMS mode is a function like the computer’s BIOS)
Now follow the steps proposed in these screens
If everything works fine, and you choose every time the correct option, grub will star in a few seconds
Ok, now the installation will proceed like in any other architecture, you need to choose which network card do you want to use, disk and file systems configuration and the first user for this server. Once the installation is completed you can restart the partition:
POST INSTALLATION CHECK
One of the most important things in my opinion is the multipath support that is natively installed, to check that run multipath -ll and it will show you all path for your disk. In my scenario, I willl have 4 active paths to my disk for each VIOS:
As another proof that everything works fine, as you can see Ubuntu gave me the same disk serial as the storage.
Take note that this short tutorial was written for Ubuntu, but also works fine with other distros such as Debian or any other distros that support PPC64LE architecture.
On IBM i systems, the SSH service is playing an important role in modernisation, it can be used, for instance, to take advantage of new software development tools such as VS Code For I, or it can be used in an innovative software release context using pipelines. SSH (or rather SFTP) is also playing a key role in securing data exchange flows by gradually replacing the plain-text transfers that used to use the FTP protocol, popular in the IBM i context.
At the moment SSHD server doesn’t have any kind of exit point that we can use in order to restrict or manage connections to this server… This doesn’t mean that is not possibile to make this server secure! In this article we will show how to restrict access to specific users (or groups of users) and log the access attempts that are made.
What do we need to know? Well, the SSHD server has the same behavior that it has on other platforms and therefore allows you to use the same directives, so if you are familiar with some other UNIX like platform, well in this case you won’t have any kind of problem. As far as logging is concerned, again we will use a very convenient and widely used utility on UNIX systems namely syslogd.
How to configure and activate SysLogD?
This service is automatically installed with the 5733SC1 operating system product. Activating the daemon is quite simple, you only need to submit a job that activates it as per this command: SBMJOB CMD(STRQSH CMD(‘/QOpenSys/usr/sbin/syslogd’)) JOB(SYSLOGD) JOBQ(QSYSNOMAX) (P.S. you need to put this command into you QSTRUP)
To check that’s everything ok, you need to look in your NETSTAT opt. 3 and in this way you need to find the UDP port 514 in listening status.
So, now that the deamon is active, you need only to change your SSHD configuration file in order to send to syslog server all entries:
Restart sshd server and check into /var/log/messages or /var/log/auth files
How to restrict access to ssh?
The logic behind the configuration of user restriction in ssh can be bi-directional, i.e. defining a list of users who are not authorised to connect and consequently all the others are, or defining the list of users who are authorised and the others are not. In my case, the choice falls on the second possibility by authorising access to this service to restricted groups of users.
