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:
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.
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:
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:
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:
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:
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.
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 |
After having a month with no presentations I have three I am involved with this month.
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:
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:
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.
|
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:
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:
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.
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:
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:
Creating the most basic JSON array is very simple. I just need to use the JSON_ARRAY table function and pass values to it:
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:
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:
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:
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:
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:
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:
They all have the same syntax:
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') ;
|
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:
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:
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:
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) |
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.
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:
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:
The rest is pretty straightforward SQL and RPG.
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.
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) |
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) ;
|
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.
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:
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.
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:
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.
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.
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?
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.
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:
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.
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:
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.
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.
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:
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.
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:
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:
Notice: As of April 27 this method is still not working. Use the second method described below.
You will see the following window:
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.
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:
All the information about these TRs can be found:
Additions and changes to the RPG programming language are:
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: