Wednesday, August 16, 2017

Sometimes a List API is better than the SQL View

use list api and user space to get data about spool files

Sometimes it is just "better" to use a list API than it is to use the SQL View that "replaced" it. This week has given me an example of this. I was asked to create a program that would allow users to email themselves reports. I knew I would have to present the users with a list of spool files from which they could select the one they would want to email.

"No problem", I thought, "I will just use the Output Queue Entries SQL View."

I created a View over the OUTPUT_QUEUE_ENTIRES View, and then created a program using a multiple row fetch to get more than one row from the View. In the "test" IBM i partition the program ran a bit slow the first time I ran it, then ran faster each time after as the DB2 query engine created a temporary access path for me. Being a "test" partition there are not many output queues or spool files when compared to the "live" partition. I moved the objects I created to "live" and called the program there. It took more than 20 minutes to load the subfile with the list of user's spool files. With several hundred users and a very large number of spool files (many thousands, does anyone ever delete spool files?) in the "live" partition it was searching through all of them to find the few spool files belonging to me. The time taken made this method unacceptable. Back to the "drawing board".

Tuesday, August 15, 2017

We are not impostors

value of self taught programmers

Last week I found a wonderful article by Roger Pence in IBM Systems magazine.

It started with the title "Your are not an impostor", and continued with the subheading "Self-taught RPG programmers bring valuable skills to enterprise programming teams". As one of those self-taught programmers it caught my eye, and he made some good points about what would make me feel like an impostor, and what I can do to overcome it. It confirmed my opinion that there is a place in every organization for the self-taught and motivated programmers, alongside the formally educated graduates.

You can read the article on IBM Systems online magazine here.

If, like me, that site does not always load, I have placed a copy here (PDF file).

Alas, when I tried to go to the URL given for "The Impostor's Handbook: A primer for self-taught programmers",, it went to a 404 page not found error page. If you have the correct URL for this page I would be grateful if you would post it in the comments section below.

Wednesday, August 9, 2017

Resetting the value of a variable

using reset operation code to set value back to initialization value

One of my colleagues was reading the post about initializing variables when defining them and asked a question that I had not considered when writing that post.

"What if I change the value in the variable," he asked, "can I change it back to its original value?"

Fortunately there is in RPG an operation code that will do this. The RESET operation resets the value of any variable or record format back to the value it was at the initialization phase of the program. It is not new operation, I have used it for as long as I have programmed on the AS400 - IBM i.

Thursday, August 3, 2017

Types of saves

types of saves

I have Steve Pitcher to thank for sharing this image so that I could post it here. It is from IBM's KnowledgeCenter showing the different types of saves that can be performed using the various save commands, on the right side, and the options on the SAVE menu, on the left.

list of the types of saves and what they save

Wednesday, August 2, 2017

Checking Software License expiration dates using SQL

checking softwatre license expiration dates using sql

Included in the latest Technical Refreshes for IBM i 7.3 and 7.2 is a SQL View and Procedure to help us monitor the licenses of the software on our IBM i.

IBM uses Software License Keys with certain IBM i products to ensure that I have paid the appropriate license fees to use these products on an IBM i. I know of several third party IBM i software vendors who also use this method to stop the unauthorized copying of their software to unlicensed servers. It is all standard stuff to prevent the distribution of unauthorized software.

Thursday, July 27, 2017

IBM i is more than an AS400

why ibm i  is not as400

I frequently get messages asking why does IBM call it IBM i when it is just an AS/400, or even System/38, by another name. Talking to other authors and IBM-ers I find that I am not alone to receive communications like this.

Steve Will, chief architect for IBM i, has felt compelled to dispel this fallacy. Earlier this month he made an online presentation, sponsored by HelpSystems, explaining how IBM i differs and is a lot more than AS/400. The video can be watched on the sponsor's website here.

The video lasts just over an hour, and, in my opinion, is worth watching to learn the evolution of the operating system to become the thoroughly modern IBM i.

Wednesday, July 26, 2017

Position cursor into field on screens

position cursor on a display file record format

The germ for this post's subject came from a two part question I was asked:

  1. How can I know which field the cursor is in when Enter is pressed?
  2. How can I position a cursor on some field without using indicators? No indicators are available in that very old screen.

Fortunately there are ways to do this by adding a few keywords and fields to the display file's DDS. In these examples I am going to use one display file with three screens, each showing a way to do what has been asked, and the RPG code needed too (and I am going to cheat on the second part).

Monday, July 24, 2017

DB2 name change

db2 family renamed

Last week at OCEAN User Group's Technical Conference I attended a couple of sessions by Scott Forstie, IBM's Business Architect for DB2 for i. I noticed in his presentation he had used "Db2" rather than "DB2".

When I asked him about this he explained that last week (Monday July 17, 2017) the DB2 family had been officially renamed "Db2". The Db2 family is:

  • Db2 for i
  • Db2 for z/OS (mainframe)
  • Db2 for Linux, Unix and Windows

I found that that Db2 page on IBM's website has already been changed, see here.

Now I need to remember to use the new name when writing about it in this blog.

Wednesday, July 19, 2017

Finding the number of times a character is in a field using SQL

using sql to count the number of times a character is in a field

I needed to determine, first, if a certain character was present in a file's field, and then how many times it occurs. Using RPG's file input I would not be able to do this without reading and checking every record's field. Could this be done easier in SQL? Yes.

I am going to give examples of various ways of being able to tell if a certain character is in a field in a file, using increasing complex methods I found. At first I will just give the SQL Select statement for each, and at the end the SQLRPGLE code I created to do this in a program.

Wednesday, July 12, 2017

Updating one row at a time using SQL

update current row/record using sql

I have been asked to write some more about using SQL embedded in RPG programs. One question I have been asked by several people is how to update a row from a file that had been previously Fetched, just like READ and UPDATE using RPG's native database access. I thought it would be useful to show examples using both RPG native database access and SQL.

First I need a file, as this is for example purposes it very simple, just two fields and one key field: