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.

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.

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.

Everything you wanted to know about dates, FAQ

1. Why do I get an error when I move zero to a date?
2. How do I validate that the date in the Date data type is a valid date?
3. If I have an ISO date and an USA date do I have to convert them to the same date format before I can compare them?
4. Why don't you initialize you dates using UDATE or *DATE?
5. Why do I get an error message when I move a date from one Date data type to another

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

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.

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

The Date data type was introduced in V2R3, but it was not until the introduction of RPGLE in V3R1 could we finally make use of it. Over those years I have used the Date data type extensively in my RPG programs and DDS files. I am surprised to still find developers who will not use them, or find others who do not fully understand the functionality of this data type.

Rather than address each item I have found I thought it would be better to write about the Date data type and how I can use them in my RPG code. This will be spread out over several posts, as to cover all I want to do is too much for just one. Let’s start today with what I consider the basics.

A better way of sending break messages

I have never liked the Send Break Message command, SNDBRKMSG, as it is not possible to send the message to a user without knowing which work station they are at. There is an alternative: the Send Message API, QEZSNDMG. This API allows me to send a message to directly to the user, without caring where they are. It is the only API I can think of that has an user interface that can be used interactively, and the same API can be used in a program to run without the user interface.

Below I am going to describe how this can be used with the user interface, and then how to call the API in a program.

Determining the last Friday/first Monday in the month

Sometimes a coincidence happens where two people ask for similar things that make me think that their question could become the subject for a post on this blog. Someone, via the internet, asked me if it was possible in RPG to write a program that would calculate the last Friday for every month. The following week I received a request, at my work, to run a report on the first Monday of every month. When two things like this come together it is a good subject to discuss.

Over the years I have found many ways to determine the day of the week. I have used CEE APIs (CEEDAYS and CEEDYWK), calculations of varying complexity (especially in RPGIII), and one software package (I will not name) had a file that had a record for every date from 1900 to 2100 with fields containing various date formats, week numbers, and the day name. For this scenario I wanted something simple, you know KISS (Keep It Simple Simon).

IBM i 7.2 TR3 / 7.1 TR11 announcement video

This is Tim Rowe’s, Business Architect for Application Development for IBM i, 7.2 TR3/7.1 TR 11 announcement webcast to COMMON Europe on Monday.

IBM i 7.2 TR3 and 7.1 TR11 is announced

The waiting is over we now know what is in latest Technology Refreshes for IBM i 7.2 and 7.1. 7.2's TR3 and 7.1's TR11 appear to contain the same enhancements to the development environment. I am not going to list them all here, just the ones that captured my eye.

For RPG-ers the biggest change is finally releasing RPG from the last constraint of the fixed format. Prior to TR3/TR11 I could only code between the 8th and 80th columns. Now my RPG code can start in the first position and finish at the end of the line. The only prerequisite is that **FREE is entered starting in the first column before my RPG code can start in the first position.

IBM i 7.2 TR3 and 7.1 TR11 being announced tomorrow?

From what I can tell the announcement of the latest Technology Refreshes, TR11 for IBM i 7.1 and TR3 for 7.2, will be made tomorrow, Monday October 5, 2015.

Having found the developerWorks pages for these Technology Refreshes last month, I noticed today that they have been updated with the enhancements for DB2 for i (SQL). Even as I am typing this post I see updates being made to these pages.