Finding out if I am authorized to a function usage identifier

Function Usage Identifiers are a way to be authorized to a particular function, without having the authority changed on my user profile. I have found it is a lot easier to persuade the sys admins to grant me authority to the Function Usage Identifier for something, than it is to have my user profile changed.

In this post I am going to show ways you can find the Function Usage Identifiers, see which ones someone is authorized to, and finally have a way to check in a program whether or not you are authorized to one of the functions. Fortunately all of this can be achieved simply using SQL. What I will be using are:

Using SQL to get the first of the month for a date

I have used the LAST_DAY scalar function to return last day of the month many times. I was interested to see in IBM i 7.5 TR1 and IBM i 7.4 TR7 that a new matching scalar function was added, FIRST_DAY.

As the name suggests FIRST_DAY will return the fist day of the month of the date passed to the scalar function. For example:

01 VALUES FIRST_DAY(CURRENT_DATE) || ' thru ' || LAST_DAY(CURRENT_DATE)

I have used FIRST_DAY and LAST_DAY to return the first and last days of the month for the current date. Rather than have this information in two columns I have concatenated these together to produce one result. I know the SQL purist are going to criticize me as I have used the double pipe symbols ( || ) rather than CONCAT. They will, rightly, point out that this using the double pipe will not work on other Db2 platforms. As I will only ever run this code in an IBM i partition, I am not concerned by this lack of cross-platform compatibility.

February 2023 Power announcement

On Valentine's day, February 14, Ken King, General Manager for IBM Power severs, published a blog describing the latest IBM Power announcements.

Not a lot about IBM i, but I am always interested to learn what is going on with the IBM Power server family of hardware and the operating systems it hosts.

You can read his blog, titled "Fueling Digital Transformation with IBM Power", here.

Discovering remote journals with SQL

I have an ongoing "battle" to keep the journals and their journal receivers under control in the IBM i partitions I am responsible for. Part of this has been to find and report on various features and functions of the journals. A recent addition to SQL, as part of IBM i 7.5 TR1 and 7.4 TR7, has been a View that returns results about the remote journals in a partition.

What is a remote journalling?

Remote journalling, in very simple terms, will send journal transactions to other partitions. I could use it to replicate changes made to file on the master partition to other partitions, and use those to update the remote files and keep all of the copies in synch.

Identifying profiles with disabled Netserver access

If you have users using your partition's IFS there are times when one of them manages to disable their access. In 2019 I wrote about how they can re-enable their access themselves using the QZLSCHSI API. What if I want to be more proactive and identify the profiles who already have disabled their IFS access?

Fortunately the information is attached to their user profile, but not in a place that I can get to with any of the User Profile commands, for example CHGUSRPRF. But I can use the SQL View USER_INFO. This View contains the column NETSERVER_DISABLED, if the User Profile has disabled their Netserver access then this column will contain 'YES'.

The statement I would use is:

Finding all the fields that contain certain characters

The germ for the idea for this post came from a question I saw in a Facebook Group. The question was how to identify records in a file where a particular field contains "special" characters, using SQL.

As the question was specially about a DDS file, I created a file, TESTFILE, with one field, FIELD1. I am not going to give the code for this file, just its contents. I would use the following SQL statement to list the contents of this file:

SELECT FIELD1 FROM TESTFILE

The results from this file are: