Scott Forstie, Db2 for i Business Architect, has updated his Db2 for i poster to include everything from the latest Technology Refreshes, and made it available for download at ibm.biz/Db2foriPoster.
Thank you Scott for doing this.
In the future if that link stops working, as ones for previous posters have, you can download it from here.
I have had the latest version of Access Client Solutions, ACS, 1.1.8.5 for just over a week. I use "Run SQL Scripts" so much I leave it open all day. It is my favorite part of ACS, as I often start with a simple SQL statement, and then copy that to make it ever more complex until I have the final form of the statement I want to use. Something that is hard to do using the STRSQL interface.
There are several ways to start "Run SQL Scripts". The most common way is from the 5250 emulation display. I will find the option to start it in the "Actions" menu.
After five days my Request For Enhancement, RFE, to give the latest form of RPG its own name was rejected.
The germ for this post came from a question from Bob. It is not the question that I wanted to share or the answer I came up with, but the solution he came up with. Bob was presented with an old program with a SQL Cursor definition statement that joined several files together. Within the Cursor definition the original programmer had listed all of the fields in the files, and in the Fetch statement he/she had listed all the fields from the files again.
The question he asked me was this possible to convert from a Cursor Fetching one row at a time to a multiple row Fetch.
In the following examples I am not going to use the files Bob used, but these test files:
I was talking to some friends about totally free RPG and I soon got tired having to save "totally free RPG" all the time. Then it struck me this language needs a new name!
Do not worry I am proposing we drop the name "RPG", but the time is right to give it something to show that this is a new version of the programming language. When the AS/400 was launched, 32 years ago, RPGIII from the System/38 was renamed RPG/400. 25 years ago ILE/RPG was brought forth giving us a new paradigm to programming.
Five years ago "totally free RPG" was introduced. It is not ILE/RPG. There are no columns and I can start typing in the first column of the source member, and can continue the way to the last column too. It was something new and different from its antecedents. This is a new version of the programming language and, in my opinion, it deserves a new name.
Update December 7, 2020: New version is currently available for download here
A new version of IBM's Access Client Solutions, ACS, has been released. ACS includes:
And it is free to download from IBM's website.
If you are using a free IBM i service, like RZKH's PUB400.com, then this is what you should use as it gives you a lot more than the other 5250 emulators.
One of the new arrivals with the latest rounds of Technology Refreshes was a table function to retrieve the authority for a particular object. "Wait!" I hear you regular readers say, "Wasn't there already a SQL View added to show that information?" And you are correct in IBM i 7.3 TR2 the OBJECT_PRIVILEGES View was released.
With a Table function I pass it a set of parameters and only the results for those values are returned. If I use a View then the entire View has to be searched for the rows matching the selection criteria. By using a Table function, rather than a View, I can get the information I want quicker. I will demonstrate this in the examples below.
The OBJECT_PRIVILEGES Table function and View have the same columns. The only annoying difference is the name of the authorized user profile in the View is AUTHORIZATION_NAME, while in the Table function it is AUTHORIZATION_USER.
In these example I am only interested in determining if user profile is authorized to use a file, TESTFILE. The profiles authority is held in the column OBJECT_AUTHORITY. Therefore, if I want to see who is authorized to use the file TESTFILE in the library MYLIB I would need to pass the Table function the following:
The subject of this post is not new, but I thought I would share this as this is the quickest way I know to get rid of millions of deleted records taking up space in your files. The last time I used this method was with a file that contained 1 million "active" and 11 million deleted records. The application owner of this file had a fixed amount of time to remove the deleted records in their weekly maintenance "window". Having performed tests using RGZPFM she found that it took longer than the allowed, and came to me for ideas.
RGZPFM FILE(BIGFILE) |
The part of this process that many people forget is all the access paths are reorganized too. In this case there were a plethora of logical files built over this file, I forget exactly how many but too many for my liking.
What was my suggested alternative?
Included with the latest Technology Refreshes is a new table function which allows me to view objects' privileges of the folders and files in the IFS. Object privileges/authorities are something that I am asked for during audits, therefore, I was really interested to learn what information I can get from it.
The only gotcha is as a table function IFS_OBJECT_PRIVILEGES returns the results for one set of objects, rather give me the ability to get information for all objects as I would using a view.
As I have done in many of my previous examples I am not going to show you all the columns returned by this table function, just the ones I find interesting. There is a link at the bottom of this post to IBM's documentation for this table function which includes a full list and description for all of the columns.
The columns I am going to work with are:
I saw this mentioned in a Facebook discussion where someone said he was using what I will describe below to determine the difference between the records in two files. I had not heard of this method so I wanted to try it out for myself and determine whether it is something I could use in the future. Two SQL Select statements joined with an EXCEPT clause. But before I show examples of that I need data.
I am going to:
In these examples I am just going to use a file with one field. The same principles work with one field as it would with a hundred fields. I decided to K.I.S.S. and just have one field in my file. The layout for the file is: