September's presentations

I am involved with two user group events this month

On Wednesday 3 September I will be in Oslo, Norway, presenting at COMMON Norge's "Professional day IBM Power and AI".

I will be making three presentations in the afternoon. If you are in Norway this will be the event of the year, so don't miss it.

Details of the meeting, and the form to register, is here.

Jeg håper å se deg der!

On my return home, to Austin Texas, I will be hosting the September meeting of the Central Texas IBM i User Group, CTXiUG, on Tuesday September 9 at 6:30 PM CT.

Our guest speaker is my System i Developer colleague Mike Pavlak. He will be sharing his experiences using stored procedures for hybrid devlopment.

The meeting is online and free, and you can learn all about it and register here.

CVTDAT can use 1970 rule

This almost slipped past me, the information in the IBM i 7.6 documentation that says that the Convert Date command, CVTDAT can use the '1970 rule'.

Whether CVTDAT uses the '1940 rule' or the '1970 rule' depends upon the presence of the QIBM_QBASEYEAR Environment variable. If its value is '1970' that rule is used, if it is not present then the '1940 rule' is used.

How can I tell if I have that Environment variable? There are two ways. I can use the Work with Environment Variables command, WRKENVVAR:

01 WRKENVVAR

Additions to the SYSDISKSTAT

SYSDISKSTAT returns the information about disk and solid-state drives, SSD. It comes in two forms:

• SYSDISKSTAT View
• SYSDISKSTAT Table function

I am not going to say much more about using these two as I gave a lot of detail when they were first introduced.

In the latest release IBM i 7.6 and 7.5 TR6 two new columns have been added to both of the SYSDISKSTAT:

Identifying and changing object owners

Before I delete an user profile I always want to identify which objects it owns, and then transfer them to another profile or profiles. I can see which objects they own using the Work with Object by Owner command, WRKOBJOWWN. If there are a lot of objects in the results I do not find this user interface helpful.

Db2 for i offers us an alternative, the OBJECT_OWNERSHIP table function. I have written about the OBJECT_OWNERSHIP view before, it would appear I overlooked the table function.

The advantage of using a table function is that only the results for the parameters passed are found, like calling an API. With a view the rows are selected according to the selection criteria, which is like searching a file or table. In various situations, one may have an have advantages over the other. In this scenario, I only want the information for one user profile, the table function is more efficient.

The syntax for the OBJECT_OWNERSHIP is simple, as it only needs one parameter passed to it, the user profile:

Get more RPGPGM.COM in your Google Search

I get quiet few communications from readers of this website that they have bookmarked RPGPGM.COM in their browser, or they have made it their home page. I am very flattered that people appreciate my work enough to do that.

If you are someone who finds the content of this website useful, and want to have a way to list results from it more prominently in your Google search experience, Google has added a new feature to help: Google Preferred Sources.

At the time of publication this feature is available in the USA and India, for Google searches in English. I am keeping my fingers crossed that it will eventually be rolled out globally.

What does it do? This is what Google says in their blog post announcing the release of "Preferred sources":

When you select your preferred sources, you'll start to see more of their articles prominently displayed within Top Stories, when those sources have published fresh and relevant content for your search.

This is available for Desktop, Android, and iPhone and iPad.

Rather than follow the steps described in the articles to activate Preferred sources, you can just click on the button below.

You must be signed into your Google Account before clicking.

More information can be found:

• Google blog: How to select your preferred sources in Top Stories in Search
• Google Search Help: Top stories in search

Prevent my user profile being used when others submit job

It is possible to submit jobs to batch that will run for another user profile by using the User parameter in the Submit Job command, SBMJOB. For example:

SBMJOB CMD(DLTF FILE(MYLIB/INVHIST)) JOB(TEST) USER(SIMON1)

The above shows that the current job is trying to submit a job to run with the SIMON1 user profile. I would consider to be a security risk. If SIMON1 has more authority than the current user, let's say that is NOTSIMON, then NOTSIMON can submit jobs that perform tasks they are not authorized to do, or do something bad that they will not be blamed for.

Fortunately in most cases NOTSIMON would receive the message:

SBMJOB CMD(DLTF FILE(MYLIB/INVHIST)) JOB(TEST) USER(SIMON1) Not authorized to user profile SIMON1.

I have encountered various companies where all the SBMJOB commands in their programs would use a "special" user profile for batch jobs, that had more authority than the average user. In this scenario when NOTSIMON ran a program that SBMJOB, and I looked at the calling job's job log I would see something like:

Useful IBM Support document for data type mapping

Someone sent me this link to an IBM Support document that maps DDS data types to SQL and ODBC data types. And I decided to share it with you as I find this information both interesting and useful.

DDS/SQL Data Type Mappings - IBM Support

New version of ACS, 1.1.9.9, is now available

Yesterday I discovered that a new version of ACS became available for download.

Giving the ability to see all of the objects on the system

I understand why IBM i developers should not have all object authority, *ALLOBJ, but at times I am frustrated by my inability to find objects in my partition. I do not want to do anything to them, just know that they exist. I have to find someone with a security office equivalent user profile and ask them to do a search for me.

I have found a way that this frustration can be removed. It will work on all partitions that are IBM i 7.5 or higher. I think what I am going to describe is included in the initial release. As none of the partition I have access to are running just base 7.5 I cannot check that this did not come in a Technology Refresh, TR.

Function Usages are way to be granted access to perform certain higher authorization functions, without being given that higher authorization. I was going through IBM's documentation about them when I came across:

Finding source members with the same name

How would I find if a source member with the same name is found in more than one source file? If there is, which one was modified most recently? I am sure those are questions many of us have asked ourselves. How could we can make this easy for ourselves to get this information? Fortunately, Db2 for i has everything I need to do it.

I start with the SYSMEMBERSTAT View, it was introduced a couple of Technology Refreshes ago, IBM i 7.5 TR4 and 7.4 TR10, and it is used in place of SYSPARTITIONSTAT when I need information about members.

What are the columns I am interested in:

SDA is not available in IBM i 7.6

The announcement of IBM i 7.6 included notice that the following application development tool set commands were no longer available:

• STRSDA:  Start Screen Design Aid
• STRRLU:  Start Report Layout Utility
• STRAPF:  Start Advanced printer Function
• MRGFORMD:  Merge Form Description
• STRCGU:  Start Character Generator Utility
• CPYIGCSRT:  Copy DBCS Master Sort Table
• MRGFORMD:  Merge Form Description

The web page with this information is here.

In the past week there were rumors circulating in social media that this is not true, and it is still possible to use the STRSDA command in 7.6 .

Retrieve the SQL statements from multiple QM queries

The germ for this post came from a message I received:

Is there any way to retrieve sources for all QMQRY objects available inside a library in one go? My objective is, there are 100's of QMQRY objects(SQL type) inside a library but they don't have predefined source members. I need search for particular string inside all those SQL queries used inside the QMQRY objects.

I decided to work out a way I could do this.

I did not create "100's" of Query Management, QM, queries as what will work for two will work for many more too.

It is possible to retrieve the SQL statement from a QM query object by use of the Retrieve Query Management Query command, RTVQMQRYM. That command copies the retrieved statement into a source member.