Mass insert into IFS file using IFS_WRITE

There are times when someone asks me a question I think is interesting enough to become a post here. This was the question that was the germ for this post:

How can I read a physical file and for each records, use the IFS_WRITE Function to write the [ IFS ] file?

The IFS_WRITE are really three SQL procedures that writes data to a file in the IFS:

1. IFS_WRITE:  Write plain text to IFS file
2. IFS_WRITE_UTF8:  Write UTF8 text
3. IFS_WRITE_BINARY:  Write binary text

In the following examples I am going to use IFS_WRITE_UTF8 as I want the contents of the IFS file to be UTF8 compatible.

CTXiUG July meeting

The next meeting of the Central Texas IBM i User Group, CTXiUG, will be on Tuesday July 11 6:30 PM (US CT). The meeting is online and free to attend.

We welcome Vern Hamberg, as our guest presenter. Having known Vern for several years I would describe him as one who knows a lot of interesting IBM i techniques, features, and functions that may be a little off the path more travelled.

To learn more, and register, go to the CTXiUG's website at https://ctxiug.blogspot.com

Presentation in June

I have one event I will be attending, and presenting, this month:

• Monday June 5: Michigan IBM Power Systems Technical Education Conference, Livonia, MI. I will be making three presentations at this conference.

I am looking forward to this as this will be my first time in Michigan.

If you are at the conference please come and introdice yourselves to me, I always like to hear what you think of this website.

If you are a member of a LUG and you would like me to talk to your group, use the contact form to reach me. We can then arrange a date and subject.

If you are a member of a LUG that is not listed on my LUG page, please contact me and send me your group's details.

Remove the need for procedure prototypes

This is another example of something in RPG that almost slipped by me. I now have a way not to have to define a procedure prototype in the procedures' member. All I need is the procedure, and the RPG compiler does its "magic" to do what ever it does to make this possible. This is made possible by the addition of a new control option and parameter in the procedure declaration.

The addition to the control option is a new option REQPREXP, which allows one of three values:

• *REQUIRE:  All procedures are required to have a prototype (DCL-PR)
• *WARN:  If a prototype is not found for a procedure a warning error, severity 10, is received when compiled
• *NO:  Procedure prototype is not required for the main and exported procedures

Another study shows the benefits of IBM i

Forrester Consulting was commissioned by IBM to perform an assessment of the benefits of the IBM i operating system. The result was one of Forrester's trademark "Total Economic Impact" assessments, determining the cost savings and business benefits of using IBM i.

The report can be read by clicking on this link here.

I have already downloaded and emailed this to my Vice President and CIO to reinforce the benefits of continuing to enhance our IBM i strategy.

If you work amongst naysayers who keep saying "The AS400 is dead", this would be a useful document to have and share with your management to show that IBM i needs to remain part of your employer's business strategy.

Using TO_CHAR to format timestamps

I use the SQL scalar function TO_CHAR all of the time to format numbers, inserting thousand separator characters. I can also use it for formatting timestamps.

In the following examples I am going to show how easy this, and it appears to duplicate a lot of the functionality of the EXTRACT scalar function.

In these examples I am going to show what I can do with a timestamp column, TIMESTAMP1, in a SQL DDL table, TESTTABLE.

Let me show you some examples of what I can do.

Why do my SQL results show pointer?

At least one a month I get a question that is something like this:

My SQL results do not look like yours, mine have the word pointer in them. Why?

This gives you away as someone who is still using the old Start SQL command, STRSQL, interface in your 5250 emulator session, rather than Access Client Solutions' Run SQL Scripts. I am pretty sure that the vast majority of us are using ACS for our 5250 "green screen" sessions, so why are you still using STRSQL when Run SQL Scripts is there too?

Latest Db2 and RPG TR PTFs out today

In the IBM i world does "Christmas" come twice a year when the PTFs for the Spring and Fall Technology Refreshes are delivered? I think so!

Today the PTFs for Db2 (SQL) and RPG are released for the latest Technology Refreshes, IBM i 7.5 TR2 and 7.4 TR8.

The base operating system PTFs for these TRs became available on the May 8, 2023, but they did not include any of the changes and enhancements to Db2/Database or RPG.

Capturing errors within ON-EXIT

Having been busy recently performing presentations to various user groups and conferences I noticed an oversight in my work. In my presentations I had given an example of using the ON-EXIT group showing how I can use an indicator to indicate if an error had occurred in the procedure or subprocedure. In my post, on this website, about ON-EXIT I only mentioned it in passing. In this post I want to overcome oversight to show how I can do this.

The ON-EXIT group can be added to the end of every procedure or subprocedure in a RPG program. Regardless of whether the procedure or subprocedures errors or not, the code within the ON-EXIT group is always executed.

Let me start with my first example program, this one does not have any procedures or subprocedures:

Spring 2023 version of the IBM i and Power10 performance guides

There are two updated publications from IBM to help us get the best performance from our favorite operating system, IBM i, and from the brand new IBM Power10 server it runs upon.

The first is "IBM Power 10 performance optimization for IBM i". It is 22 pages long and covers all kinds ways you can get the most from your IBM Power 10 server. I have read most of it, and when my employers moves to a Power 10 server, later this year, I hope to implement most of its suggestions. You can download your own copy here.

The other is the "IBM i on Power – Performance FAQ". This one is 112 pages and covers in detail what you can do to get the most from the IBM i operating system. This includes sections for things you can do with your RPG and COBOL to optimize it. This can be downloaded from here.

Lots of information to read, but worth it to get the best from our operating system and servers.

New columns added to OUTPUT_QUEUE_ENTRIES

Two new columns were added to the OUTPUT_QUEUE_ENTRIES SQL View and SQL Table Function as part of IBM i 7.5 Technology Refresh 1 and IBM i 7.4 TR7, and while I wonder how often I will use them I do find the information they return interesting.

These columns are:

1. TOTAL_RECORDS:  Total number of records in the spool file. Will be null unless the spool file is *AFPDS, *AFPDSLINE or *LINE, or the file is open
2. MAXIMUM_RECORDS:  The maximum number of records that the spool file can contain, when the file was first opened

Using SQL to retrieve information about hardware resources

Amongst the many new additions to the latest Technology Refreshes, IBM i 7.5 TR1 and 7.4 TR7, comes a new View and Table Function that allows me to retrieve information about a partition's hardware resources, including its status.

Both have the same name, HARDWARE_RESOURCE_INFO, and are in the library QSYS2. The View returns information for all hardware resources, the Table Function returns can be used to only return the information about one type of hardware resource or another. Both return the same columns in their results.

Before I start showing you examples of the results from these two I recommend you run the following SQL statement. This will allow you to see all the available columns, and help you decide what is useful to you.

COMMON Finland

Tervetuloa, COMMON Finland have a new website, at commonfinland.fi

They become the 18th national association affiliated with COMMON Europe to launch their website.

I have added their site to my IBM i User Groups page. There is a link at the top of every page and post to this page.

If you are a member of, or know of, any User Groups not listed on my IBM i User Groups page please let me know of it, using the Contact Form that you can find on every post and page.

New columns added to HISTORY_LOG_INFO

As part of the latest round of Technology Refreshes, IBM i 7.5 TR1 and 7.4 TR7, three new columns were added to one of my favorites Db2 for i Table Functions, HISTORY_LOG_INFO. I often use this Table Function to search the IBM i partitions' history.

The new columns are all parts of the existing job name column, FROM_JOB:

• FROM_JOB_NAME:  Job name
• FROM_JOB_USER:  The user profile of the job
• FROM_JOB_NUMBER:  Job number

These columns might not sound like a big addition to HISTORY_LOG_INFO, but the first two, name and user, make it easier to find results I am looking for.

Personalised SQL error logging, SELF

Introduced as part of IBM i 7.5 Technology Refresh 1 and IBM i 7.4 TR7, is a mechanism to capture details of SQL errors into a separate log table. I can decide which errors I want to capture in the log, by use of the SQL code. The SQL codes to capture are set at the SQL session level, rather than at the IBM i job level.

This is called the SQL Error Logging Facility, or SELF for short. It consists of several parts, the parts I am going to explain in detail are:

• SQL_ERROR_LOG:  A View that is used to display the logged errors
• SELFCODES:  A Global Variable that needs to contain the SQL codes I wish to log
• VALIDATE_SELF:  A scalar function that validates SQL codes

ACS 1.1.9.2 now available

