Using values in an array for the SQL Where clause

It was a good question to ask: "Is there a way to use an array in the Where clause of a SQL Select statement?"

I am disappointed that it is not possible to use an array in that manner:

01 exec sql SELECT * FROM PERSON 02 WHERE LAST_NAME IN (:MyArray) ;

This code will not pass the SQL precompile. It gives the following error message in the precompile listing:

MSG ID SEV RECORD TEXT SQL0312 30 13 Position 32 Variable MYARRAY not defined or not usable for reason code 2.

When I look up that message I see that arrays are not allowed:

Program to capture CPU usage over time

I have been asked multiple times if there is an easy way to capture a list of jobs with the amount of CPU they, with a program. The answer is, of course, "Yes". I thought it was time to give an example of a program example of how I would do this.

My scenario is that I need to capture all the jobs in this partition with the CPU percentage they are using, every five minutes. This data needs to be captured in an output file, or in this example it's a table. The information I need is:

• CPU percent
• Job name
• Subsystem and the library it is found in
• User name
• Job type – batch, interactive, prestart, etc.
• Function and function type
• Job status
• CPU time
• Time the data was captured

PTFs for 7.5 TR3 and 7.4 TR9 now available

Today is the day the PTFs for the fall 2023 Technical Refreshes are available, IBM i 7.5 TR3 and 7.4 TR4.

This page shows the PTF numbers for the TR base PTFs:

• IBM i 7.5 TR3
• IBM i 7.4 TR9

If you also want all the new cool enhancements made to SQL and RPG you'll need to download the latest PTFs for Database, which you can find here (at the time of publication the link to 7.5 PTF is incorrect). According to the RPG Café page: "The 7.4 and 7.5 [ RPG ] PTFs are also available in Db2 for i Fix Packs"

I am excited about this and will be asking the folks at RZKH to apply all of these PTFs to the partition I use.

A quick and easy way to end a job

I have written programs that capture information over time, using a "never ending" Do-loop in a RPG program. When I wanted the job to end I would use the ENDJOB command. There must be an easier way to use ENDJOB without having to search for the job, copy it's name, and then manually enter the information into the command. This post will give you an example of how to do this.

What is becoming one of my favorite SQL View, ACTIVE_JOB_INFO, has exactly what I need to accomplish this. The column JOB_NAME_SHORT was added to this View in 2021 as part of IBM i 7.4 TR4 and 7.3 TR10. This column contains the job name's name, in this case SIMON_JOB. I can use this column to return the full job name and then end the job with that.

Another way to read a subfile

The idea for this post came from a question I was asked. I was asked to clarify an answer to a question someone was asked during a job interview. They had been asked was it possible to read a subfile without using the READC, read changed records, operation code? The interviewee answered that it could probably be read using a CHAIN operation. The interviewer told them they were wrong as it was not possible to use a CHAIN with a subfile.

The interviewee is correct. You can "read" a subfile using a CHAIN operation. This post demonstrates that is possible.

Before I start showing source code for a display file or RPG program, I am going to start with a DDS file I created:

My favorite file definition keywords in free format

This all started with a question, and then I got carried away. I was asked did I have a free format example code for what I described in my post about Useful keywords for your F-specs. Recently I have not used file definitions in my programs as I use SQL for my file I/O. I relished the thought of giving these file definition keywords, and then went on to show how they could be used.

The scenario is I have a file, TESTFILE, that I want to make a "work" copy in QTEMP. Then I want to have two members in the file and I fill the first with the contents of TESTFILE in ascending key order, and the second in descending key order. Just to make it interesting for myself, I will not use SQL for any of this, or for showing the results.

TESTFILE contains one field, NAME, and I can use the Run Query command, RUNQRY, to views it contents:

Validating an email address

I am sure every application has a need to validate email addresses. After finding a question on Facebook I decide to create one just to see how easy, or hard, it would be.

If this was going to be something that could be used from multiple programs it made sense to put the logic in a procedure that I can then bind into any programs that needs this functionality.

For this scenario I have created two new objects:

1. MODULE01:  The *MODULE that contains the procedure ValidateEmail
2. RPGPGM01:  A *PGM that needs to call the procedure to validate various email addresses

IMHO it make sense to start showing and explaining the procedure. As it is long, rather than show it all at once I am going to show it in two parts. The first part:

Testing the new save compression ZLIB

After applying the latest round of CUM PTFs to a partition with IBM i 7.4 I noticed that the new save compression algorithm, ZLIB, is available. I was surprised as I was under the impression that ZLIB was only available for IBM i 7.5 . There is no mention of ZLIB in the help for the Save Objects command, SAVOBJ, data compression parameter's help. Was this an oversight by IBM introducing something that was not compatible 7.4? This made me want to test it to see if it worked in 7.4, and if it did how much were the savings compared to the other types of data compression.

I have before compared the savings I could get with the various compression algorithms. Now I could compare them all to the new ZLIB.

Updated Power 10 performance guide published

On Wednesday Steve Will, IBM's CTO and Chief Architect for the IBM i operating system, announced on social media that an updated version of the "IBM Power 10 performance optimization for IBM i" had been published.

The timing could not have been better as the company I work for is installing their new Power 10 server next week.

I have read the guide and am impressed with all the advice that is offered.

You can reach the guide by using this link here.

Renaming a CL procedure's name in a RPG program

One of my criticisms when using CL procedures in my RPG programs is that I had to use the name of the module. Sometimes the ten character name of the procedure, and module, was not descriptive enough for my sensibilities. I have discovered that a solution was provided in IBM i 7.2 that allows me to give my own name to CL procedures.

In this example what the code for the CL procedure is not necessary. All I need to know is that it will copy a file to a IFS folder. It has four parameters:

1. Library the file is in
2. File name
3. Path name, directory and name the file will be called in the IFS
4. A code returned from CL procedure to whatever called it

Calculating the sine, cosine, and tangent

I was asked what is the best way to calculate the sine for a number within a RPG program? I know my answer frustrated the person who asked as I replied with "It depends".

I can think of two scenarios that I would use different approaches for:

1. Calculate the sine for a value in a file or table's field or column when "reading" the file or table
2. Calculate the sine for a value calculated within a program

In my examples I am going to also show how to calculate the cosine and tangent too.

Using the time zone UDF I created in RPG

Yesterday I showed how to create a SQL User Defined Function, UDF, to return the current timestamp for another time zone.

Several people messaged me asking me how I would use this in a RPG program. Rather than answer them individually I thought would share my answer with you all.

I could think of two examples of when I would use the THEIR_TIMESTAMP UDF in a RPG program:

1. I want to retrieve the timestamp from the UDF into a RPG variable, that I can then use elsewhere in the program
2. Use it as part of an SQL Insert statement into a DDL Table or DDS file

Let me start with the first scenario: retrieving the timestamp from the UDF into RPG variables:

Determining the time zone with a user defined function

In a previous post I wrote about calculating the time in different places in my IBM i partition. I mentioned at the end of that post that I had created a User Defined Function, UDF, to perform that calculation for me. This post is to show you how I did that.

As I described in the previous post I can calculate the UTC by using the CURRENT_TIMEZONE special register:

UTC time = CURRENT_TIMESTAMP – CURRENT_TIMEZONE

It is not possible to know what other time zone's difference is from UTC without using a table to contain the following information:

More information about the Fall 2023 TRs

Information is still coming out about the Technology Refreshes announced yesterday.

Steve Will, Chief Architect for IBM i, gave his take on these in his "You and i" blog, you can read that here.

IBM has worked with COMMON North America to share two videos about these TRs:

• IBM i Latest and Greatest with the Business Architects


• Latest Db2 for i SYSTOOLS Updates for Every IBM i User by Scott Forstie, Db2 for i Business Architect.

You will need to register for both of these recordings. It requires a COMMON North America profile to do this, which is free to create.

I am still looking out for anything else I can find out about these TRs that I will share with you on this page.

Fall 2023 TRs announced

Today is the day that IBM has announced the Fall 2023 Technology Refreshes for the currently supported releases of IBM i, 7.5 TR3 and 7.4 TR9.

All the information about the TRs can be found on IBM website on the following on the following pages in IBM's websites:

• IBM i 7.5 TR3
  • IBM announcement
  • IBM Support wiki
  • RPG Cafe page

• IBM i 7.4 TR9
  • IBM announcement
  • IBM Support wiki
  • RPG Cafe page

Determining the time zone, and calculating the time in another country

I was asked if there is a way to calculate the current time in Japan, in an IBM i partition in the USA. To add to the complication in doing this the use of Daylight Savings Time, DST, has to be handled. Most places in the USA observe DST, not all other countries do.

