Learning about active queries

Have you ever wanted to know what is going on within an active SQL Query Engine, SQE, query?

If, like me, you do there is a table function that shows all kinds of interesting information.

The table function ACTIVE_QUERY_INFO has been around since IBM i 7.3 . It has four parameters that allow you to select the results you want returned.

1. JOB_NAME:  This is the parameter I have found the most useful. Rather than it using the qualified job name it uses the job name part of the job name. Therefore I can just search for jobs with wildcards, for example 'DSP1*'. I can also use '*' to return results for the current job only, and '*ALL', or blank, to return the results for all active jobs.
2. JOB_USER:  The user name from the job name. A wildcard can be used here too.
3. JOB_NUMBER:  The job number part of the job name. '*ALL' can be used here.
4. USER_NAME:  The user name for the job, which may not be the same as the job user name.

November's presentations

Monday 4 - Wednesday 6 November I will be at COMMON NAViGATE conference in Toronto, Canada. This is my last in-person conference of 2024.

You can learn more about the conference, and register, by clicking on this link here.

And you can see the presentations I will be giving by clickin here.

Tuesday November 19 is the next meeting of the Central Texas IBM i User Group, CTXiUG. We are fortunate to have Patrick Behr as our guest. He will be giving a presentation titled "SQL 101 Bullet Train".

To learn more about this presentation and to register for it you can click here.

This meeting is online and free.

Special Authority Data Mart

A second data mart was introduced as part of the last round of Technology Refreshes that allows me to capture and store information about the special authorities given to user profiles in my partitions.

The Special Authority Data Mart is held in the table SPECIAL_AUTHORITY_DATA_MART, which resides in the SYSTOOLS. The table is a Materialize Query Table, MQT. This means that the contents of the table are not dynamic, changed when changes are made to the user profiles, it is refreshed using the REFRESH TABLE SQL statement. If you don't know what that is I will show how to use it in my examples below.

The most basic SQL statement to retrieve the data from the Table is:

01 SELECT * FROM SYSTOOLS.SPECIAL_AUTHORITY_DATA_MART

Retrieving the library that contains the currently running program

The idea for this post came from a question. The questioner had a situation where, due to a mixed-up library list, someone had run the wrong version of a program from another library. They already had the program's name on their display files, and they asked was it possible to retrieve the library the current executing program is in, and add that to the their display files.

After some discussion we decided that any change would be needed in both RPG and CL programs. Fortunately, this is simple in both languages.

Before I start explaining my RPG and CL code I need to have a display file within which are fields for the program and the program's library. I created a simple display file to do this, called TESTDSPF:

01 A DSPSIZ(24 80 *DS3) 02 A R SCREEN 03 A 3 2'Program name . :' 04 A PGMNAME 10 O 3 20 05 A 4 2'Program library :' 06 A PGMLIB R O 4 20REFFLD(PGMNAME *SRC)

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: