Saturday, July 20, 2019

Ocean’s TechCon 2019 in 6 days

In six days I will be giving my presentation SQL at Ocean UG’s TechCon 2019. I have my presentation finished, and I am ready to present it on Friday (July 26) at 1:00 PM in room 201. If you are attending this conference I hope you will consider attending my presentation.

If you are in So Cal there is still time to register to attend the best User Group conference in the West.

I hope to see many of you there!

Wednesday, July 17, 2019

Easiest way to retrieve the program's name in a CL program

this is the easiest way to get the cl program name

It has always been a bit of a pain to get the name of the CL current program using the Receive Message command, RCVMSG. Therefore, I was excited when I received an example program showing a much simpler way of getting the same information by using a Machine Interface program.

The program I am going to show needs to be created as a CLLE member, and compiled using the Create Bound CL program , CRTBNDCL. As the call procedure command, CALLPRC, is not permitted in CLP source members.

My CLLE program will be calling the MI program _MATPGMNM, it is important to notice that the program name starts with an underscore ( _ ). I cannot use a CALL command, I must use the CALLPRC command instead.

My first example is the simplest program I could write to call this MI program, and have the current program name and library returned.

Wednesday, July 10, 2019

Which users are authorized to these files and tables?

systabaut, system table authority, sql view

This post started as one idea, and the more I played it morphed into something else.

The starting point was the SYSTABAUTH view. This view displays the authority of all DDS PF and LF files, SQL DDL tables, views and indexes, in all libraries, except QTEMP. It contains the same information that is shown when I use the Display Object Authority command, DSPOBJAUT. As it is a view I can retrieve the results from SQL statements and manipulate it any way I desire.

In these examples I will be using a few of the view's columns:

  1. GRANTEE:  the user profile that has been authorized to the object
  2. SYSTEM_TABLE_SCHEMA:  the system name of the library that contains the object
  3. SYSTEM_TABLE_NAME:  the system name of the object
  4. PRIVILEGE_TYPE:  the various authorities granted to the user profile to this object

There are seven different types of privilege that can be given to an object:

Wednesday, July 3, 2019

Checking for Firmware updates

check for firmware updates

You might want to share this post with the System Admin of the IBM i you use, as this will probably more of more interest to him or her than to a developer type of person.

Wikipedia defines firmware as:

Firmware is a specific class of computer software that provides the low-level control for the device's specific hardware.

Wikipedia: Firmware

Despite the term "Firmware Currency" being widely used by many different hardware platforms I have been unable to find a definition. From what I can ascertain "firmware currency" appears to be the equivalent of firmware release.

With the latest Technology Refresh for IBM i, TR6, a new view has been added to allow us to compared the firmware fix level installed on the IBM i partition to the recommended fix level from the remote Fix Level Recommendation Tool, FLRT. To be able to use this view the IBM i partition being used must be able to access the internet and be able to reach the FLRT website:
https://www14.software.ibm.com/support/customercare/flrt/liteTable?prodKey=fw&format=json

Wednesday, June 26, 2019

It is now easier searching message files

new sql view for searching message files

I have always found it a bother to search for IBM i message ids. Which message file contains the particular message id I want? What messages could I use for a date validation error? Etc. I always had wished for an easier way to perform searches like this.

Fortunately the latest Technology Refresh, IBM i 7.3 TR6, has a new Db2 for i view to make my searches for messages so much easier. The view MESSAGE_FILE_INFO returns a row for each message from all the message files in the IBM i partition I am using. I am not going to describe what the columns are here, as I think their names explain what they contain. For a full list of all the columns contained in this view click on the link to IBM's documentation at the bottom of this post.

Everything you can see with the Display Message Description command, DSPMSGD, is in this view. Using this view I do not have to give the message file's name when searching for a particular message id. For example if I am looking for the description for the message "RSC0082" I would have used the DSPMSGD command:

Monday, June 24, 2019

Friday, June 21, 2019

New release, IBM i 7.4, day

ibm i release 7.4 is available today

The new release of IBM i is out today!

If your Power server has a POWER8 or POWER9 chip, then you are one of the fortunate ones who can install the new release.

You can read the announcement document for IBM i 7.4 here.

Most of the new programming features I have been writing about in IBM i 7.3 TR6 are in 7.4 too.

The biggest thing to happen to Db2 for i for years, Db2 Mirror for i, is only available for this new release. If you are one of those moving to 7.4 and you start using Db2 Mirror for i I am interest to hear your thoughts about it.

For more information on what is available in the new release, and 7.3 TR6, visit these links:

Wednesday, June 19, 2019

New table function to break apart values in columns and fields

suing string able function to break long strings into smaller parts

A couple of years, and a few Technical Refreshes ago, the LISTAGG SQL built in function was added to Db2 for i. Until the latest TR there was not some easy way to do the opposite, take a string from one column and break it into separate results. I am not saying it was not possible to do this before, it has just got a whole lot easier with the introduction of the SPLIT table function in the lastest IBM i 7.3 TR6.

The syntax of this new table function is as follows:

SPLIT(input list or column,separator character)

SPLIT returns two columns in its results:

  1. ELEMENT:  the values extracted from the "input list". This is a CLOB variable that is 2 gigabytes in size.
  2. ORDINAL_POSITION:  Not ORDINAL as is given in the IBM documentation for SPLIT. This is the relative position of the value returned in ELEMENT from the original "input list".

Here is an example using an "input list", or a string to you and I, of names.

Monday, June 17, 2019

Online presentation is this week

This Thursday, June 20, at 11 AM PDT I will be giving an online presentation with New Generation Software (NGS). My part of the presentation will be about using SQL views to make it easier to get data from your IBM i database.

If you would like to watch the presentation live you will need to register on NGS's website here.

If you cannot attend the presentation at the given time, NGS will be recording the presentation and uploading it to their web site. You will still need to register to watch the recording.

I am looking forward to doing this, and I hope you will be able to attend the live event.

If you want to know what time 11 AM PDT is in your time zone click here.

Thursday, June 13, 2019

New subfields added to Program Status Data Structure

two new subfields added to the new program status data structure

The latest Technology Refresh for IBM i 7.3, TR6, has seen two new subfields added to RPG's Program Data Structure. This data strucutre provides me with a wealth information about the status of the program while it is running, and when it errors.

I always add the Program Status Data Structure, PSDS, to all of my RPG programs. I can dump the program and learn a lot of what happened from the information contains within the PSDS.

Rather than manually entering the same data structure into every program, I have my PSDS in a member I just copy, or include, it into the source members of others.