Wednesday, September 27, 2023

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:

Tuesday, September 26, 2023

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.

Thursday, September 21, 2023

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:

Wednesday, September 20, 2023

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

Wednesday, September 13, 2023

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:

Tuesday, September 12, 2023

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:

Thursday, September 7, 2023

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.

 

Wednesday, September 6, 2023

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

Friday, September 1, 2023

September's presentations

After having a month with no presentations I have three I am involved with this month.

  • Central Texas IBM i User Group
  • QUSER
  • COMMON India

Thursday, August 31, 2023

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:

Wednesday, August 30, 2023

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:

Monday, August 28, 2023

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.

 

Wednesday, August 23, 2023

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:

Tuesday, August 22, 2023

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:

Thursday, August 17, 2023

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.

Wednesday, August 16, 2023

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:

Wednesday, August 9, 2023

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:

Tuesday, August 8, 2023

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:

Wednesday, August 2, 2023

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:

Wednesday, July 26, 2023

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:

Tuesday, July 25, 2023

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:

Thursday, July 20, 2023

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:

Wednesday, July 19, 2023

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') ;

Thursday, July 13, 2023

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:

Wednesday, July 12, 2023

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:

Tuesday, July 11, 2023

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)

Wednesday, July 5, 2023

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.

Wednesday, June 28, 2023

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

Tuesday, June 27, 2023

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.

Wednesday, June 21, 2023

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.

Tuesday, June 20, 2023

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)

Wednesday, June 14, 2023

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) ;

Monday, June 12, 2023

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.

Wednesday, June 7, 2023

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.

Wednesday, May 31, 2023

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

Tuesday, May 30, 2023

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.

Wednesday, May 24, 2023

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.

Tuesday, May 23, 2023

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?

Friday, May 19, 2023

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.

Wednesday, May 17, 2023

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:

Thursday, May 11, 2023

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.

Wednesday, May 10, 2023

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

Wednesday, May 3, 2023

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.

Thursday, April 27, 2023

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.

Wednesday, April 26, 2023

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.

Wednesday, April 19, 2023

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

Monday, April 17, 2023

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:

Wednesday, April 12, 2023

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.

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

Tuesday, April 11, 2023

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:

Additions and changes to the RPG programming language are:

Wednesday, April 5, 2023

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: