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: