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