Getting Active Jobs data using SQL

With IBM i 7.2 released a whole lot of useful Views and Table functions that allowed us to gather, select, and view data that had otherwise only been available via a command or API. I have written about some of them, and today I am going to do so for another: ACTIVE_JOB_INFO, which basically gives me the save information as the Work With Active Jobs command, WRKACTJOB.

ACTIVE_JOB_INFO, found in library QSYS2, is a Table Function, therefore, it used in a slightly different way to a View. Perhaps the most obvious difference is that the Table Function has parameters, where a View does not.

Happy birthday RPG IV

This year contains a few significant anniversaries for RPG.

Today RPG IV has come of age, celebrating its 21st birthday. The first version of RPG IV came as part of OS400 V3R1, which was released on November 25 1994. Even though the code was still constrained by columns, the new Definition specifications (D-specs) was introduced, and I could now use variable names that were up to ten characters long. It also made it possible to use Date, Time, and Timestamp data types along with operations codes to be able to easily perform math with them. And I no longer had to use indicators for reads, chains, etc.

TR3 and TR11 available today

The latest Technology Refreshes for IBM i, TR3 for 7.2 and TR11 for 7.1, are available today. Both were announced last month, and you can learn what they contain here.

Technology Refreshes are delivered via PTFs from IBM. The PTFs for these TRs are:

Selective prompting of your CL commands

I have a number of CL programs I have written for myself to "speed up" things I do on a regular basis. For example, I work in an environment with multiple PowerSystems servers each with one or more IBM i partitions. If I need to move object from one to another I could type in the series of commands I use one at a time, find the parameters I need to change, enter the value I want into the parameter, and then press Enter. Being someone who likes to KISS (Keep It Simple Simon) I have developed programs were I use selective prompting on the commands to keep it simple.

What is selective prompting? It is when in a CL program a command is displayed prompted, and parameters can be seen and changed. I can have all the parameters displayed and changeable, or only some displayed and only some of those changeable. The other parameters remain hidden and unchangeable. It allows me to only display the parameters I want to, the ones I need to change. The screen shot below shows what I mean:

Getting information about User Profiles using SQL

As audit season comes round again there are a standard set of reports I need to prepare for the IBM i I am responsible for. Amongst them would be: a list of all the user profiles with the date they last signed on, user profiles with *ALLOBJ authority, etc.

I would use the Display User Profile command, DSPUSRPRF, with the outfile option to create a file I could then use Query to extract the information I wanted from it. The problem was that the data in the file is static.

Introduced in, I believe, IBM i 7.1 is just what I wanted a View USER_INFO, in the library QSYS2, which contains lots of useful information about user profiles. I can now get the information I want directly from this View. I can also build Views of the information I want over this View.

Using Relative Record Number with data files in RPG

Lately I have received a lot of emails and messages about using Relative Record Numbers, RRN, with data files in RPG programs. So many I have decided to write this post so I can refer people here rather than answer their questions individually.

Before I get started I just want to say I can think of no good business reason to use RRN to get and manipulate records from Physical or Logical files. The best I can recall the last time I wrote a program using them was way back in time in the late 1980s on an IBM System/36, one of the ancestors of the AS400 and IBM i. If someone asked me today to write a program using RRN I would ask them "Why?" and need a very good reason why.