As promised in the latest round of Technology Refreshes a new release of Access Client Solutions, ACS 1.1.9.2, is now available.

I install the update in two ways:

From ACS

Notice:  As of April 27 this method is still not working. Use the second method described below.

• Open your ACS window
• Select "Help" on the menu at the top of the window
• Select "Check for updates" in the drop down menu

You will see the following window:

More about IBM i 7.5 TR2 and 7.4 TR8

This is what I have found since yesterday about the new Technology Refreshes, IBM i 7.5 TR2 and IBM i 7.4 TR8.

• Announcing IBM i 7.5 TR2 and 7.4 TR8 by Steve Will, Chief Architect and CTO for IBM i
• Tweaks To The Power Software Stack, And Red Hat Gets Easier by Timothy Prickett Morgan, IT Jungle

Then new release of ACS is now available. You will find instructions about how to download it here.

New TRs, IBM i 7.5 TR2 and 7.4 TR8, announced

The Spring 2023 Technology Refreshes, IBM i 7.5 TR2 and IBM i 7.4 TR8, have been announced today.

The availability dates for the PTFs are:

• Base TR PTFs May 5, 2023
• Db2 (SQL) PTFs May 19, 2023
• RPG PTFs included in the Db2 fix pack

All the information about these TRs can be found:

• IBM i 7.5 TR2:
  • IBM Support page:  These get better every TR
  • RPG cafe with RPG additions and enhancements
  • IBM announcement
• IBM i 7.4 TR8:
  • IBM Support page
  • RPG cafe with RPG additions and enhancements
  • IBM announcement

Additions and changes to the RPG programming language are:

Easy way in SQL to insert records from one file that are not in the other

In this scenario there are two files with identical field names, and they have the same data types too. I was asked if there is an easy way, using SQL, to insert all records from one file into a second file, omitting records that match ones that are in the second file.

I wanted to come up a solution where I did not have to give any field/column names in the statement. For all I knew the files in questions had many, many field names.

I created a file, I called FILE1, with four fields. Then I used the Create Duplicate Object command, CRTDUPOBJ, to create a duplicate, which I called FILE2.

FILE1 contained four records, which I can show using the following SQL statement:

Manually collecting and refreshing statistics for a DDL table

This is how to manually update the statistics used by the SYSCOLUMNSTAT View for a SQL DDL table, TESTABLE.

If the video is too small for you to see what is going on, click on the YouTube link in the video, or click here.

 

IBM documentation describing how to do this here.

 

Using ACS version 1.1.9.1

More useful information added to SYSCOLUMNSTAT View about variable length columns

As part of the recent IBM i Technology refreshes, 7.5 TR1 and 7.4 TR7, three new columns were added to the SYSCOLUMNSTAT View. These are only of interest for variable length columns. While two of the columns I found easy to understand the third took awhile for me to work out what its contents mean.

These new columns are:

• MAXIMUM_COLUMN_LENGTH:  For variable length columns this is the maximum length of the column. For fixed width columns this is the column length.

Converting numbers and characters into timestamps

I am sure we have all worked with ERP Applications that still store their dates and times in numeric or character fields. I was asked what would need to be done to convert these into a timestamp in a SQL Select statement. Personally, I do like using timestamps rather than individual date and time columns, or fields. Therefore, I consider this a good question.

I have a SQL DDL Table with a mixture of "date" and "time" columns within it:

• DATE_NBR8:  A numeric representation of a date, eight long, and in ISO format (YYYYMMDD).
• DATE_NBR7:  A numeric representation of a date, seven long, in CYMD format (CYYMMDD).
• DATE_CHAR:  A character representation of a date, eight long, in ISO format with no separator characters.
• DATE_DATE:  What I would call a "true date", date data type.
• TIME_NBR:  A numeric representation of a time, six long.
• TIME_CHAR:  A character representation of a time, six long, with no separator characters.
• TIME_TIME:  A "true time", time data type.

Find the most recent rows for a repeating sets of data with SQL

This post is based upon something I needed to provide to my superiors at my work. We have a history file that contains many thousands of repeating sets records (yes, I know it is time to purge the old data), I was asked to "create a SQL statement" that would return the most recent records for each key. It will make more sense when I show the data I am going to be using here.

In these examples the file is named TESTFILE, and contains three columns of data. I can use the following SQL statement to list the contents of this file:

01 SELECT * FROM TESTFILE

The results are:

Getting the hours, minutes, and seconds from a difference in timestamps with SQL

I have timestamp columns in a Db2 Table that I need to give the difference in hours, minutes, and seconds. Preferably I would put these into separate columns that I can then use in various ways in subsequent jobs and programs.

All you really needs to know about the Table I will be using is that it is called TESTTABLE, and the timestamp columns I need to calculate the difference between are:

1. FROM_TIMESTAMP
2. THRU_TIMESTAMP

I can easily determine the difference between the two with the following SQL statement:

Improved SQL built in function for calculating difference between timestamps

I had written about the Db2 built in function TIMESTAMPDIFF, which is used to calculate the difference between two timestamps and return the difference in various different units of time. In the latest Technology Refreshes, IBM i 7.5 TR1 and IBM i 7.4 TR7, a new and improved built in function, TIMESTAMPDIFF_BIG, was introduced.

TIMESTAMPDIFF had issues in being able to correctly return the difference in microseconds. IBM's documentation says that the new TIMESTAMPDIFF_BIG will do that correctly. The syntax of the two built in functions, BiF, is the same:

Copying data with an Identity column from one table to another and not change the value

It has always been a problem with copying data from one SQL DDL table to another if the table contains an identity column. Identity columns are controlled by the database itself. When a new row is inserted into the table the database assigns the Identity column in the new row the next sequence number. They are a great way to ensure that the table has a unique key, and they can be shared with other files as an external key to link back to the original table.

The issue has always been that if I copy data from one table to another with an Identity column of the same name the database will assign the inserted rows new identity values using the sequence of the copied to table. This has been a great frustration to us all, and has led to many forsaking Identity columns altogether.

How to change someone's PDM defaults

I have been asked this question twice this week:

Is there a way to change everyone's PDM settings without them having to into their own settings and making the change themselves?

Both people want to change the "file" that contains these setting either with a SQL statement or they would write a program to do so.

I do not know where the PDM settings are stored. I assume they are in a file, or possibly even files. As the data is IBM's data I would be very wary to change it as I have no idea of what the consequences are if I was to make a mistake.

Easy way to validate if user has a certain group profile

This is another example of something I found by accident. Checking IBM's documentation I can find that this is found in IBM i releases 7.5 – 7.1, it might even be in earlier releases but IBM's online documentation only goes back to IBM i 7.1.

VERIFY_GROUP_FOR_USER is a SQL function that allows me to verify if the user of the current job is a member of any group profiles.

I could use the Display User Profile command, DSPUSRPRF, to display which groups and supplemental groups an user profile has. What is the fun in that when I can get the same information using SQL?

Finding out if I am authorized to a function usage identifier

Function Usage Identifiers are a way to be authorized to a particular function, without having the authority changed on my user profile. I have found it is a lot easier to persuade the sys admins to grant me authority to the Function Usage Identifier for something, than it is to have my user profile changed.

In this post I am going to show ways you can find the Function Usage Identifiers, see which ones someone is authorized to, and finally have a way to check in a program whether or not you are authorized to one of the functions. Fortunately all of this can be achieved simply using SQL. What I will be using are:

Using SQL to get the first of the month for a date

I have used the LAST_DAY scalar function to return last day of the month many times. I was interested to see in IBM i 7.5 TR1 and IBM i 7.4 TR7 that a new matching scalar function was added, FIRST_DAY.

As the name suggests FIRST_DAY will return the fist day of the month of the date passed to the scalar function. For example:

01 VALUES FIRST_DAY(CURRENT_DATE) || ' thru ' || LAST_DAY(CURRENT_DATE)

I have used FIRST_DAY and LAST_DAY to return the first and last days of the month for the current date. Rather than have this information in two columns I have concatenated these together to produce one result. I know the SQL purist are going to criticize me as I have used the double pipe symbols ( || ) rather than CONCAT. They will, rightly, point out that this using the double pipe will not work on other Db2 platforms. As I will only ever run this code in an IBM i partition, I am not concerned by this lack of cross-platform compatibility.

February 2023 Power announcement

