Audit Journal Data Mart

This is something that caught my attention when the latest Technology Refreshes were announced: the ability to create a database of journal entry types entries. I had been doing something similar using the CREATE TABLE SQL statement and various of the Audit journal entry services, AUDIT_JOURNAL_xx. But these additions added functionality that makes it easier for me to extract the journal entries from the System Audit Journal, QAUDJRN.

The two new additions are:

1. MANAGE_AUDIT_JOURNAL_DATA_MART:  SQL procedure that uses the various AUDIT_JOURNAL_xx to create an output table.
2. AUDIT_JOURNAL_DATA_MART_INFO:  SQL View that displays information about all the files created with the MANAGE_AUDIT_JOURNAL_DATA_MART SQL procedure.

RPG Cafe updated with Fall 2024 enhancements

The RPG Cafe has been updated with the four changes that will be coming in the latest Technology Refreshes, IBM i 7.5 TR5 and 7.4 TR11.

These enhancements are:

• In preparation for the switch to the "1970" rule for six long dates, with two digit years, there will be a compile option when used allows you identify where a two digit year is used in the program or module, see here for more details.
   
• The ability to use the %PROC BiF in the ON-EXIT section of a procedure. Learn more here.
   
• Two new BiFs, %HIVAL and %LOVAL that will return the largest or smallest possible value that can be held in a variable. The details are here.

I consider these three great additions to the modern RPG language.

NHMUG calls it a day

I am always saddened to learn that an IBM i Local User Group, LUG, stops functioning.

Yesterday, I learned the New Hampshire Midrange User Group, NHMUG, announced on their website that they were disbanding. You can read their announcement here.

This just leaves one IBM i user-related organization in the New England region, the Northeast Users Groups Conference. NEUGC holds an annual conference, see their website for details of the 2025 conference.

If you have any updates for any other IBM i related LUG please let me know via the Contact form on the right.

For a list of all the active IBM i User Groups I have found check out the user groups page.

Fall 2024 TRs announced

The Fall Technology Refreshes for IBM i 7.5, TR5, and 7.4, TR11, were announced this morning.

Having a quick glance at the announcement documentation from IBM it would appear that the two have the identical changes:

• Db2 of i (SQL)
  • 14 enhancements
  • 9 new additions
• RPG
  • 3 new additions

Audit Journal Entry Services

I have managed to persuade the admins of the server I use to develop the code for these posts upon to give me access to the Audit Journal, QAUDJRN, this gives me the opportunity to write about several enhancements that have made to Db2 for i that makes getting information from QAUDJRN.

Over the past few releases and Technology Refreshes IBM has given us many Audit Journal Services. As QAUDJRN contains many different types of journal entries IBM has given us a specific Audit Journal Service for just about every journal entry type.

All of Audit Journal Services are called: AUDIT_JOURNAL_xx, where xx is the journal entry type. As of IBM i 7.5 TR4/IBM i 7.4 TR10 there are 41 of these Audit Journal Entry Services, out of a total of 76. That is 54% of all journal entry types covered. I do not know if the plan is to cover all of the job entry types, or not.

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