Wednesday, November 25, 2015

Getting Active Jobs data using SQL

active_job_info table function wrkactjob

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.

Friday, November 20, 2015

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:

Wednesday, November 18, 2015

Selective promoting of your CL commands

selective prompts ?? ?*

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:

Wednesday, November 11, 2015

Getting information about User Profiles using SQL

sql view user_info

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.

Wednesday, November 4, 2015

Using Relative Record Number with data files in RPG

relative record number, rrn, in rpg using recno and inzpfm

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.

Wednesday, October 28, 2015

Using SQL to get information about Job Scheduled entries


Am I the only one who has found it difficult to get information about what is in an IBM i's Job Scheduler?

I can use the Work Job Schedule Entries command, WRKJOBSCDE, to create a print out of the details. But I just want a quick and simple way to quickly and easily find what I want.

Someone at IBM must have been listening as the View SCHEDULED_JOB_INFO was added in IBM i 7.2. I can now easily get to, pick and choose the information I want using a SQL SELECT statement. I will give some examples of what I find helpful, and show you what you can do using this View.

Monday, October 26, 2015

IBM i and Open Source presentation

This is a presentation of a Webex made by IBM's Tim Rowe to the COMMON User Group last Monday, October 19 2015, about Open Source on the IBM i.

Wednesday, October 21, 2015

Everything you wanted to know about dates but never dared to ask, part 2

Date built in functions, BIF, %DAYS %MONTHS %YEARS %DIFF and %SUBDT

In part 1 of this series I covered what I consider to be the basics:

  • Date formats
  • Defining dates
  • Moving values in and out of dates

In this part I am going to be covering the RPG built in functions for Date data types I use.