There have been times when I am debugging a multi-thousand line program when I find a line of code that can look something like:
1414.00 dou (X = Const1) ; |
I can see what value is in variable X. But my attempts to see what is in Const1 is met with:
EVAL Const1 Identifier does not exist. |
I use data structure arrays in many of my RPG programs. It has always been impossible to sort the data in the data structure array by more than subfield, that is until the latest Technology Refreshes, IBM i 7.4 TR5 and 7.3 TR11.
Included within the new TRs is a Built in Function, BiF, for RPG's Sort Array operation code, SORTA, that allows me to sort by more than one data structure array subfield. The syntax for using %FIELDS with a data structure array is:
It has been a busy couple of weeks in the IBM i world. We have had the announcement of the IBM Power 10 chips, new E1080 server, and new Technology Refreshes. On Friday all of these were joined by the announcement of an updated version of Access Client Solutions, ACS.
The new Power 10 chips and server, E1080, were announced last week and it should come as no surprise that IBM is releasing updates to their documentation.
This document, IBM Power Performance Capabilities Reference, has been updated with the CPW values for various models of the E1080 server running with the IBM i 7.4 operating system.
It also includes the same information for all of the Power 10, 9, and 8 servers going back to the Power 824 running IBM i 7.2, back in April 2014.
I won't ruin the "plot" by giving you examples of how fast the new E1080 is, the CPW numbers are impressive. You will have to check out the document for yourself to discover them.
You can download the PDF here.
The latest round of Technology Refreshes, IBM i 7.4 TR5 and 7.3 TR11, brought in three new additions to the RPG language. I thought I would start with two new Built in Functions, BiFs, that return the greatest or lowest value from an array.
The two new BiFs, %MAXARRAY and %MINARRAY, have the same syntax:
In what will be probably be the biggest announcement in the IBM i world this year IBM has announced their first server using the Power 10 chip, the E1080. I am not really a hardware guy, but I do appreciate the improved performance that the new Power 10 chip gives.
The ERP vendor SAP has developed their own SAP Application Performance Standard, SAPS, which they use to compare servers to one another. Their benchmark tests for the IBM Power 10 show that it outperforms Dell EMC PowerEdge with the Intel Xeon Platinum 8380 processor, SAPS 955.050 versus 248.530.
Even in a cloud environment 8 sockets, 160 cores of the IBM E1080 outperforms 16 sockets, 448 cores Google Cloud and HPE Superdome Flex.
To accompany the announcement of the IBM Power 10 launch we also have the announcement of the Fall 2021 Technology Refreshes, TR, for the two supported releases of the IBM i operating system, 7.3 and 7.4 . These TRs are necessary if you want to move to the new Power 10 E1080 server.
Comparing the page for IBM i 7.3 TR11 and 7.4 TR5 they have the same additions and changes:
With the Power 10 announcement happening tomorrow I received a link to a sneak peek from IBM, which you can watch here.
Look for the "orbs" and click on them as they will reveal more information.
If you have not registered for the announcement tomorrow, Wednesday September 8, click on this link to do so.
It has been a long time since I last wrote a post about display files, but I thought this was worth sharing. I was asked how you could determine which field the cursor is in when the prompt key is pressed. The prompt key is the SAA standard F4 key.
The person had found a very old program where the position of the cursor was returned as its location on the screen, as the row and column numbers. If this method was use they would have to determine the location of each field in the display file's record format.
I was asked is there an easier way?
Next week will be the biggest announcement from IBM this year, the next generation of IBM Power.
You can be there and learn about IBM Power 10 from the experts by attending this virtual announcement by signing up here.
Date: Wednesday September 8, 2021
Time: 10:30 AM (US Eastern time)
Duration: 45 minutes
Back in IBM i release 7.2 the SYSDISKSTAT SQL View was added. It was introduced to give similar information to what can be see using the Work with Disk Status command, WRKDSKSTS, and allow us to do more with that information. Over the next couple of releases there have been new columns added to it making it ever more useful. I have to admit I have been remiss not writing about it until now.
In the latest Technology Refreshes, IBM i 7.4
With this statement I am going to get a snapshot of the which disks are the top five when it comes to the percent of the disk is used:
I have to admit when I read that a SQL table function to change user profiles had been introduced in the latest Technology Refreshes for IBM i 7.4 and 7.3 I was skeptical as to whether it was something that I would find practical and useful. Having had some time to play with it I can now appreciate its usefulness.
But before I get to that, let me explain how the CHANGE_USER_PROFILE table function works.
The table function will only change certain information of a user profile. The parameters you can change are:
I don't have a good example of using a Common Table Expression, CTE, in this blog so I decided to write this one as an example of how I can determine the maximum length of 80% of the rows in a SQL Table.
It does sound a bizarre thing to calculate, but it was used in the post from last week where I discussed the fastest way to copy data from one file or table to another. IBM's recommendation was if you use a VARCHAR to define a column in a table using the ALLOCATE it could reduce the number of I/O operations that would be performed. The suggested allocation for any column is that the number of bytes allocated will include 80% of the rows in the table.
I decided to write the SQL statement I will show, below, to perform that calculation. I broke up what I wanted to do into "parts", this would allow me to test my results before going to the next "part".
I just want to show the latest roadmap for IBM i. It shows how IBM has plans for the operating system we love beyond 2031 with the next two planned releases.
It also shows the extended support for releases 7.1 and 7.2, identified as SE1 for 7.1 and SE for 7.2 .
If you are using 7.1 or 7.2 I would seriously consider moving to the latest release supported by your Power server. There have been a lot of security upgrades in later releases, not having these could leave your IBM i partitions vulnerable to hacking or malware attacks.
A couple of days ago I published a post about testing a friend had performed looking at the fastest way to copy data from one table to another. I thought his results were really interesting, which is why I shared them.
But there are another couple of ways I would have tested. In this post I will describe them.
With all the example programs I am not displaying the entire program, just the interesting parts.
The tests were performed over the same SQL tables as before:
This post is based on the presentation Thomas Leo Swint made to the Central Texas IBM i User Group last night. Thomas performed a series of tests looking to find the fastest methods to "read" several million rows of data, and also to "read" the data from one table and output it to another table. When he shared his results with me, I was surprised what he had discovered. I asked him to make a presentation to the CTXiUG, and for his permission to share his results here.
Thomas needed to copy millions of rows of data from one set of SQL tables to another. This process needed to take the least amount of time possible. He created four test tables with the character columns defined in different ways. He compared the time taken to "copy" rows from the tables to another table using:
This question came from a colleague of mine. He was trying to identify which records from a DDS file contains one of several special characters within a particular field. To make it a bit more interesting these special characters could be in any position of the field, maybe the first position, last or in the middle. He had tried several SQL statements but had not managed to create one he felt comfortable with.
I am sure you can understand why I cannot share the file he was using. I have created my own file to illustrate the problem and my solution. I am sure you will not be surprised to find I called it TESTFILE.
To see the contents of the file I can use this simple SQL statement:
01 SELECT * FROM TESTFILE ; |
And the following results are returned:
The IFS_OBJECT_STATISTICS table function has always returned whether the object in the IFS is a symbolic link, *SYMLNK, but not where its links. As part of the last of the latest round of Technology Refreshes a new column has been added to the table function's results to give us that information.
I feel I need to explain what a symbolic link is before I give an example from IFS_OBJECT_STATISTICS.
Symbolic links are a special kind of object only found in the IFS. They are identified by their own object type, *SYMLNK. Think of them like a Windows shortcut. These files contain the path name to another file or location in your partition.
You create symbolic links with the Add Link command, ADDLNK:
This is going to be a short post about a new SQL View, USER_PROFILE_BASIC, that was released as part of the latest round of Technology Refreshes. As the name suggests this new View contains a subset of the columns found in the USER_INFO View.
By having less columns USER_INFO_BASIC fetches and calculates its results faster than USER_INFO.
The syntax for this statement is simple:
SELECT * FROM QSYS2.USER_INFO_BASIC ; |
The column names are the same in USER_INFO_BASIC as they are in USER_INFO.
Introduced as part of the latest Technology Refreshes for IBM i 7.4 and 7.3 is a SQL View which lists all of the security information for the partition I am using.
The View SECURITY_INFO only has one row, and shows the same information as the Display Security Attributes, DSPSECA, and Display Security Auditing commands, DSPSECAUD. Many of the columns in this View are taken from the security system values.
To be able to see the contents of the audit receiver columns I must have Object operation authority, *OBJAOPR, to the Audit journal, QSYS/QAUDJRN, or to the attached journal receiver.
In its simplest form to see all of the security I can just use the following statement:
SELECT * FROM QSYS2.SECURITY_INFO ; |
A small enhancement to the Object Statistics table function in the latest round of Technology Refreshes is going to make it easier for me to get the results I want. Previously if I wanted to get the results for a group of objects I would have to ask the table function for a list of all the objects in the library, and then limit the returned results only to the ones I want using a where clause.
SELECT OBJNAME,OBJTYPE,OBJATTRIBUTE
FROM TABLE(QSYS2.OBJECT_STATISTICS(
OBJECT_SCHEMA => 'MYLIB',
OBJTYPELIST => '*ALL',
OBJECT_NAME => '*ALL'))
WHERE OBJNAME LIKE 'TEST%'
ORDER BY OBJTYPE,OBJNAME ;
|
I am sure you can appreciate that this is not the most efficient way to get the results I want, a list of objects that start with the letters: TEST. I have had to return the results of all the objects in the library MYLIB, and then select from that list the rows I want, where the name start with TEST.
IBM has updated their branding for the Power range of servers and architecture. I could also say the processor has been rebranded too, but in my opinion POWER9 has been replaced by the newer, faster Power10 processor. They have designed what I think is a pretty cool logo for the Power10, that I have placed to the right of this text.
As part of the latest round of latest Technology Refreshes for IBM i 7.4 and 7.3 are four SQL table functions that allow me to be able to easily get the following information from the system audit journal:
The syntax for all of these table functions is the same:
The latest round of Technology Refreshes sees the addition of the same three columns to the JOB_INFO table function as were added to the ACTIVE_JOB_INFO table function.
These new columns break apart the full job name placing its parts into three new columns:
For example I can get the information about my job by leaving out the parameters for the table function:
In the past few Technology Refreshes there have been additions to one of my favorite Db2 for i table functions. I use the ACTIVE_JOB_INFO a lot, I have even created a program to display jobs in message wait in a subfile using it.
The first change I am going to show makes it easier to search for all jobs that have the same job name. Three new columns have been added, "breaking apart" the full job name into its individual parts:
SELECT JOB_NAME,JOB_NAME_SHORT,JOB_USER,JOB_NUMBER FROM TABLE(ACTIVE_JOB_INFO()) ; |
When anyone asks for information that they are going to "pull" from the IBM i into another database or to interface to another application on the IBM i I do not want them to access the "live" files for several reasons:
In my opinion the perfect tool for this is a SQL View. I can build it the way I want and tell them to use that.
The only way I have known how to write data to a User Space and read it has been by using APIs. With the last round of Technology Refreshes for IBM i 7.4 and 7.3 came an alternative to using an API to read.
There are two additions to SQL to help with User Space:
I am sure everyone who has programmed using IBM i has encountered QCMDEXC. Most of us have used the QCMDEXC API, or the QCMDEXC procedure in SQL to execute CL commands from another program or a SQL statement. Included within the latest round of Technology Refreshes for 7.4 and 7.3 is a QCMDEXC scalar function.
A scalar function has to be executed as part of a SQL statement, a Select for example, rather than called directly. Like all the other scalar functions I know, it will return "1" when it executed successfully, and "-1" when it did not.
I have to admit I had to think for a while of a situation I could use this in. Then I remembered one where this could be a perfect fit.
Many years ago I came across a couple of programs that were part of a month end job to release and hold job queues that were used by the month end. There was a file that contained a list of job queues, the library they are found in, and what was called the "flag" field. All the flag was used for if it was not "1" the job queue was not processed.
I have built a SQL table to mimic that file:
I have been working with a group of Windows programmers to interface data from an IBM i partition to a Microsoft SQL database. The biggest issue I had was the formats of the dates. These Windows programmers insisted I pass all the dates as character format including the slash character ( / ) as the separator, which is no big deal. What had us going around in circles was that the same date would need to be provided in different date formats depending on the part of the interface. As I am in the USA most of the time they wanted me to pass the date in MDY format (MM/DD/YY), sometimes in USA format (MM/DD/YYYY), and for a couple of dates I need to pass them in the European DMY format (DD/MM/YY).
The date was coming from a DDS physical file. To illustrate how I could simply provide the date in the formats they desired I have created a file, called TESTFILE:
01 A R TESTFILER 02 A TEST_DATE L 03 A TEST_STAMP Z |
I have written about executing dynamic generated SQL in a RPG program before, all of the examples were to do something and not to return results from those statements.
This post will show how you can build SQL Select statements in a variable, execute them, and get the results returned.
I need to give credit to Hassan Farooqi for providing me with the information to show a simple way to do this. The code is based upon examples he shared with me. His were far more complicated than what I am showing here, but I just want to show simple of examples of what you can do. You can make yours as complicated as you like.
All of the examples have three steps:
Prior to the latest round of Technology Refreshes for 7.4 and 7.3 if I wanted to get the data from a particular message queue I would need to use the MESSAGE_QUEUE_INFO SQL View. Being a View it contains all the data from all of the message queues, not just the one I would be interested. If I wanted to retrieve the messages from one message queue it would take a while for the results to be found and returned.
There is now a MESSAGE_QUEUE_INFO Table function. Table functions are faster than Views as they are sort of like an API, I pass the message queue name to the Table function and only that message queue is searched for the information I want.
The results returned by the View and Table function are almost identical. The only difference is that the View returns columns for the name of the message queue and the library it resides. As I pass that information to the Table function it is irrelevant to its results.
MESSAGE_QUEUE_INFO Table function has four parameters:
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.
