Capturing system statistics using SQL

I thought this question would be a good reason to introduce another Db2 for i view that I find useful when monitoring IBM i:

I would like to collect the percentage CPU used and percentage system ASP used (from WRKSYSSTS) every day at 00:00 hours and save it in an outfile or print it to an spool file or send the values as a message to a user.

I could dump the contents of WRKSYSSTS to spool file, copy that to a data file, and then extract the information I want from the data file, or I could use an API. But why would I bother to go through all that effort to get the same information I can get from the Db2 for i view: SYSYTEM_STATUS_INFO

What information do I want? On the WRKSYSSTS display I only want the following:

Returning rows where the last few characters are not numeric

Sometimes I am asked a question by a colleague I think would be a good example to share in this blog. This week I was asked was there a way in a SQL statement to only return the records from a file where the last three positions are not numeric. For example:

• A2CDEF = Include
• ABC4EF = Exclude

There is an added complication as the string I need to test is contained within a ten long character field, but it can be of any length 1 - 10.

I quickly made a test file, TESTFILE, that I can use for these examples. The file contains the following records:

Screen at a time subfile using SQL

In my last post I gave an example of how to write a "screen at a time" subfile in RPG. When I finished writing that post I thought to myself how would it be possible to do the same using SQL.

There are two ways I could read, Fetch, data from the input file:

1. Single row fetch
2. Multiple row fetch

The single row Fetch is almost as inefficient as a RPG read operation, retrieving one record at a time from the file. The multiple row fetch is more efficient and the method I am using in this example.

The data file this program uses, PERSONP, is the same as in my last post, therefore, I am not going to describe it. If you care to refresh your memory then you should go to that post.

I made three changes to the display.

Presenting at OCEAN

I will be one of five presenters at the April 16 OCEAN user group meeting. I have been given 10-15 minutes, and have decided to spend that time giving some examples of what I consider are ways to get the most from SQL.

The meeting starts at 5:30 PM, and am I to assume as my name is first on the list I am the first person presenting?

Details of April's meeting.

I hope I will see you there!

Let's make February IBM i user group month

You regular readers know that I believe that we should all get involved with our local IBM i user groups.

To help you find your nearest group I have a list of all the user groups I have found on a page in this website: rpgpgm.com/user-groups

I review and update the list with the following information on a regular basis:

• Websites
• Facebook groups
• LinkedIn groups
• Twitter accounts

I am sure this is not a complete list of all the IBM i user groups in the world. I need your help to add to and revise this list. If you know of any user groups not on the list, need their information updated, or any of their social media accounts please use the Contact Form, right, to send me the information you have.

For example, in India there are many of IBM i folks. Are there really no user groups there?

Let's make February IBM i user group month!

Another type of subfile: screen at a time

All of the examples of subfiles I have given in previous posts have been "load all" subfiles. The subfile size is at the maximum, 9,999 records, and the entire subfile is loaded all at once. When loaded the display file controls the scrolling up and down within it, without any need for code in the program.

If give a "position to" field when a value is entered in it the subfile is reloaded starting at the nearest matching record and continuing onto the end of the subfile or available records from the input file. When I have presented this to users they have understood that if they enter "Hutchinson" then the subfile will start at the closest match. If they wanted the name "Hughes", rather than "Hutchinson", they would change the value in the "position to" field rather than expect to be able to page up through subfile records less than the value "Hutchinson" until they reached "Hughes".

In my experience of working with users, in many different companies, they understood and have been satisfied with this functionality. When I have given examples in this blog I receive messages telling me that this is not acceptable, the users must be able to page up to values less than "Hutchinson". I have until now ignored these complaints as they are from IT people, not from the people who use these subfile programs every day.

Today I relent, and am giving my version of a program that would allow people to page up to records before that entered in a "position to". To accomplish this I need to use a different type of subfile, a "screen at a time" subfile. This type of subfile will only load the same number of records that are displayed on the screen. If either the page up or page down keys is pressed the program has to reload the subfile with data.

More proof the new release of IBM i is coming

I mentioned earlier this year that there were signs that there would be a new release of IBM i announced this year. Images from IBM showed arrows to indicate that IBM i Next would happen in 2019.

I was curious whether IBM i Next would be another version 7 release, 7.4, or a totally new release, 8.1?

Poking around in the IBM KnowledgeCenter I found the answer, there is now a section of the site for IBM i 7.4. It does not, as yet, contain any information, it is more of a "place holder".

Padding columns using SQL

On cold morning in So Cal (we do get them) I was asked: "Is there any way to pad the values in FIELD1 in TESTFILE with zeroes?".

FIELD1 is a 20 long alphanumeric field in TESTFILE, despite its size only the first six characters are ever used.

....+....1....+....2 FIELD1 875 6891 3131 24187 40731 127963 684443

My mission was to add leading zeroes to the values that were not six long to make them that length.

Create SQL tables, views, and indexes with long names

I received a question asking me how to create a table with a long name that also had a good short name, and the long column names within the table would have meaningful short names too.

I can easily create a table with a long name and long column names:

01 CREATE TABLE MYLIB.THIS_IS_A_LONG_FILE_NAME ( 02 FIRST_LONG_FIELD_NAME CHAR(7), 03 SECOND_LONG_FIELD_NAME TIMESTAMP 04 ) ;

If I use PDM to look for the object I just generated it is not clear that this is the table from its generated short, or system, name.

Re-enable disabled IFS user id

When I am mapping an IFS folder to a Windows network drive I know that there are times where I will perform a typo on my password and the IBM i operating system will block me. This is where it gets interesting, I have disabled the user id I use trying to perform this connection, but I can still signon to the same IBM i.

When I create a user profile the command appears to create two:

1. What I call the "IBM i" that I use to signon to a 5250 ("green screen") session.
2. A NetServer user profile, that is used when I use the NetServer functions like mapping a Windows network drive to an IFS share.