On Valentine's day, February 14, Ken King, General Manager for IBM Power severs, published a blog describing the latest IBM Power announcements.

Not a lot about IBM i, but I am always interested to learn what is going on with the IBM Power server family of hardware and the operating systems it hosts.

You can read his blog, titled "Fueling Digital Transformation with IBM Power", here.

Discovering remote journals with SQL

I have an ongoing "battle" to keep the journals and their journal receivers under control in the IBM i partitions I am responsible for. Part of this has been to find and report on various features and functions of the journals. A recent addition to SQL, as part of IBM i 7.5 TR1 and 7.4 TR7, has been a View that returns results about the remote journals in a partition.

What is a remote journalling?

Remote journalling, in very simple terms, will send journal transactions to other partitions. I could use it to replicate changes made to file on the master partition to other partitions, and use those to update the remote files and keep all of the copies in synch.

Identifying profiles with disabled Netserver access

If you have users using your partition's IFS there are times when one of them manages to disable their access. In 2019 I wrote about how they can re-enable their access themselves using the QZLSCHSI API. What if I want to be more proactive and identify the profiles who already have disabled their IFS access?

Fortunately the information is attached to their user profile, but not in a place that I can get to with any of the User Profile commands, for example CHGUSRPRF. But I can use the SQL View USER_INFO. This View contains the column NETSERVER_DISABLED, if the User Profile has disabled their Netserver access then this column will contain 'YES'.

The statement I would use is:

Finding all the fields that contain certain characters

The germ for the idea for this post came from a question I saw in a Facebook Group. The question was how to identify records in a file where a particular field contains "special" characters, using SQL.

As the question was specially about a DDS file, I created a file, TESTFILE, with one field, FIELD1. I am not going to give the code for this file, just its contents. I would use the following SQL statement to list the contents of this file:

SELECT FIELD1 FROM TESTFILE

The results from this file are:

Finding a way to monitor for more than one message with RPG's monitor

Perhaps this title is a little misleading, I struggled to come up with something that adequately describe this scenario in the space allowed.

I was asked if there was an easy way when using a Monitor group to perform some kind of action if the message id starts with something like 'RNX12'.

Let me get start with my example RPG code:

Creating a User Index with SQL

When I wrote about a SQL procedure that allowed me to add data to a User Index, ADD_USER_INDEX_ENTRY, I created the User Index with the QUSCRTUI API. I received an email from an IBM employee telling me that there was a SQL procedure that would allow me to create the User Index.

I am going to do here is to create the same user index as I did with QUSCRTUI in that other post.

In this first example I am going to create the User Index with a fixed length entries of 100 bytes.

Getting an accurate count of characters in a variable RPG

Over recent years many of us are dealing with data in different character sets, not just the default CCSID of the partition we are using. The most common of these I encounter is UTF8.

UTF8 can contain double byte characters, which take two bytes for the character, as opposed to the standard single byte characters. If I am using a variable I have defined as UTF8 it is difficult to calculate the number of characters within, as the double byte characters result in an over count of the number of character present.

One of the additions to the RPG language as part of IBM i 7.5 Technology Refresh 1 and 7.4 TR7 are several things to make it possible to get a real character count from an UTF8 variable.

I am going to show several example programs to demonstrate how these new features work. Let me start with what I consider the most basic way, that is not bad thing I just me that IMHO the easiest. I am going to break this program into parts so it will be easy to explain what is happening. Let me start at the "top":

Procedure parameter option to convert data type

Another addition to RPG within the IBM i 7.5 TR1 and 7.4 TR7 updates was a new option in the OPTIONS parameter when defining the call to a procedure.

Previously if you passed, for example, a date to a procedure the parameter within the procedure's interface would have to be a date too. If the wrong data type was passed it would error.

The addition to the OPTIONS parameter is:

OPTIONS(*CONVERT)

Happy New Year 2023!

The old year is behind us, and I am always so hopefully and excited what the New Year will bring us all. Perhaps the new year will feel a bit more like the world returning to a kind of normal we have not seen for a couple of years.

In the IBM i world 2022 brought us:

• A new version of IBM i, 7.5
• Two new Technology Refreshes for 7.3 and 7.4, and the first TR for 7.5
• Merlin
• Midsized Power10 servers

What did you find interesting last year? These were the top five popular posts from 2022: