Changing user profiles using SQL

I have to admit when I read that a SQL table function to change user profiles had been introduced in the latest Technology Refreshes for IBM i 7.4 and 7.3 I was skeptical as to whether it was something that I would find practical and useful. Having had some time to play with it I can now appreciate its usefulness.

But before I get to that, let me explain how the CHANGE_USER_PROFILE table function works.

The table function will only change certain information of a user profile. The parameters you can change are:

CTE example: Calculating the maximum length of 80% of the rows in a column

I don't have a good example of using a Common Table Expression, CTE, in this blog so I decided to write this one as an example of how I can determine the maximum length of 80% of the rows in a SQL Table.

It does sound a bizarre thing to calculate, but it was used in the post from last week where I discussed the fastest way to copy data from one file or table to another. IBM's recommendation was if you use a VARCHAR to define a column in a table using the ALLOCATE it could reduce the number of I/O operations that would be performed. The suggested allocation for any column is that the number of bytes allocated will include 80% of the rows in the table.

I decided to write the SQL statement I will show, below, to perform that calculation. I broke up what I wanted to do into "parts", this would allow me to test my results before going to the next "part".

Latest IBM i roadmap

I just want to show the latest roadmap for IBM i. It shows how IBM has plans for the operating system we love beyond 2031 with the next two planned releases.

It also shows the extended support for releases 7.1 and 7.2, identified as SE1 for 7.1 and SE for 7.2 .

If you are using 7.1 or 7.2 I would seriously consider moving to the latest release supported by your Power server. There have been a lot of security upgrades in later releases, not having these could leave your IBM i partitions vulnerable to hacking or malware attacks.

Finding fastest way to copy data – part 2

A couple of days ago I published a post about testing a friend had performed looking at the fastest way to copy data from one table to another. I thought his results were really interesting, which is why I shared them.

But there are another couple of ways I would have tested. In this post I will describe them.

With all the example programs I am not displaying the entire program, just the interesting parts.

The tests were performed over the same SQL tables as before:

Finding fastest way to copy data from one table to another

This post is based on the presentation Thomas Leo Swint made to the Central Texas IBM i User Group last night. Thomas performed a series of tests looking to find the fastest methods to "read" several million rows of data, and also to "read" the data from one table and output it to another table. When he shared his results with me, I was surprised what he had discovered. I asked him to make a presentation to the CTXiUG, and for his permission to share his results here.

Thomas needed to copy millions of rows of data from one set of SQL tables to another. This process needed to take the least amount of time possible. He created four test tables with the character columns defined in different ways. He compared the time taken to "copy" rows from the tables to another table using:

Finding records with a certain character in a field using SQL regex

This question came from a colleague of mine. He was trying to identify which records from a DDS file contains one of several special characters within a particular field. To make it a bit more interesting these special characters could be in any position of the field, maybe the first position, last or in the middle. He had tried several SQL statements but had not managed to create one he felt comfortable with.

I am sure you can understand why I cannot share the file he was using. I have created my own file to illustrate the problem and my solution. I am sure you will not be surprised to find I called it TESTFILE.

To see the contents of the file I can use this simple SQL statement:

01 SELECT * FROM TESTFILE ;

And the following results are returned:

Symbolic link location added to IFS Object Statistics

The IFS_OBJECT_STATISTICS table function has always returned whether the object in the IFS is a symbolic link, *SYMLNK, but not where its links. As part of the last of the latest round of Technology Refreshes a new column has been added to the table function's results to give us that information.

I feel I need to explain what a symbolic link is before I give an example from IFS_OBJECT_STATISTICS.

Symbolic links are a special kind of object only found in the IFS. They are identified by their own object type, *SYMLNK. Think of them like a Windows shortcut. These files contain the path name to another file or location in your partition.

You create symbolic links with the Add Link command, ADDLNK:

USER_INFO_BASIC faster way to get data for user profiles

This is going to be a short post about a new SQL View, USER_PROFILE_BASIC, that was released as part of the latest round of Technology Refreshes. As the name suggests this new View contains a subset of the columns found in the USER_INFO View.

By having less columns USER_INFO_BASIC fetches and calculates its results faster than USER_INFO.

The syntax for this statement is simple:

SELECT * FROM QSYS2.USER_INFO_BASIC ;

The column names are the same in USER_INFO_BASIC as they are in USER_INFO.

Security information from a SQL View

Introduced as part of the latest Technology Refreshes for IBM i 7.4 and 7.3 is a SQL View which lists all of the security information for the partition I am using.

The View SECURITY_INFO only has one row, and shows the same information as the Display Security Attributes, DSPSECA, and Display Security Auditing commands, DSPSECAUD. Many of the columns in this View are taken from the security system values.

To be able to see the contents of the audit receiver columns I must have Object operation authority, *OBJAOPR, to the Audit journal, QSYS/QAUDJRN, or to the attached journal receiver.

In its simplest form to see all of the security I can just use the following statement:

SELECT * FROM QSYS2.SECURITY_INFO ;

Object statistics wildcard makes it easier to list objects

A small enhancement to the Object Statistics table function in the latest round of Technology Refreshes is going to make it easier for me to get the results I want. Previously if I wanted to get the results for a group of objects I would have to ask the table function for a list of all the objects in the library, and then limit the returned results only to the ones I want using a where clause.

SELECT OBJNAME,OBJTYPE,OBJATTRIBUTE FROM TABLE(QSYS2.OBJECT_STATISTICS( OBJECT_SCHEMA => 'MYLIB', OBJTYPELIST => '*ALL', OBJECT_NAME => '*ALL')) WHERE OBJNAME LIKE 'TEST%' ORDER BY OBJTYPE,OBJNAME ;

I am sure you can appreciate that this is not the most efficient way to get the results I want, a list of objects that start with the letters: TEST. I have had to return the results of all the objects in the library MYLIB, and then select from that list the rows I want, where the name start with TEST.

IBM updates Power branding

IBM has updated their branding for the Power range of servers and architecture. I could also say the processor has been rebranded too, but in my opinion POWER9 has been replaced by the newer, faster Power10 processor. They have designed what I think is a pretty cool logo for the Power10, that I have placed to the right of this text.

Retrieving specific audit journal data by SQL table functions

As part of the latest round of latest Technology Refreshes for IBM i 7.4 and 7.3 are four SQL table functions that allow me to be able to easily get the following information from the system audit journal:

• Authority failures (journal entry type AF)
• Authority changes (CA)
• Ownership changes (OW)
• Password entries (PW)

The syntax for all of these table functions is the same: