Copy source members to the IFS

Someone asked me how I would write a program to copy all of the members in source files to separate files in the IFS, where they could then me imported into a GiT. I can see a program like this being more useful overtime as more people move from using source files to using a GiT repository.

In this example I will be copying all the source members from the source file DEVSRC in the library MYLIB to the IFS directory /home/MyFolder. The files in the IFS would all be named in the following way: source_member_name.source_member_type, for example: TESTRPG.RPGLE

My first attempt at creating a program to do this was to use RPG. I was going to use the CPYTOIMPF command within the QCMDEXC Scalar function. Alas, several hours of creating program I received the following message when running the RPG program for the first time:

New View lists all file members

If I wanted to get a list of members in a file with SQL I always had to work with the SYSPARTITIONSTAT View, which was not the purpose it was designed for. In the latest latest Technology Refreshes a new View has been added that is designed for information about members, SYSMEMBERSTAT.

The new SYSMEMBERSTAT View is in the library QSYS2 and contains all the columns I could want to know about members in a file.

I always recommend if this is the first time you are using a View you should look at all of the columns and determine, for yourself, which ones are important. The following statement displays all the columns in SYSMEMBERSTAT:

Improvements to the SQL scalar function that sends email

As part of the latest Technology Refreshes a number of improvements have been added to the SEND_EMAIL scalar function.

What are these:

• New parameters for carbon copy (CC) and blind carbon copy (BCC)
• Up to twenty email addresses in the email, cc email, and bbc email parameters
• Up to ten attachments from the IFS can be attached to the email

All of which are useful additions to SEND_EMAIL.

Let me jump right to giving an example of using more than one email address and the new cc and bbc email parameters:

IBM i: Integrate and innovate

IBM published a document last month, August 2024, called "IBM i: Integrate and innovate", with the subtitle "An executive guide to the strategy and roadmap for the IBM I integrated operating environment for IBM Power servers". I think it is great that IBM publishes documents like this. I read them all, and share them with you too.

It is a 31 page document that IMHO you should share with your colleagues, managers, and executives. I will be doing so myself today.

The document can be reached on the IBM website by clicking on the link here.

Extra columns in a Select statement but not in the group by

I struggled with the title of this post to make it something that explains the content, without it being too long. I am not limited for length in the body of this post so I can explain in more detail my scenario: I want to have columns that are listed in my results for a Select statement that are not in the GROUP BY clause.

This is something that has been made easier with a new Scalar function added in the latest round of Technology Refreshes. First let me give you examples of my scenario.

What I am going to do is to take use my PERSON DDL Table and make a count of the places of birth of the people. As these are people from the British Isles, I will also include the columns for the county and the country (England, Ireland, Northern Ireland, Scotland, or Wales) from a second Table, TOWN.

Retrieving Printer File information using SQL

This is one of the additions that came as part of last round of Technology Refreshes that I was excited about. Previously If I need to get information about printer files I would either have to use an API or the DSPFD command. Now I have an easy way to get information about any printer file using a SQL View.

The new SQL View is called PRINTER_FILE_INFO and is found in the SYSTOOLS library.

I always recommend that you run the run the SQL statement to see all of the columns in the SQL View for yourself. That way you can make your determination which columns you find useful to your situation. You can do that with the following statement. I have a limit of returning ten results as I found that if you want to return all the results from the View it may take some time to do so.

Using SQL to create, update, and get from Environmental Variables

Within the latest database PTFs that came as part of the last Technology Refresh were a couple of new Scalar functions that can be used to do things with Environmental Variables. They are named after the APIs that can be used to put and get information from the Environmental Variables. These new SQL Scalar functions are:

• PUTENV:  Creates or changes the Environmental Variable. Equivalent of the following commands: ADDENVVAR and CHGENVVAR, and the putenv API
• GETENV:  Retrieves the value from the Environmental Variable. Equivalent of the getenv API

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

New version of ACS, 1.1.9.6, out now

 

The original contents of this page have become obsolete, go to this page for up-to-date information.

 

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.