Using file name from data area for reading IFS file

The idea of this post came from a question I was asked. The questioner had a scenario where the name of an IFS file they needed read is in a data structure. They were unable to work out how to use the value from the data structure as the path name to read of the IFS file.

Let me start using ACS's "Run SQL Scripts", RSS, tool.

Before I can read a file in the IFS I need to create it. I can use the IFS_WRITE SQL procedure to do so:

01 CALL QSYS2.IFS_WRITE('/home/MyDirectory/test.txt', 02 'First line', 03 END_OF_LINE => 'CRLF')

Finding logical files that exist in a different library to the physical

I am sure I am not the only person who wants any logical files in the same library as the physical file they are created over. I do realize that join logical files and SQL views can be built over files in different libraries, and they have always been handled in a case-by-case basis.

My senior System Administrator stumbled across a number of logical files which were in a different to their physical files, and wondered how many more there may be?

SQL to rescue and with a simple SQL statement I can give her that information.

Let me explain the scenario I will be using in these examples. I have a physical file, TESTFILE that exists in the library MYLIB. I built three logical files over it, each one was in a different library:

1. Logical file: TESTFILE1 in the library MYLIB
2. TESTFILE1 in the library QTEMP
3. TESTFILE1 in the library MYLIB2

I can display the database relationship between the physical file and these objects by using the Display Database Relations command, DSPDBR:

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

Deleting objects in the IFS with a SQL scalar function

This post is a two-for-one where I will explain how two of the additions that came in the latest Technology Refreshes go together.

The first is a new scalar function IFS_UNLINK, which is found in the SYSTOOLS library, that deletes IFS objects that are passed to it as a parameter. Behind the scenes it calls the unlink() C API. The scalar function returns the integer value that is returned by the C API. If it completed successfully, IFS_UNLINK returns a value of zero. If there was an error the scalar function returns an errno value.

The second addition, ERRNO_INFO, which is also found in the SYSTOOLS library, is a Table function that will return the description of the errno passed to it.

The easiest way I know to be able to show you how it works is to show an example of using it.

Comparing differences between objects in two IFS directories

While it has been possible to compare the contents of two directories in the IFS it has not been, well, simple. A new addition to Db2 for i, SQL, in the latest round of Technology Refreshes, IBM i 7.5 TR4 and IBM i 7.4 TR10. Makes it a whole lot, well, easier.

The new SQL Table function, COMPARE_IFS uses the output from the Table functions IFS_OBJECT_STATISTICS and IFS_OBJECT_PRIVILEGES to compare the objects in two IFS directories to one another. It will only check the following object types:

• Character special files, *CHRSF
• Directories, *DIR
• Name pipes, *FIFO
• Local sockets, *SOCKET
• Stream files, *STMF
• Symbolic links, *SYMLNK

I created two subfolders within my home directory, /home/MyDir, called subdir1 and subdir2. I uploaded some files into each one. Some were the same files, others were different files, and some files had the same names but different contents.

RPGPGM.COM-unity in So Cal

Last week I was at OCEAN's TechCon2024 conference, in southern California, and I took the opportunity to hand out some more of the RPGPGM.COM-unity ribbons.

What is the RPGPGM.COM-unity? You will have to click on this link to learn what it means, and who has ribbons.

Now possible to build SQL Alias over last member

The SQL Create Alias statement was enhanced in the latest round of Technology Refreshes, IBM i 7.5 TR4 and IBM i 7.4 TR10. Previously when you created a SQL Alias you had to give a specific member in the DDS Physical file. Now I can use *LAST to denote the most recent member in the Physical file, and what is really cool is if I add another member to the file the Alias will now use that member!

This will be clearer when I show my examples.

First I need a DDS physical file. I called this one MULTIMBR, and it is found in my library MYLIB.

A R RMULTIMBR A FIELD 100A

I compiled the above DDS Physical file with the following command:

01 CRTPF FILE(MYLIB/MULTIMBR) SRCFILE(MYLIB/DEVSRC) MAXMBRS(*NOMAX)

List all object types using SQL

Have you ever wondered how many different types of objects there in IBM i? And what the types of objects are?

The latest Technology Refreshes give us a SQL View that contains that information, and we can query it to answer those questions.

The SQL View is called SYSTEM_OBJECT_TYPES, and it is found in the QSYS2 library. It has three columns:

1. OBJECT_TYPE:  Object type
2. TEXT_DESCRIPTION:  Description of what the object type is
3. CATEGORY:  There are two categories:
   IFS - object is in the IFS
   LIBRARY - object resides in a library (not an IFS object)

New functionality added to RPG's SND-MSG op code

Two years ago the Send Message, SND-MSG, operation code was added to RPG. In the latest round of Technology Refreshes a number of new options have been added to SND-MSG and the Target, %TARGET, built in function.

To oversimplify it SND_MSG consists to three parts:

1. Type of message to send
2. Message text or variable
3. The target for the message, which is the %TARGET built in function, BiF. This is optional

For example:

SND-MSG *INFO 'Message text goes here' %TARGET(*SELF)

Deleting old device definitions

Last month I wrote about a way to list all of the unused virtual devices in your partition. In response to the article I received a question: "Is there a way to then delete these devices, other than manually going through? One system I just did has over 5,000 devices...."

The answer, of course, is yes there is. Before I give my final result let me go through the steps I did to create a SQL script to delete the unused virtual devices.

I can identify the virtual devices using the Object Statistics SQL Table function. I do not want to repeat myself. For the details read my previous article.

I decide to create a Common Table Expression, CTE, as it was just "neater". In the first part of the statement I would create a virtual table of all the eligible virtual devices, and in the second I would delete them. To start with I just want to make sure I gather the data I expected, therefore, my first statement did not do any deleting.

Constant keyword added to RPG definitions

As part of the latest Technology Refreshes, IBM i 7.5 TR4 and 7.4 TR10, comes an addition to the variable and data structure definitions. It is now possible to use the constant keyword as part of those definitions to protect them from being changed.

Why would I want to do that?

There are times I want to use a variable, rather than a constant. It might be to contain a date. I would not want this to be changed, either on purpose or by accident.

Below is an example:

01 **free 02 dcl-s StartDate date inz(*sys) const ; 03 dsply StartDate ; 04 StartDate = d'2024-07-04' ;

Create an Excel spreadsheet from a CL program

It has been possible to create a Microsoft compatible Excel spreadsheet using SQL since the last Technology Refreshes. In my example I demonstrated how you could do this within a RPG program.

A few months have passed and I have been asked how to do the same within a CL program. With a minor change I will show it is possible.

Let me start by showing the code for this program: