Wednesday, July 28, 2021

USER_INFO_BASIC faster way to get data for user profiles

user_info_basic smaller faster user_info

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.

Tuesday, July 27, 2021

Security information from a SQL View

sql view to show all security information for partition

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 ;

Thursday, July 22, 2021

Object statistics wildcard makes it easier to list objects

wildcard in object name in object_statistics

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.

Wednesday, July 21, 2021

IBM updates Power branding

pibm rebrands power brand

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.

Wednesday, July 14, 2021

Retrieving specific audit journal data by SQL table functions

table functions to view audit joournal entries

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:

Tuesday, July 13, 2021

New columns added to Job Info table function

3 new coulns added to job_info

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:

  • JOB_NAME_SHORT
  • JOB_USER
  • JOB_NUMBER

For example I can get the information about my job by leaving out the parameters for the table function:

Thursday, July 8, 2021

Additions to the Active Job Info table function

changes to active_job_info

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()) ;

Wednesday, July 7, 2021

Combining data from the same file in multiple libraries into a view

view union joions convert number to date and varchar to char

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:

  1. Control the information they can get to, don't show them what they do not need
  2. If there are calculations or other mapping that needs to be performed perform them, rather than rely on them to do it and then find they have not done so in the right manner
  3. Map data from the files and tables into the type of data format they want
  4. Prevent them from being able to insert, change, or delete data from files and tables

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.