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.
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 ; |
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 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.
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:
The syntax for all of these table functions is the same:
The latest round of Technology Refreshes sees the addition of the same three columns to the JOB_INFO table function as were added to the ACTIVE_JOB_INFO table function.
These new columns break apart the full job name placing its parts into three new columns:
For example I can get the information about my job by leaving out the parameters for the table function:
In the past few Technology Refreshes there have been additions to one of my favorite Db2 for i table functions. I use the ACTIVE_JOB_INFO a lot, I have even created a program to display jobs in message wait in a subfile using it.
The first change I am going to show makes it easier to search for all jobs that have the same job name. Three new columns have been added, "breaking apart" the full job name into its individual parts:
SELECT JOB_NAME,JOB_NAME_SHORT,JOB_USER,JOB_NUMBER FROM TABLE(ACTIVE_JOB_INFO()) ; |
When anyone asks for information that they are going to "pull" from the IBM i into another database or to interface to another application on the IBM i I do not want them to access the "live" files for several reasons:
In my opinion the perfect tool for this is a SQL View. I can build it the way I want and tell them to use that.