Every IBM i partition has a set of system values that are related date and time. Two of these are particularly relevant here:

• QTIMZON:  Time zone
• QUTCOFFSET:  UTC offset, hours and minutes different from UTC. UTC the same as GMT, Greenwich Mean Time

I can retrieve these two system values with a SQL statement using the SYSTEM_VALUE_INFO View:

Deleting records from a file without the delete trigger executing

I have a file that has a "delete trigger" on it. I need to delete all of the records in the file without the "delete trigger" inserting data into its output file. How could I do that without removing the trigger from the file?

I am going to show you how.

Before I do I am going to need a file with data. I have created a file, TESTFILE, in my library, MYLIB, that has ten records in it:

Creating a spool file from a modern RPG program with no output specs or custom printer file

I am not sure if the title really describes what this post is about. The germ for this post came from an email I received from Elbert Cook. He asked me about emulating output specifications in a modern RPG program. Modern free format RPG does not support output specifications. I am pleased about this. I can get far more functionality and make changes many times faster by creating my own printer file than I would making changes to an output specification. Some have argued to me that printer files are too new for them to use, which is not true as I first used them when this operating system was OS/400 and it was running one of the releases of version 2.

Let me stress that what I describe in this post is NOT a case for creating something like output specifications. Use custom printer files.

There are times where I have created printed output from a program I was testing or developing that was so "down and dirty" I did not want to spend my time creating a printer file for. This post is based upon the methods I have used, and some examples from Elbert.

Creating a spool file from a SQL Select

I was asked if it possible to create a spool file from the results of a SQL statement. It most certainly is! I have done it several times, and I was surprised I have not written about it here. I know I could do this with SQL embedded in RPG, but I wanted to do this not using RPG and have SQL do most of the work.

I have written about Query Management queries, QM queries, before. They are, basically, a compiled SQL statement. I execute them using the Start Query Management Query command, STRQMQRY. One of the parameters of the command is an option to *PRINT.

Before I show examples of QM queries I need a file of data to use. It will come to not surprise to you that I am going to use one called TESTTABLE. The source code for the Table is:

New functions added to assist with Boolean data type

The Boolean data type was introduced as part of IBM i 7.5, and does not work for lower releases.

With the last couple of Technology Releases, TRs, a number of SQL functions have been added to make using the Boolean data type easier. These are:

• BOOLEAN:  Returns Boolean value for parameter
• ISTRUE:  Returns "true" if the parameter is Boolean true, and if not returns "false" or null
• ISFALSE:  Returns "true" if the parameter is Boolean false, and "false" or null if it is not
• ISNOTTRUE:  Returns "true" if the parameter is not Boolean true, and returns "false" or null if not
• ISNOTFALSE:  Returns "true" if the parameter is not Boolean false, and if not then "false" or null

Creating a pivot table with SQL

Alas, if I wanted to create the equivalent of a pivot table using Db2 for i there is not the equivalent of the PIVOT relational operator that there is in Microsoft SQL Server.

A pivot table is taking a table's data that is in rows and "turning on its side" to become data in columns. This is best demonstrated with an example. I have a file that lists sales by color, month, and year in separator rows. I want to convert that to be one row per column and year. If I am little creative I can do pivot data with Db2 for i.

I have a SQL DDL Table, TESTTABLE that contains the data I want to pivot:

Selecting data I just inserted into a Table

This piece of SQL was brought to my attention by a friend who works with Db2 for z/OS (mainframe). He said that at times he uses what I am going to explain to validate that the data he inserted into Table has the values he expected.

He refers to it as "Select final table", which will be the name I am going to use for it. I did find this referred to in the IBM Documentation web site, there is a link to this page at the bottom of this post. The Db2 for z/OS version has more functionality than one for Db2 for i. This has me puzzled, why they are not the same?

With Db2 for i "Select final table" can only be used with a SQL Insert subselect. It returns the rows that were inserted into the table.

The Table, TESTTABLE, I will be using in these examples has two columns:

AS/400 launch TV news story

It has been a few months since the IBM i's 35th anniversary, I wanted to share this old news story from KTTC TV, Rochester Minnesota, broadcast on the day of the launch of the AS/400, June 21, 1988.

Click on the image below to open the video of the news story.

 

