Using table function rather than view for object privilege information

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:

Removing deleted records faster than RGZPFM

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?

Viewing IFS object's authority using SQL

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:

SELECT with EXCEPT clause to find differences between records in two files

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:

• Create a file
• Add data to it
• "Clone" the file to make an exact copying, including the data
• Change the data in the "clone"
• Compare the two files

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:

Viewing HTTP server information using SQL

I want to give credit to IBM's Db2 for i team for continually adding SQL views, table functions, etc. to information that was not easy to get using APIs or other tools. Another example of their excellent work, introduced with the latest Technology Refreshes, is the view HTTP_SERVER_INFO. Previously this information was only available using the Web Administration for i GUI.

This view, which is located in the library QSYS2, displays information about the Apache server in the HTTP server for IBM i, and will only display data about the enabled and active functions.

As usual with my examples of Db2 for i views and table functions I am not going to list all of the available columns, just the ones I found interesting:

Removing apostrophe in a field using SQL

There are occasions when interfacing data to non-IBM i database where an apostrophe ( ' ) in a field can cause a conversion error. In this post I am going to give some simple examples of how apostrophes can be converted to something else using SQL and RPG.

Let me start with the DDS file I will be using in these examples. The file is called TESTFILE and contains one field:

A R TESTFILER A COMPNAME 35A

The field contains the following three records:

It's been IBM i for longer than AS/400

In a post on LinkedIn yesterday Steve Will, chief architect of the IBM i, shared an interesting milestone in the life of the this operating system.

This operating system had been known as IBM i for a long as it was known as AS/400.

Subtracting days from a "date" when it is a number in a file

The germ for this post came from a question:

How can I subtract 35 days from a date in my files using SQL? The date is a packed number

Most of us who deal with older databases find that the "dates" in the files are not true dates fields, they are numbers masquerading as dates. I have, in a previous post, shown how to change a numeric representation of a date to a date with SQL, this is taking it a step further by converting the date back to a number and updating the file with the new value.

In these examples I will be using the numeric representation of the date in two formats:

How to view pending database commits using SQL

Another addition to Db2 for i in the latest Technology Refresh, IBM i TR2 and 7.3 TR8, was a view called DB_TRANSACTION_INFO. This view lists what the IBM documentation describes as: "returns one row for each commitment definition".

What does that mean? Having played with this View for a while I have found that it just lists all uncommitted database transactions to a file or table that is using commitment control. Database operations to files and tables that do not use commitment control do not appear in this View.

There are far more columns in this View than I am going to use in my examples, so I am going to say to you if you want to know what all of the columns are click on the link to IBM's documentation at the bottom of this post.

Viewing all of the subsystems autostart jobs in one place

If I needed to see which autostart jobs were in my IBM i partitions I would either have to use the Display Subsystem Description command, DSPSBSD, to view the autostart jobs for each subsystem or use an API. Fortunately the latest Technology Refreshes include a Db2 for i View that gives me the information I want.

Before I give examples of using the new View I want to show how to get to the same information using the DSPSBSD command so that you can compare the data from the two.

If I want to see what autostart jobs there are in the QCTL subsystem I would use the following command:

DSPSBSD SBSD(QCTL)

When the Enter key is pressed the following menu is displayed: