Thursday, March 14, 2019

IBM i 7.4 section now in KnowledgeCenter

ibm knowledge center page for coming ibm i release 7.4

In January I found signs that a new release of IBM i would happen this year. This week I found another sign when I discovered that the KnowledgeCenter now has a section for IBM i 7.4.

Alas, it is just a "place holder", with no information is within it, but it is another sign of the pending new release.

If you are interested in visiting the new section click here.

Wednesday, March 13, 2019

Using SQL to check if a batch job is active

sql to find if job is active in subsystem

A colleague asked me if there was an easy way, using SQL, to tell if a job was active in a subsystem. This subsystem, I am going to call it TESTSBS, should contain two jobs that remain active all day, copying and sending data between various IBM i partitions. During the day-end process the jobs are ended, and then resubmitted after the backups.

Recently there have been problems with these jobs. If one of these jobs errored the system operators would answer the message with "C", which would end the job and the data would not be transferred until someone else noticed. Or the jobs would not end during the day-end process, therefore, after the backups completed two new versions of the job would be submitted resulting in four jobs, two of each.

My colleague had looked into ways of determining which jobs were active in the subsystem. During the day if one of the jobs was missing it could be resubmitted. After the backup finished only if the job was not active would it be submitted. All of the methods she had come up with to determine if the jobs were active were, in her opinion, too complicated. Which is why she came to me.

Tuesday, March 12, 2019

Does end of Google+ mean the end of this website?

Over the past week I have received several communications asking if the end of Google+ means that this site will also be closing.

This site is hosted on another Google product, Blogger. Google's commitment to the Blogger platform continues unabated. There have been some minor changes to Blogger because of Google+'s demise, but none of these will effect your viewing pleasure of this website.

If you have included me in your Google+ circle and want to continue that relationship feel free to send me a Facebook friend request.

Wednesday, March 6, 2019

Copying part of an existing spool file to a new one

copying part of one spool file to a new one

I was asked this week how to send the last few pages of a report to a remote output queue. The queue had been configured in a way that even though I could change the range of pages to print, number of copies, etc. despite this a single copy of the entire report would be printed. Such a waste of paper when the report was 450 pages.

The way I overcame this is to create a new spool file, copying data from the original. In a previous post I have shown how to copy multiple spool files and have them print as one. The spool files are copied to a physical file, then the data from the physical file is copied back to a printer file. If I only want a few pages I just need to find the relative record number of the start and end of the section I want to print, and then enter that data when I use the Copy File command, CPYF. For the new spool file to look like the old I also need to know the page width of the original spool file, the characters and lines per inch, CPI and LPI. I can find these myself by looking at the original.

It is not a big deal to do this manually, but my philosophy is if I need to do it more than a few times then it is best to write a program to do it for me. This example program is based upon a program I wrote to do this, make a new spool file that contains a subset of some of the pages from the original spool file, that can then be sent to the remote output queue.

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:

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!

Wednesday, January 30, 2019

Another type of subfile: screen at a time

screen at a time subfile

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.

Sunday, January 27, 2019

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.

Arrow shows IBM i next coming 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".

Wednesday, January 23, 2019

Padding columns using SQL

padding fields 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.


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

Wednesday, January 16, 2019

Create SQL tables, views, and indexes with long names

create long and short names for sql tables, indexes, and views and their columns

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:

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.

Thursday, January 10, 2019

Re-enable disabled IFS user id

enable disabled netserver ifs user id or profile

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.

Wednesday, January 9, 2019

Create and share IFS folder with Windows

creating ifs folder and sharing to microsoft windows

In my last post I wrote about making it easier to copy IBM i files to an IFS folder. I have my personal IFS folder set up as a Microsoft Windows network drive, which allows me to open the mapped folder in Windows Explorer, and just drag-n-drop files from the mapped network drive into folders on my PC. Here I am going to describe how you can do the same.

I am going to use Client Access's Operations Navigator, I could use IBM Navigator for i instead, but I have found that not many sites have not enabled it. To be able to do what I am describing you are going to need the full version of Operation Navigator, and the appropriate authority to create folders and shares in the IBM i partition you use. If you do not have the full version of Operations Navigator and/or you do not have the necessary authorities feel free to share this with your System Operation or Tech Support person who can perform these steps for you.

This process can be divided into three steps:

Tuesday, January 8, 2019

Making copy a file to the IFS easier

automate copy of file to ifs

Often I find it easier to analyze the contents of a file using Microsoft's Excel than using the IBM i based tools. I take the file, or table, and copy it to my folder in the IFS, and then copy it from there to my PC. A colleague asked how did I do this so quickly, and having showed him how I do, he recommended that this would be a good subject for a post in this blog.

I am surprised how few developers I talk to build their own set of programs to automate tasks they perform every day. Too many times I see these people type in command names, prompt with F4, and fill in the same parameters time and again to do the same task. I admit I don't want to do that, if I need to perform the same task more than just a few times I will create a program containing all the commands I need. Then I can call the program with just a few parameters, and the program will do everything. If I want to copy a file to the IFS I just need the file and library, the rest will always be the same.

Wednesday, January 2, 2019

Creating a screen with two side-by-side subfiles

2 subfiles next to each other

I have written about subfiles, and even a screen with two subfiles on it. When I was asked about a screen with two subfiles, side-by-side, I was intrigued how to do it.

The first problem to overcome is that if I code two subfiles, one to occupy the left side of the screen and the other the right, whichever subfile's control record I write last will overlay the other.

After using Google I could find reference to an example of using DDS windows to contain the subfiles, but the links to the article only returned a "page not found". With that hint I created the following.

Let me start by saying that this display file is a "stripped down" version. I have removed a lot of the things I put in display files so not to clutter this example. Let me start with the file level keywords and a record format I have called HEADER.

Tuesday, January 1, 2019

Welcome to 2019

A new year has started, and I am always excited to look back on the old year and forward to what the new year will bring.

The past few years have been an exciting time for IBM i community. Just in the life of this blog I have seen RPG undergo a radical make over, and numerous wonderful things added to Db2 for i, or SQL.

You feel the same as the most popular articles from 2018 have been:

  1. Creating a XML file
  2. Processing simple XML using XML-INTO
  3. Using RPG data structures with SQL insert and update
  4. Displaying more than one subfile at a time
  5. Copying any data to and from a file in the IFS

Have you liked others? If so which ones?