Reading from multiple members in a CL program

It has been a while since I wrote a post about CL. I have been given an opportunity to do so again when I received this question:

If my file has 5 members and I want to read only first record of each member in CL how can we do that ?

Yes, this is entirely possible using CL, with no help from RPG.

Before I start showing CL code I am going to need a file, which you will not be surprised I called TESTFILE, and it is in my library, MYLIB. The DDS code for this file is:

01 A R TESTFILER 02 A TEXT 45A

Cancelling a SQL job

Have you ever been in the position of having a SQL statement running in ACS's Run SQL Scripts, RSS, and you realize that the reason it is taking so long is something in your selection criteria is too large. I am sure the same thought has crossed all of our minds: "Wouldn't it be nice if I could just cancel this statement!"

There is a way to do this using the procedure: CANCEL_SQL. The documentation describes this as:

CANCEL_SQL() procedure provides an alternative to end job immediate

This is true I could cancel the RSS job with the End Job command, ENDJOB, with the *IMMED option:

Faster way to find who answered a message

Six years ago I wrote a post about using the MESSAGE_QUEUE_INFO SQL View for finding jobs that had errored, and who had answered those messages. What I would like to do today is to give an example of more efficient, faster, way to get the same results using the MESSAGE_QUEUE_INFO Table Function.

On the whole retrieving results from Table Functions tend to be faster than getting the same results from a View. Most Table Functions have parameters that are used to narrow down the results to a smaller set of results, which can then be interrogated with the Where clause. With a View I have all the results there can be, and then the Where clause has to search through all of those to find what I want.

The MESSAGE_QUEUE_INFO Table Function has four parameters. I am going to be using three in these examples:

Copying save file from PC to PUB400.COM

If like me you are a user of PUB400.COM you will probably find this video useful. There are times where I have a save file I copied from another IBM i partition that I want to restore onto PUB400.COM, this video explains how to do it.

Click on the image below for it to open in YouTube.

 

Convert character to hexadecimal with SQL

A couple of weeks ago I wrote about using Machine Instruction, MI, procedures in RPG to convert a character string to hexadecimal, and then convert it back again to character. Several people sent me examples of converting character to hexadecimal using a SQL statement, but no-one sent me the statement to convert the hexadecimal to character.

I decided to give an example of how I would do what I did last week in SQL. I embedded the SQL statements into a RPG program as this would be the way I would likely use it.

Below is my example program:

Now able to 3 part name table functions

IMHO this is a big deal, I can now use the SQL three part name to retrieve data from Db2 for i SQL Table functions on other partitions. The three part name is where I give the "database" name along with the schema and object name. In the IBM i world I can translate that to:

partition.library.object

The dot (otherwise known as: period or full stop ( . )) must be used as the separator, rather than the slash ( / ).

For example if I want to retrieve a list of objects of the files in my library on another partition, I will be calling the other partition OTHERSYS. I can use the following statement to retrieve data from the other partition using the three part name with the, for example the SYSFILE View:

ACS 1.1.9.3 released

A new release of IBM's Access Client Solutions, ACS 1.1.9.3, was released yesterday.

I have tried the "Check for Update" link to see if it acknowledges the new release, it does not. ACS 1.1.9.1 never alerted us that there was a new release, and I would not wait for ACS 1.1.9.2 to do so.

Get the new version's install zip file from IBM using: http://ibm.biz/IBMi_ACS

Don't worry if you don't have an IBMid, you can create one in a couple of minutes.

Confirm your agreement with IBM's license.

List and changing object owner with SQL

At my work one of the things that auditors look for is who owns all the objects in the production libraries. If there are objects that do not belong to the expected user profile then they want an explanation of why they do not. The week before the auditors are due I do a "scan" of all the production libraries, I am responsible, for to look for any objects that do not belong to the expected user profile, it's better to be prepared than be surprised by the auditors.

Fortunately I can perform the "scan" of all those libraries at once with a SQL View, OBJECT_OWNERSHIP, to return a list of objects that do not belong to the expected user profile.

For the purposes of this post I am going to limit my examples to the objects in my personal library, MYLIB. And I am only interested in a few of the columns of this View:

Creating JSON array with SQL

Last week I showed how I could retrieve information from a JSON array using SQL. This week I thought it would be a good thing to do the opposite: create a JSON array and insert it into a column in a SQL table.

To accomplish this I will be using several Db2 for i Table functions:

• JSON_ARRAY:  Creates a JSON array
• JSON_ARRAYAGG:  Creates a JSON array with an array element from each result row returned from a SQL statement
• JSON_OBJECT:  Create a JSON object (not a physical object like a file)

Creating the most basic JSON array is very simple. I just need to use the JSON_ARRAY table function and pass values to it:

Convert character to hexadecimal

The idea for this post came from a question I was asked by a friend. In a RPG program is there a simple way to convert a character value to hexadecimal, and vice versa.

I know of two Machine Instruction procedures that will do this for me:

1. cvthc:  Convert character to hex
2. cvtch:  Convert hex to character

In my examples I am going to convert a couple of character strings to hex, and then convert one back from hex to character. All of this I performed in one RPG program. Let me start by showing all the definitions:

Retrieving data from a JSON array with SQL

I have noticed with a few of the enhancements made in recent Technology Refreshes to various SQL Views and Table functions that new columns have been added that contain JSON arrays. For example the BASED_ON_FILES column in the SYSFILES View. I want to be able to convert the data held within the JSON array into individually columns. With Join logical files, SQL Views and Indexes the object could be based upon more than one data source.

Before I show how to extract the data from the BASED_ON_FILES columns I want to give a simpler example that will allow me to explain how to do it. Before I start I need to thank Brian Hill for his help with the code.

My example JSON array looks like:

Using SQL to make a list of defective PTFs

This is something I know that System Admins should be checking, defective PTFs in their partitions. As part of the latest Technology Refreshes, IBM i 7.5 TR2 and 7.4 TR8, is a SQL View that allows me to generate a list of defective PTFs. Previously this was only available in QMGTOOLS, alas I am not authorized to that in any of the partitions I have access to, therefore, you'll have to take my word that option 24, PTF Menu, then option 3, "Compare defective PTFs from IBM", takes you to the same information.

The View DEFECTIVE_PTF_CURRENCY is found in the SYSTOOLS library. It returns a list of defective PTFs that do not have the corrective PTF applied.

If this is the first time you have encountered this View I suggest you use the following statement to see what defective PTFs there are in your partition, and what columns of information are returned to you:

Creating a unique value across multiple partitions

This is another case of stumbling upon something that I am likely to use in the future. As I work in a multiple partition environment I need to keep rows of data unique not only with each file but also across partitions. Using an identity column will keep the rows unique within one table, but not across the same table in different partitions.

While searching for something else I found the SQL scalar function GENERATE_UNIQUE. This creates a 13 long bit data character value that is made up of an UTC timestamp and the system serial number. This can be used for a unique value in the table as each successive row that is added has a different timestamp value.

I can how what this looks like using the following statement:

Date arithmetic functions added to SQL

In the latest round of Technology Refreshes, IBM i 7.5 TR2 and 7.4 TR8, a number of date arithmetic functions were added to SQL.

I have to admit I was surprised by this as I did not consider the way I had been doing date arithmetic with SQL as lacking.

These new SQL functions are:

• ADD_YEARS
• ADD_MONTHS
• ADD_DAYS
• ADD_HOURS
• ADD_MINUTES
• ADD_SECONDS

They all have the same syntax:

Lookup IP address host using SQL

Added as part of the recent Technology Refreshes, IBM i 7.5 TR2 and 7.4 TR8, is a new scalar function that returns the hostname for an IP address.

The syntax for this scalar function, DNS_LOOKUP_IP, is simple, and can be used in one of two ways:

01 VALUES DNS_LOOKUP_IP(IP_ADDRESS => '123.456.789.12') ; 02 VALUES DNS_LOOKUP_IP('123.456.789.12') ;

Service extension for old releases of IBM i

I am grateful to the person who sent me the link to the IBM Support document "Service Extension for IBM i 7.3, 7.2, and 7.1", dated July 10, 2023.

I am not going to repeat what the document says, I will provide a link to it below.

The last dates that you can have support for these old releases is:

Use new RPG BiFs to see if input parameter was passed

I cannot remember how long ago it was when IBM introduced the %PARMS RPG Built in Function, BiF, that would return the number of parameters were passed to the program. This has been "refined" in IBM i 7.5 TR2 and 7.2 TR8 with two new BiFs:

• %PASSED:  Returns logical true if the parameter was passed
• %OMITTED:  Returns logical true if the parameter was omitted

To show how these BiFs work I created a procedure, that contains the new, and a RPG program to call the procedure.

Let me start showing by showing the procedure, Procedure:

Using SQL to get Network Attributes

This new View, NEWORK_ATTRIBUTE_INFO, was introduced as part of IBM i TR2 and 7.4 TR8. It shows the same information as the Display Network Attributes command, DSPNETA, and allows you to retrieve the same information as you would using the Retrieve Network Attributes command, RTVNETA.

I will admit I don't use this information much, in fact the only attribute I use is the Current System name. In a CL program I retrieve it using RTVNETA:

01 DCL VAR(&SYSNAME) TYPE(*CHAR) LEN(10) 02 RTVNETA SYSNAME(&SYSNAME)

Finding which fields were defined using a reference field

In my opinion one of the best things about the DDS database is the use of Reference fields. I can define a field in a one file, a "Reference file", and then use it to define fields in other files. These fields will inherit the properties of the "Reference field".

If I need to make a change to the database, changing the size on one field, I can compile all the files that use the "Reference field" and the change will made to the file.

But how can I know which files use a particular "Reference field"?

Fortunately a SQL View gives me the information I need to do this.

Changes to the RPG Select operation code

In my RPG work I have been using the IF-ELSEIF operations code instead of Select groups for longer than I have been writing this web site. I preferred them as I could do the same as a Select operation code in less lines of code.

But then comes along two new RPG operation codes in the latest Technology Refreshes, IBM i 7.5 TR2 and 7.4 TR8, that are related to the Select operation code that might change my mind.

The two new RPG operation codes are:

• WHEN-IS:  Used to compare the given value is equal to a value or variable
• WHEN-IN:  Used to compare the given value is in a list or values, array, or in a range

Copying multiple IFS files into one output file

This us another post that came from a question I was asked by a reader of this blog. The question is:

I have multiple files in an IFS folder with similar names, each has a timestamp at the end of the file name to make them unique. How can I read all the files and write the contents into one file?

Fortunately there are two SQL Table Functions that can help me do this:

• IFS_OBJECT_STATISTICS:  To make a list of the files
• IFS_READ:  Read the contents of the files

The rest is pretty straightforward SQL and RPG.

35th anniversary of IBM i

There has been lots of publicity in the build up to today, and I thought I would add my thoughts on the 35th anniversary of IBM i. That is a little misnomer as the server that was launched on June 21, 1988, was called AS/400, IBM Application System/400, which ran the OS/400, Operating System/400, operating system. Over the intervening years both the server and the operating system have been enhanced to be so much more than AS/400 / OS/400, becoming a thoroughly modern server, IBM Power, and operating system, IBM i.

As this is an anniversary I am not going to talk about the future of IBM Power and IBM i, which is "golden", I am going to write about the history of this.

Getting Save File information with SQL

Added to our SQL "toolkit" with the latest round of Technology Refreshes, IBM i 7.5 TR2 and 7.4 TR8, were two Views and a Table function that helps me get information about Save Files, and their contents.

Prior to these if I wanted to know how many save files I have in my library I would use the Work Object PDM command, WRKOBJPDM:

WRKOBJPDM LIB(MYLIB) OBJTYPE(*FILE) OBJATR(*SAVF)

RPG %SPLIT now handles blank sub-strings

When it was introduced in IBM i 7.4 I thought the Split built in function, BiF, would be useful way to break apart a string into pieces. The one frustration I had with it was when two separators were next to one another %SPLIT would not regard it as an empty, or null, sub-string.

Let me jump straight into my RPG program to demonstrate how this works. Let me start with all the definitions, etc.

01 **free 02 dcl-s String varchar(100) inz('RED,,BLUE,,GREEN,,,BLACK') ; 03 dcl-s Piece char(10) ; 04 dcl-s Array char(10) dim(*auto:999) ;

It has been a decade!

Saturday was the tenth year anniversary of this web site. IMHO that is a remarkable milestone. I have seen other IBM i related web sites and YouTube channels come and go in that time, as people do not appreciate the effort it takes to research, write, and publish content on a regular basis. I am not sure how long I thought I would write, I guess it was until there was nothing more about IBM i to write about. Fortunately IBM has obliged and this decade has seen more additions and changes to our favorite operating system than any other. Long may it continue.

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.

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