Wednesday, February 27, 2019

Finding really big objects in my libraries quickly

quickly finding size of objects in library using object_statistics

The shout goes out: "The IBM i is at 92% of disk capacity! Delete as much test and work stuff as you can!"

We don't have time to run the best way to find the largest objects on this IBM i. At this point I am just concerned with the objects in my personal, work and test libraries.

I don't want to use the Display object Description command, DSPOBJD, as I will have to build a file, to then query, and that will only add to the amount of used disk space.

Fortunately Db2 for i provides me with a solution I can get data from immediately. In a prior post written about using the OBJECT_STATISTICS table function to find objects that have not been used for a long time. I can use the same table function to identify find the largest objects in my personal, work, and test libraries.

Friday, February 22, 2019

Another sign a TR for IBM i 7.3 is imminent

IBM has started teasing us with information about a new Technology Refresh.

Yesterday a new page was published in the "IBM i Technology Update" wiki for TR6 for IBM i version 7.3. At present the page is just a place holder with the words "stay tuned..." on it. You can visit it here.

What I find interesting is that there is no mention of an equivalent TR for IBM i 7.2 . This would be TR10.

Wednesday, February 20, 2019

Capturing system statistics using SQL

system_status_info sql view rather than wrksyssts command

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:

Wednesday, February 13, 2019

Returning rows where the last few characters are not numeric

checking last 3 characters if not number

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:

Wednesday, February 6, 2019

Screen at a time subfile using SQL

screen at a time subfile written 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.

Monday, February 4, 2019

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!

Friday, February 1, 2019

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!