Wow! Another year has passed and RPGPGM.COM celebrates its eighth birthday today. I know I act surprised every year, I do so for two reasons:
Despite lockdowns and COVID I have an exciting year:
One of the many things that caught my eye in the latest round of Technology Refreshes for 7.4 and 7.3 is a way to send messages to the System Operator message queue, QSYSOPR, using a SQL procedure, SEND_MESSAGE.
Before I used the Send Message command, SNDMSG, to do this:
SNDMSG MSG('Message from SNDMSG command') TOUSR(*SYSOPR)
Additional Message Information
From . . . . . . : SIMON Severity . . . . . : 80
Message type . . : Information
Date sent . . . : DD/DD/DD Time sent . . . . : TT:TT:TT
Message . . . . : Message from SNDMSG command
|
This new SQL procedure, SEND_MESSAGE, must use a predefined message that must have one substitution parameter that is defined as *CHAR *VARY 2.
My first thought was to use the CPF9898 message, but that has a severity of 40, and it is fixed length of 512.
I decided to create my own message I could use. To avoid any confusion with any existing messages in any messages files I created my own message file, and added my message to it:
Several years ago I wrote about how to convert spool files to PDF in the IFS. It still remains one of the most popular posts in this blog. With the latest round of Technology Refreshes for 7.4 and 7.3 there is now a way I can generate PDF from spool files using SQL.
I have several spool files I can see using the Work With Spool Files command, WRKSPLF:
Work with All Spooled Files
Opt File User Queue User Data Sts
_ QPRTSPLQ SIMON MYOUTQ RDY
_ QSYSPRT SIMON MYOUTQ Testing RDY
_ QPQUPRFIL SIMON MYOUTQ RDY
_ QSYSPRT SIMON MYOUTQ RDY
|
I can get the same information using SQL too using the View OUTPUT_QUEUE_ENTRIES_BASIC, which gives me a subset of the columns that are available in OUTPUT_QUEUE_ENTRIES, which makes it faster to return the results:
Two years ago IBM introduced a SQL function, SPLIT, that would break apart a string into parts. In the latest Technology Refreshes for IBM i 7.4 and 7.3 introduces into RPG a Built in Function, BiF, that does a similar thing.
The Split BiF, %SPLIT, breaks apart data from a string into a temporary array.
Let me started with some examples:
01 **free
02 dcl-s String char(100)
inz('RPGPGM is a website that provides stories about IBMi') ;
03 dcl-s wkArray char(10) dim(10) ;
04 wkArray = %split(String) ;
|
When I saw these mentioned in the list of new enhancements made to RPG in IBM i 7.2 TR and And 7.3 TR10 the word "Finally" escaped my lips. Now we have built in functions, BiF, that would allow us to easily convert upper case letters to lower case, and vice versa.
For years I have been able to convert the case of characters in CL:
CHGVAR VAR(&STRING1) VALUE(%UPPER(&STRING1)) CHGVAR VAR(&STRING2) VALUE(%LOWER(&STRING2)) |
And in SQL too:
I am not sure why this did not publish on Friday, which is when all the PTFs became available.
On Friday (May 14, 2021) the PTFs for IBM i 7.4 TR4 and 7.3 TR10 are now available for download from IBM.
The PTFs for these TRs can be found here:
You will need to get and install latest PTFs for Database to get the TR enhancements for Db2 for i/SQL.
And check for any new RPG PTFs. Some of them were released in April, see here for information on those.
For information on what is in these new TRs check out the post I wrote when the announcement was made last month.
I am pleased to say that the guys at RZKH have already loaded the new PTFs, a big Vielen Dank to them. I will writing about all new features and functions them in the following weeks.
I am sure that the person who asked me if this was possible is not the first person to have tried, and was unable to get it to "work".
They had been presented with a "flat" file where the first two characters denoted the length of the key value. What they need to do was to extract the variable length key into another value they could use.
The file was like this:
DATA ---------------------------------- 10<-- 10 -->XXXXXXXXXXXXXXXXXXX... 05<-5->XXXXXXXXXXXXXXXXXXXXXXXX... 20<------- 20 ------->XXXXXXXXX... |
It is easy to extract the first two characters from DATA using the following SQL Select statement:
01 SELECT SUBSTR(DATA,1,2) AS "Length", 02 DATA 03 FROM TESTFILE ; |
Which returns:
I was asked if it was possible to see which DDS file or SQL index was used by a SQL statement?
Fortunately it is easy to see. But before I show how to do it, let me set up my "test data".
I have my physical file, TESTFILE, which has no key:
A R TESTFILER A FLD001 6P 0 A FLD002 10A A FLD003 L |
I built two logical files built over this physical file. TESTFILEL0 has the field FLD001 as its only key:
A R TESTFILER PFILE(TESTFILE) A K FLD001 |
And TESTFILEL1, which has the key field FLD003:
In my opinion the long SQL names I can give to Tables, Views, and Indexes allows me to give these objects good descriptive names. I also give them short system names, so I can easily use IBM i commands with them. But this can cause me problems.
I have a table, you will not be surprised to learn I have called it TESTTABLE and it resides in my library MYLIB, that I want to make a copy of to the library MYLIB2. I am sure I am not the only person who would use the Create Duplicate Object command, CRTDUPOBJ, to do this:
CRTDUPOBJ OBJ(TESTTABLE) FROMLIB(MYLIB)
OBJTYPE(*FILE)
TOLIB(MYLIB2) NEWOBJ(@CRTDUPOBJ)
CST(*NO) TRG(*NO)
|
I get the following message to confirm that the file was created in MYLIB2.
Object @CRTDUPOBJ in MYLIB2 type *FILE created. |
Today is the end of regular support for IBM i version 7.2.
Launched in May 2014, 7.2 has been a supported operating system for seven years. With its coming off support there remains only two releases of IBM i that are still supported, versions 7.3 and 7.4.
If you are still using 7.2 and you want continue support ask your IBM Business Partner if there is a Service Extension agreement. I have seen one mentioned as "TBA", but I have not seen any details. As an incentive to move to a more modern release IBM extended support tends to be more expensive than support was before.
If are using 7.2 and you can upgrade to a new release then you ought to consider doing so sooner rather than later, so that you will remain on IBM support. Being off support is not a good idea as you can no longer expect PTFs if any security updates, etc., are needed to keep your business safe and secure.
More and more of us are using SQL to modernize our databases from DDS files to SQL tables, view, and indexes. When you rework your files into tables you quickly realize the benefit of converting that 50 alphanumeric field in the DDS file to a variable length character, VARCHAR, column in the SQL equivalent. This conversion saves disk space as the data in that column is only stored as the length it is, not the whole 50 characters.
RPG and SQL easily copes with this type of column. Alas, CL does not. If I try to declare file, DCLF, that contains variable length columns the program will not compile without using a special option in DCLF.
Before I start showing CL code I need to start with a SQL table, that contains a VARCHAR column:
Db2 for i comes with a host of built-in global variables and special registers that provide us with some of the basic information about the environment of the IBM i we are using.
When I am using ACS's Run SQL Scripts it is useful to be able to quickly check these values. Or if I am doing a presentation I need to be able to show which IBM i partition I am using to help people to understand what I am doing.
If I wanted to see which partition I am using I could use the following SQL statement:
|
I am sure more of you are familiar using the SYSDUMMY1 table in the library SYSIBM to do this kind of thing.
I am all for shortcuts and making things easier for myself. I need to give credit to Paul Tuohy for showing this particular shortcut.
Rather than using the Select statement I can use the VALUES statement in its place. By using the VALUES I do not have to define a file. If I want to display which database server my Run SQL Scripts session is attached to I could just use the following:
|
It is 2021 and I hope we should all be using IBM's ACS with our IBM i partitions. I have the Run SQL Scripts tool open all day as it makes it so easy to develop SQL code that I will copy and paste into in a program, procedure, etc. or to test any SQL Views or Tables I have built.
In my opinion the Run SQL Scripts tool is so much better than the STRSQL command as I can see my statement and in the case of a Select my results on the same screen.
|
The problem is that this locks the object(s) I used in the Select statement. If I need to recreate those objects I get a CPF3202 message:
A new version of IBM's Access Client Solutions, ACS, has been made available. It is free so why would you want to use another tool? ACS is so much more than just 5250 emulation.
I can get the new version in two ways
I can go to the IBM website directly : ibm.biz/IBMi_ACS.
Or I can go to my ACS main window, click Help > Check for updates.
Looking on various social media feeds it would appear that the RPG PTFs for the latest Technology Refreshes enhancements have been made available today. These are for RPG's new %UPPER, %LOWER, and %SPLIT Built in Functions.
The RPG PTF numbers will be different for the two releases you will have to go to the IBM Support center and check which are the ones relevant for your release, or ask you System Admin to do so. PTF numbers:
I hope to get my hands on these soon, and applied, so I can start writing about them on this blog.
Yesterday I received the following message in my Blogger account:
FollowByEmail widget (Feedburner) is going away
You are receiving this information because your blog uses the FollowByEmail widget (Feedburner).
Recently, the Feedburner team released a system update announcement , that the email subscription service will be discontinued in July 2021.
After July 2021, your feed will still continue to work, but the automated emails to your subscribers will no longer be supported. If you'd like to continue sending emails, you can download your subscriber contacts.
If you have subscribed to the "Follow This Blog By Email" feature you will stop receiving the email notifications from this site in July.
I am removing this feature as there seems little point in allowing people to signup for a service that is going away, and I will look for another similar application. If I find one that meets my expectations I will add it as the new "Follow By Email".
Other links Blogger supplied explaining what is going to happen are:
Today IBM has announced the latest Technology Refreshes for the current IBM i releases, 7.3 TR10 and 7.4 TR4. While the PTFs for some of these will be available on Friday (April 16, 2021) most of them will not be released as a PTF until May 14.
What's in these TRs?
For 7.4 TR4 only: Blocked insert is extended to include tables containing generated columns.
I was asked:
What about referenced tables when you specify a foreign key when creating a table? I can check this when I run DSPFD, it is in Parent File Description but I still can't find an SQL to elaborate all tables in our Application.
I have to admit this one took me some time to find the information I need to provide the example in this post.
A foreign key is one of the many constraints that can be used with Db2 for i tables and DDS files. Doing a quick search of the IBM KnowledgeCenter I found the following:
In the western Christian calendar today is doubly special. It is Easter day, and it is Saint Isidore of Seville saint's day.
St Isidore of Seville is special to the Information Technology community as in 1997 he was made patron saint of all things related to computers and IT by Pope John Paul II. You can learn more about Isidore here.
Happy St Isidore of Seville day, and let's hope for a little divine intervention so all of our IT projects in the next year complete successfully, on time, and on budget.
Yesterday afternoon I found that IBM had replaced their documentation website, IBM KnowledgeCenter, with a new one, IBM Documentation.
While the home pages of the KnowledgeCenter for the various releases of IBM i redirect to the equivalent page in the new IBM Documentation, the subpages do not. This means that the links to KnowledgeCenter pages at the bottom of the posts in this blog will not redirect to their equivalent in the new site.
What am I going to do? The links in the menu on the right of all pages now link to the new site. If you find a link in this blog that does not link to the Documentation site you can use the links on the right, and then search the Documentation site for the relevant information.
The links to the Documentation pages for each release are:
Barbara Morris, lead developer for the RPG compilers in the Toronto labs, posted a comment in this post:
I tried clicking on the link for %LIST at the end of this article https://www.rpgpgm.com/2020/11/new-rpg-features-for-arrays.html and it correctly redirected. So maybe the redirects were not all in place yet at the time you tried them, Simon.
I have clicked on an assortment of links in this blog too and it would appear that the old URLs do redirect to the new URLs. This makes me a very happy man.
Thank you IBM for putting the redirections in place.
The question was simple: Can you tell when a subsystem started?
I am going to give two examples. The first will show what I would do if you asked me what time the QPGMR subsystem was started. I only need to give an answer one time, and I do not need to write a program or code to do this.
In the second example I do want some code to show when the QPGMR subsystem was started. This would be used in a scenario where I would need to know frequently what time it started. I would add it to the Job Scheduler, run the job periodically, and it would send me an email with the date and time it started included within.
Many years ago, and yes it is so long ago I cannot remember exactly how long ago, when I was first embedding SQL into my RPG/400 programs I was introduced to the WHENEVER SQL statement. This statement can be used to cope with SQL errors and warnings returned from the previously executed statement. Personally I do not like what it does, and I do not use it in my programs.
In the announcement for the latest Technology Refreshes, IBM i 7.4 TR3 and 7.3 TR9, was mention of enhancements made to this SQL statement, which I think has won me over.
Before I explain what these enhancements I need to show the way the WHENEVER statement works without these enhancements. Even after the TRs you can still use the same old way.
The WHENEVER allows you to handle three types of exceptions:
One of the problems of using the SQL Views to list the columns of SQL Tables, Views, etc. is that they do not include objects in the library QTEMP. The latest round of Technology Refreshes, IBM i 7.4 TR3 and 7.3 TR9, has brought us a View that allows me to get to the information of those SQL objects in QTEMP.
Most people use the SQL View SYSCOLUMNS to learn about the columns in their SQL objects. For several IBM i releases has had a similar View SYSCOLUMNS2 that contains all the same columns, even using the same column names, and with ten additional columns not found in SYSCOLUMNS. If you compare the list of columns in both Views the last column common to both is HAS_FLDPROC.
IBM recommends us to use SYSCOLUMNS2 rather than the other view as:
... a query that uses SYSCOLUMNS2 will typically perform better than querying SYSCOLUMNS.
The question was: How can I check if job is still on the job queue and if it is held?
Fortunately the solution is simple using SQL. I can check a specific job queue or all for the job. The gotcha is that the job name does need to be unique. If the job was submitted to batch with the default job name, which is the user profile of the job that submitted it, it will be hard to determine which job is the one I want. But as I always submit jobs with a distinct name it is easy. In this example I want to check for a job called TRIAL_BAL that is submitted to the QPGMR job queue, which is in the QGPL library.
I have given examples of using the JOB_INFO SQL Table function in a previous post, but I did not write about how I can use it to get details of jobs waiting in job queues.
JOB_INFO Table function has a parameter called JOB_STATUS-FILTER that can be used to limit the type of jobs returned in the results. When *JOBQ is used it will return all of the jobs waiting on job queues.
If I use this SQL statement I get every job that is waiting on a job queue returned in the results:
Someone created a SQL DDL Table with a long name and with long column names only, and did not give short or system names. I have discussed in an earlier post how to add the short/system names to the Table and columns when you are creating the table. But in this example the programmer who created just created the Table with the long names only:
01 DROP TABLE IF EXISTS MYLIB.THIS_IS_A_LONG_NAME ; 02 CREATE TABLE MYLIB.THIS_IS_A_LONG_NAME( 03 FIRST_LONG_FIELD_NAME CHAR(7), 04 SECOND_LONG_FIELD_NAME TIMESTAMP 05 ) ; |
Line 1: This deletes the Table if it already exists. A nice addition in the latest round of Technology Refreshes is the addition of the IF EXISTS, it prevents the DROP TABLE from generating an error if the Table does not exist.
I have been writing this blog for 7¾ years and during that time IBM has used the blue circular "i for business" logo for our favorite business operating system, IBM i.
Yesterday IBM launched a new logo for IBM i.
I was at a presentation by Allison Butterill, Offering Manager for IBM i, where she shared the latest IBM i and Power support roadmap.
I always like to share these to counter the nay-sayers claiming that the "AS400 is dead", AS400 has been dead for more than a decade but IBM i and POWER live on.
Looking at the chart, below, you will notice that the dark blue bars are followed on by a light blue bar. This signifies when a release goes off support, but support can be prolonged by purchasing extended support.
Someone asked me how to detect if any of their system commands have been changed. By "system commands" they meant any of the commands in the library QSYS. I can think of two ways to do this, and I will describe both below.
I have written before about how to retrieve the defaults of a command, so I will not repeat that here.
When I tested both methods in the IBM i partition I use when writing these posts I found that only one command was returned as changed, therefore, the results I am going to show will be limited to that one command.
I stumbled across this SQL function and thought it might be something I could use in the future, and that some of you might find it useful too. TRUNC_TIMESTAMP will truncate a timestamp to give another based on the type of truncation. For example if I wanted to truncate to the month it returns a new timestamp of the lowest value for the year and month.
It is not new, I can find it in the IBM KnowledgeCenter back to IBM i 7.1, it just surprises me that I have not discovered it before.
Rather than try to explain how this function works I think it is easier to show with examples. In these examples I am going to use the special register CURRENT_TIMESTAMP as the timestamp I will be truncating.
Having discussed how to make timestamp values from numbers, the next step in what I need to do was to determine the difference between the start and the end times. In this case there is a SQL function I can use: TIMESTAMPDIFF
The basic syntax of the statement is:
SELECT TIMESTAMPDIFF(< number >,CHAR(< end timestamp > - < start timestamp >)) FROM < file > |
The numbers you can use are for the following time intervals:
Let me jump straight to some examples. In this first I am only going to calculate the differences as time values, but not microseconds. Microseconds has such a small range they could not be calculated for the timestamps used in these statements.
I am sure that most of us work with ERP databases where the date and time fields are really numbers. Finding ways to convert these numbers into "real" dates and times allows us to make use of all the rich date and time features within the IBM i operating system.
I have described in a previous post how to convert various numeric versions of a date to a real date. So I won't bother to repeat that.
But what about converting numbers into a real time?
Before I start giving examples let me show the file that contains the data I will be using. Yes, I am using a file as in my experience if there are numbers pretending to be dates and times then they are in DDS files, rather than DDL Tables. The file contains four fields:
I was asked if there is an easy way in SQL to calculate the difference between two date fields.
I am sure there are many different ways to do this. In my example I have a file, TESTFILE, that contains a date field, WKDATE. I want to compare this date to today's date and produce a string that tells me the number of years, months, and days difference. Let me start off with the calculation of the difference:
01 SELECT WKDATE, 02 CURRENT_DATE AS "Curr date", 03 CURRENT_DATE - WKDATE AS "Diff" 04 FROM TESTFILE ; |
I am a big believer and supporter of IBM i Local User Groups, LUG, as they are always a great place to swap and share experiences, as well as learning from your peers. This website has a page dedicated to IBM i user groups, and you can use this to find whichever one is closest to you.
Once a year I go through this list and purge from it all of the groups that are no longer active, or their website is inactive. I also ask you if you know of any groups, anywhere in the world, who are not listed, changed their web address, Twitter and LinkedIn accounts that are not on the list please contact me via email, Facebook, LinkedIn, Twitter with their details. You will find all of my contact information on the About page, or you can use the Contact form on the right.
If you learn of any information about a LUG outside of this month please let me know, and if it is new or has a new website I will mention it on this blog.
Thank you in advance for helping to spread the word about LUGs.
This comes under the category of: I thought I had already written about this. The DOFOR command in CL allows me to construct a For group in CL, like I can in RPG when I use its FOR operation code. The same information has to be given to both:
RPG === for Count = 1 to 10 by 1 ; CLLE ==== DOFOR VAR(&COUNT) FROM(1) TO(10) BY(1) |
By now you should be aware of my liking and promotion of IBM i local user groups, LUGs. I am proud to announce that a new LUG, that I am involved with, has its first meeting next month.
The Central Texas IBM i User Group, CTXiUG, will have its inaugural meeting on Tuesday February 9, 2021, at 6:30 PM CT (click here to see what time that is in other places).
Exit points are a feature of the IBM i operating system. When certain processes are run an Exit point is where an Exit program can be called. After the Exit program has completed the process continues. You can create your own Exit programs, and insert them into these Exit points. Even though Exit points and programs have been around forever many people do not use them, nor do they monitor them. As they can capture system information, their misuse must be considered a security breach.
In this post I am not going to describe how to add or make changes to Exit points and programs. I am going to give a simple example how they can be monitored. Notifying me when they have been changed.
This work includes two new SQL Views that came as part of the latest Technology Refresh, IBM i 7.4 TR3 and 7.3 TR9:
One of the many additions to SQL in the latest round of Technology Refreshes, IBM i 7.3 TR3 and 7.3 TR9, is a Table function that displays a job's open files.
I can see the same information using the Display Job command, DSPJOB, option 14. But there are times I might want to access the same information in a manner that I can get to easily in a program.
The Open Files table function, OPEN_FILES, is found in the QSYS2 library. It has only one parameter, the job name. If you want to retrieve the information for the current job you can use "*" rather than give the full job. Retrieving the current job's name requires minimal effort as it is a "built in" global variable, JOB_NAME.
The following SQL statements would return the results for current job:
SQL's DROP statement is pretty universal. It is used to delete just about anything that can be created in SQL: tables, indexes, views, procedures, etc. As with all delete statements there will be an issue if you try to delete an object that does not exist. For example if I drop a table:
DROP TABLE QTEMP.DOES_NOT_EXIST |
I will get an error message. This one is from ACS's "Run SQL Scripts":
SQL State: 42704 Vendor Code: -204 Message: [SQL0204] DOES_NOT_EXIST in QTEMP type *FILE not found. |
While this is not a big deal in "Run SQL Scripts" as I can just ignore the message, it can be a significant issue if this situation is encountered in a program.
Fortunately this issue has been overcome in the latest Technology Refreshes released last week, IBM i 7.4 TR3 and 7.3 TR9, with the ability to add the words "IF EXISTS" to the Drop statement. For example if I use the following statement in "Run SQL Scripts":
I am often asked how is it possible to find the IBM i release and Technology Refresh level on a partition. In previous posts I have given ways to display the release, which PTFs are loaded, and the partition name. So I thought it was time I rolled this all into one SQL statement, from which I would get:
I can hear a number of you say: "Isn't the partition and database name the same?"
With the vast majority of IBM i and earlier models I have used when the partition was installed the database name was changed to be the same as the partition name. But there are occasions they are not, or it was given a different name. One of the IBM i partitions I use to write these posts has a different database name, and so do two of the four partitions at my work. This post will use RZKH's DEV730, which is the one where the partition and database names are different.
All the celebrations to welcome in the New Year are drawing to a close, and we say farewell to an annus horribilis. I am sure I am not the only person wishing for 2021 to be the year where we can overcome this virus, and return to living more normal lives.
Despite the lock downs, social distancing, and working from home IBM has still brought us two Technology Refreshes for the supported releases of IBM i, 7.4 and 7.3. They have done a great job bringing many new features and functions. You can read about them here on this web site.
This brings me to the five most popular posts of 2020. These are the ones you thought were the most interesting:
