Retrieving the CPU usage information via SQL

One of the many additions to the last release and Technology Refresh, IBM i 7.5 and 7.4 TR6, was a SQL View that returns one row of results of the usage of the CPU.

To most people it may not sound the most interesting information, but I have been asked if there was a way to get to this information so it could be written to an outfile, by a couple of people. Previously the only way I knew how to show the information was the Work with System Activity command, WRKSYSACT.

Work with System Activity DEV750 DD/DD/DD TT:TT:TT Automatic refresh in seconds . . . . . . . . . . . . . . . . . . . 5 Job/Task CPU filter . . . . . . . . . . . . . . . . . . . . . . . . .10 Elapsed time . . . . . . : 00:00:02 Overall CPU util . . . . : 80.1 Overall SQL CPU util . . . : 30.4 Average CPU rate . . . . . : 101.2 Current processing capacity: 1.00

SQL Views to help get information about Temporal Tables

When I ask people about their use of recent additions to the IBM i operating system my question about Temporal Tables are generally met by question "What are those?"

I am not going to explain what they are and how to use them in this post, as I wrote about them when they were introduced as part of IBM i 7.3 . You learn about them in the post I wrote when they introduced as part of 7.3, you can read about them here.

What I am going to explain in this post are two SQL Views that show you information about Temporal Tables:

Changing data within User Space with SQL

As part of the IBM i 7.5 and 7.4 TR6 release and refresh we have been given two new SQL Procedures that allow us to change data within an User Space. The first allows me to change the data within the User Space in character format, the other as binary.

Before I start showing these new Procedures I need a User Space. I can create this with SQL using the CREATE_USER_SPACE Procedure:

CALL QSYS2.CREATE_USER_SPACE('MYUSRSPC','MYLIB',131072,'YES', '','*USE','YES')

Creating a list of commands and their command processing programs

Update

I now use the method described in this post as, IMHO, it is easier to use a SQL View than it is this API.

---

The questions was is there a way to list the command processing programs for all of the commands in a library without having to look at each one individually with the Display Command command, DSPCMD. I decided to take this a small step further and want to have the name of the Validity Checking Program too.

Having looked at all of the lists I know of SQL Views, Table Functions, etc. I could not find one for commands. Alas, all of the CL commands only output to screen or spool file. It meant I had to use an API, QCDRCMDI. While writing this example I did have issues with QCDRCMDI. After over four hours the only way I found I can call the API it is call it from a separate program. More about that later.

As a result I have two programs:

Fall 2022 Technology Refreshes announced

The one day on my vacation I was beyond the reach of the internet, IBM announced their latest Technology Refreshes for IBM i 7.5 and 7.4 . As 7.3 is nearing the end of support there will be no more TR for it.

What caught my eye in these TRs?

As usual the Db2 for i has produced many new things for us to use. The first thing that caught my eye is the new REMOTE_TABLE, will I be able to access table functions on remote partitions using the three-part name? I cannot wait to try this.

Using SQL to check for level check, reuse deleted, and file size

There are three things about data physical files I always like to keep track of:

1. Level check status
2. Will file reuse deleted records
3. What is the maximum number of records the file can contain

For years I have used the Display File Description command, DSPFD, to create a lists of the files in a library, or libraries, and then read that file to check the fields for that information. As I can execute CL commands in ACS's Run SQL Scripts I can do the following:

01 CL:DSPFD FILE(MYLIB/*ALL) TYPE(*ATR) OUTPUT(*OUTFILE) FILEATR(*PF) OUTFILE(QTEMP/DSPFD_ATR) ; 02 SELECT PHLIB,PHFILE,PHDTAT,PHLVLC,PHRCDC,PHRUSE FROM QTEMP/DSPFD_ATR ;

Do not SQL insert if there is already a row present with the same key

The need was understandable. Every time I made a change to a file I need to insert the file's library into another table. I only needed the library name in the table once. And I did not want my statement to error.

The table is every simple, just one column for the library name:

01 CREATE TABLE MYLIB.TESTTABLE 02 (LIBRARY CHAR(10), 03 PRIMARY KEY(LIBRARY)) ;

Inserting the first record is not a problem:

Create and change User Spaces' attributes with SQL

In IBM i 7.5 and 7.4 TR6 the Db2 for i team introduced two new SQL Procedures for creating and changing the attributes of User Spaces, rather than using APIs.

• Create User Space
• Change a User Spaces attributes

I am going to show examples of these below.

 

Create User space

SQL equivalent of WRKSPLF, faster way of getting information about my spool files?

One of the first commands we all learn with IBM i is the Work with Spool Files command, WRKSPLF. With IBM i releases 7.5 and 7.4 TR6 comes a new SQL Table Function that is the equivalent of WRKSPLF.

The new Table Function, SPOOLED_FILE_INFO is found in library QSYS2, has parameters that match those of the WRKSPLF command. The syntax for the Table Function is:

SELECT * FROM TABLE(QSYS2.SPOOLED_FILE_INFO( USER_NAME => '*CURRENT', STARTING_TIMESTAMP => CURRENT_TIMESTAMP - 6 MONTHS, ENDING_TIMESTAMP => CURRENT_TIMESTAMP - 2 DAYS, STATUS => '*READY *HELD', JOB_NAME => '*', OUTPUT_QUEUE => '*LIBL/MYOUTQ', USER_DATA => 'SQL', FORM_TYPE => '*STD', SYSTEM_NAME => 'DEV750'))

These parameters and their WRKSPLF equivalents are:

Using SQL to get information about Journal Receivers

Earlier this year I wrote about using the QjoRtvJrnReceiverInformation API to get information about journal receivers. I noticed that in a recent batch of PTFs a new SQL View, JOURNAL_RECEIVER_INFO, which provides all of the information I am interested in. It is easier to get the information I desire from the SQL View than the API, so why would I continue to use the API?

The View JOURNAL_RECEIVER_INFO is found in the QSYS2 library. I recommend that you run the following statement to see all of the columns and information it contains:

SELECT * FROM QSYS2.JOURNAL_RECEIVER_INFO LIMIT 10 ;

End of support for IBM i 7.3 announced

I was guessing that this was going to happen either this or next month, and it has. In an announcement dated September 27, 2022, IBM revealed that they will discontinue support for release IBM i 7.3 on September 30, 2023.

7.3 was released on April 15, 2016. With it going off support on September 30, 2023, it means that it will have been an active release for seven years and five and a half months.

You can read the full announcement, including the other product coming to end-of-life, here.

If you have a Power8 or higher and you are on IBM i 7.3 you need to start finalizing your plans to update your operating system to either 7.4 or 7.5.

Determining which objects have changed since last save

There is an old library, I am going to call it OLDLIB1, that is still in everyone's library list. I needed to determine if there are files in this library that are still changed. I have used the word "changed" rather than "used" as they mean two different things. A file is used when it is opened in a program, the data within might not be changed. A file is changed when a record is added to the file, modified, or deleted. At present the entire library is saved using the SAVLIB command once a week. But if files are still being used we need to do a more often backup.

I can get to this information using the Display Object Description command, DSPOBJD, but with that I have to build an output file and then search the output file for the information I need.

Fortunately the SQL View SYSTABLESTAT contains the information too in the following columns: