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:

New columns added to Job Info table function

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:

Additions to the Active Job Info 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()) ;

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

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.

Getting data from User Spaces made easy with SQL

The only way I have known how to write data to a User Space and read it has been by using APIs. With the last round of Technology Refreshes for IBM i 7.4 and 7.3 came an alternative to using an API to read.

There are two additions to SQL to help with User Space:

• View to list all User Space
• Table Function to view the data within an User Space

 

QCMDEXC scalar function added to SQL

I am sure everyone who has programmed using IBM i has encountered QCMDEXC. Most of us have used the QCMDEXC API, or the QCMDEXC procedure in SQL to execute CL commands from another program or a SQL statement. Included within the latest round of Technology Refreshes for 7.4 and 7.3 is a QCMDEXC scalar function.

A scalar function has to be executed as part of a SQL statement, a Select for example, rather than called directly. Like all the other scalar functions I know, it will return "1" when it executed successfully, and "-1" when it did not.

I have to admit I had to think for a while of a situation I could use this in. Then I remembered one where this could be a perfect fit.

Many years ago I came across a couple of programs that were part of a month end job to release and hold job queues that were used by the month end. There was a file that contained a list of job queues, the library they are found in, and what was called the "flag" field. All the flag was used for if it was not "1" the job queue was not processed.

I have built a SQL table to mimic that file:

Providing the same date in different formats using SQL

I have been working with a group of Windows programmers to interface data from an IBM i partition to a Microsoft SQL database. The biggest issue I had was the formats of the dates. These Windows programmers insisted I pass all the dates as character format including the slash character ( / ) as the separator, which is no big deal. What had us going around in circles was that the same date would need to be provided in different date formats depending on the part of the interface. As I am in the USA most of the time they wanted me to pass the date in MDY format (MM/DD/YY), sometimes in USA format (MM/DD/YYYY), and for a couple of dates I need to pass them in the European DMY format (DD/MM/YY).

The date was coming from a DDS physical file. To illustrate how I could simply provide the date in the formats they desired I have created a file, called TESTFILE:

01 A R TESTFILER 02 A TEST_DATE L 03 A TEST_STAMP Z

Getting results from dynamically built SQL statements

I have written about executing dynamic generated SQL in a RPG program before, all of the examples were to do something and not to return results from those statements.

This post will show how you can build SQL Select statements in a variable, execute them, and get the results returned.

I need to give credit to Hassan Farooqi for providing me with the information to show a simple way to do this. The code is based upon examples he shared with me. His were far more complicated than what I am showing here, but I just want to show simple of examples of what you can do. You can make yours as complicated as you like.

All of the examples have three steps: