I have just learned that there will be a leap second before January 1, 2017. A leap second is an extra second added to a day to keep UTC (Coordinated Universal Time) synchronized with "mean solar time". The leap second is added to December 31, 2016, after 23:59:59 UTC, and before 00:00:00 UTC on January 1 2017.
| December 31, 2016 | 23:59:59 UTC |
| 23:59:60 UTC | |
| January 1, 2017 | 00:00:00 UTC |
The question is how does this effect IBM i?
Someone said to me that the reason that they will not use totally free RPG is they cannot call programs using it. In the earlier flavors of free format RPG they could just switch from free format to fixed to do the call, and return to free format afterwards.
Var1 = 'Hello' ; Var2 = 1 ; Var3 = '' ; C call 'OTHERPGM1' C parm Var1 C parm Var2 C parm Var3 if (Var3 = 'Y') ; |
With totally free RPG once the **FREE compiler directive is placed in the code it is not possible to go back to fixed format after that. Therefore, the approach used above is not possible.
The idea for this post came from a meeting of the programming managers at work. My programming nemesis, the "I can do everything you do in RPGIV just as well using RPGIII" guy, was bragging that no-one could write a better way of changing a date into words than his old RPG38 program.
"I can do what your program does in just a few lines of code," I explained. He disputed that I could, and the challenge was on.
His old program is passed a date, as a number, and returns:
Yesterday an updated version of the Tools and Solutions for Modernizing Your IBM i Applications Redpaper was released. This Redpaper, which was first published in 2014, describes the tools that are available from Independent Software Vendors, ISV, and Business Partners, BP, to modernize IBM i applications and databases.
It has sections about:
While I usually avoid mentioning ISV and BP software in this blog, sometimes it is interesting to see what is "out there".
You can download the latest version of this Redpaper, as a PDF, here.
I am sure I am not the only person who has received a message, email, or telephone call informing me that there was an error message that happened earlier. When I reply asking for more details it is not uncommon to be told: "I don't know what the error was, I just replied with a C". This, of course, tells me nothing useful to be able to make a diagnosis what and where the error happened. Often it drives me to the History log which I have to search through looking for an error message. Narrowing the possible time frame makes the searching easier, but if the best the message sender can tell me is "earlier this afternoon" or "during the day end run" I am left with a large amount of log data to search through.
IBM has come to my rescue with a DB2 of i (SQL) table function that allows me to search the History log and select the data I want to see. I am not sure if this is part of the IBM i 7.3 TR1 and 7.2 TR5 updates. You can search to see if it is present on your IBM i using the PTF information View, PTF_INFO, to look for relevant PTF:
Yesterday I came across a new IBM i user group with the wonderful name of MAGIC. I reached out to them for more information and received a wonderful reply from Laura Hamway. Her explanation of what the group is and what they want to achieve, was so good I am just going to include it here.
Mid-Atlantic Group of IBM i Collaborators (MAGIC) was formed to help IBM i Users in this region (VA, NC, DE, MD and PA) to network and to facilitate ongoing education. The group was formed to support this region but anyone can join. We have all the frame work done, now we are ready to get started in 2017!
As part of the IBM i 7.3 TR1 and 7.2 TR5 enhancements a new RPG operation code, ON-EXIT, was added. The purpose of this operation code is to give a section of code that is executed whenever a procedure ends, with or without an error. Alas, when an error occurs in the procedure it is not possible to "correct" or prevent the error being returned to the calling program or procedure using the ON-EXIT section. But it does allow for the programmer to add code to, perhaps, flag the error or to perform various clean up tasks before the procedure ends.
The ON-EXIT section must occur at the end of the procedure. It is started by the ON-EXIT operation code, and is ended with the END-PROC operation code that flags the end of the procedure.
When I posted pictures of the IBM print charts I had found, Kevin Adler sent me pictures of some of the RPG history he has, RPG coding sheets.
Looking closely at them I think they may have been sheets used in the days of punch cards.
When I asked Kevin how he came to possess them he replied: "I got them from a former team leader before he retired."
This post is based upon something I made for myself, and after discussing it with some friends they said it would be worthy of becoming a post as others have the same need.
All of the code shown on this blog is written on servers generously provided by the IBM i hosting provider RZKH. Their hosting package gives me a fixed amount of storage, disk space, which I can use. While I am a careful user, who deletes all objects when I have finished with them and regularly clears my output queue, I want an easy way to see the following:
A new draft of the IBM Redbook "Who Knew You Could Do That with RPG IV? Modern RPG for the Modern Programmer", completed October 20 2016, has been made available. I recognize many of the authors as people I respect as highly knowledgeable in the use of modern RPG.
The chapters cover many items which should interest anyone using, or wanting to learn, modern RPG:
When I wrote about creating a validation trigger in SQL several people asked me to give an example using RPG. Never being one to turn down a challenge this post is going to show how to do the same as my SQL example, but completely in RPG.
I am not going to go into the reasons why you might want to consider using validation triggers, as I gave my opinion in the previous post.
I started my previous example giving an example of a SQL DDL table, in this example I am going to use a DDS physical file. FILE1 has three fields:
I could not resist posting pictures of a find I made in a box at home, a pad of IBM 132/10/8 print charts. The numbers denote that the chart is 132 characters wide, with 10 characters per inch and 8 lines per inch. These were used to assist programmers designing reports, and make it easier to translate the desired layout to Output specifications.
My wife had acquired the pad of charts in a previous job many years ago, and had put it in a "safe place" in case she needed them in the future. The charts had been stuffed into box of other old computer items during one of our moves, only to be rediscovered yesterday during one of those "I wonder what's in that box?" moments.
I do get many requests asking for whole example programs, especially ones with a subfile, rather than just bits and pieces of code that I usually give in these examples. With this in mind this post brings a number of things I have written about before together into one program, which I thought would be an interesting refresher. So here goes...
I was asked to write a program to only show jobs that are in message wait status, and I want to add the ability give the user some options that would allow the user to analyze the error and reply to it.
To summarize what I need to do:
I was assisting a colleague creating a SQL View when I encountered something I had not thought of before. He wanted to create a view with a derived column based on a column in the "right" table of a LEFT OUTER JOIN. Everything looked fine until the LEFT OUTER JOIN did not find a row in the "right" table. We could easily stop a null appearing for the columns from the "right" table, but what about the derived column?
Null is a concept that many people struggle with. I often have had to explain what null is and why it is different from blank or zero to older RPG programmers, who always have a confused or dismissive look in their faces. Users just don't understand the concept. This is why when I create output I always put some value in the column that comes up null.
Triggers are a versatile database tool. A few weeks ago I wrote about using triggers to write data to an output file. I can also use triggers to validate data before it is written to the file or table. By placing the business rules and validation processes into the database with a trigger guarantees that:
Null is a concept that RPG programmers are going to have to learn. Generations of RPG programmers have used a blank or zero in a variable to denote that it is not used or its value is not given. Null, in the simplest terms, means nothing, and if there is not a value for variable then null would be the ideal value to give it. If I had used a blank instead how am I to know, later, if blank is a valid value or is it being used to denote there is no value?
While null values can be found DDS files and SQL tables, it was not until IBM i 7.3 that I could code variables within my RPG programs that could be null.
In my previous post, Creating User Defined Functions to make my SQL statements easier, I gave example of how to create SQL User Defined Functions, UDF, using RPG. In this post I am going to show how I can create an UDF made up entirely of SQL.
I have created this UDF to concatenate the parts of the employee's name together. My example Employee Master file, EMPMST, has the following fields:
A R EMPMSTR
A LASTNME 30A
A FIRSTNME 20A
A MIDINITL 1A
|
When I look in the file I see:
The old ERP application I have to work with holds the "dates" in its files as seven long packed numeric fields, called *CYMD format. When I extract data from these files using SQL I always want to convert these "dates" to real dates, which I can the use for calculations, etc. I am tired of using the following SQL code to convert these fields to dates:
SELECT DATE(TIMESTAMP_FORMAT(CHAR(CYMD_DATE + 19000000),'YYYYMMDD')) FROM TESTFILE |
And if the value in the numeric field is not compatible with a date the Select statement fails, and I have to fix the data with a best guess, and try again. It would just be so much easier if I could make the equivalent of a subprocedure to do this for me, then all I would have to do is call the "subprocedure" with the value to convert and it would return a valid date.
While it was not part of the fanfare for the release of IBM i 7.3, there were some enhancements made to RPG. Two of the changes were made to the Scan built in function (BiF):
Let me jump straight into my code example so I can explain what differences these additions make. First I need the definitions for my example program:
(Updated Tuesday October 11 at 6:00 AM)
Technical Updates for IBM i 7.3, TR1, and 7.2, TR5, have just been announced on IBM’s developerWorks and Software announcement web sites.
There is lots of additions and enhancement to SQL, including a new JSON_TABLE table function that is causing quite a buzz on social media.
Even RPG has a new addition, the operation code ON-EXIT. This will force a section of code to run when something unexpected happen. There is no easy link to find it in the Software announcement pages, you will have to use your browser’s search function and search for "ON-EXIT".
Both of the updates will be available on Friday November 11, 2016.
You can learn more from these links:
Long time readers of this site know my position on User Groups, I love them. I was excited to hear that a new one is holding its inaugural meeting this week.
IBM i Southampton will be holding its first meeting on Thursday, in the city of the same name, Southampton, in the south of the UK. When I learned of this I contacted Liam Allan, who has been working to get this started, to learn more. Liam is someone I am sure we will all be hearing a lot from in the future. This young fellow is a pioneer in working with open source languages and utilities, and with the IBM i. He is a Common Education Foundation winner. And you might have seen him one of one the events he has spoken at in the USA, UK, and Sweden.
I took the opportunity to ask him a few questions on his goals for the new group.
It was during a COMMON presentation discussing the new features of IBM i 7.3 that Steve Will, chief architect of this operating system, mentioned the reason for this release was to accommodate the new Temporal tables. These new tables promise ability to see data within SQL DDL tables as it was in the past. In fact multiple users could use the same table at the same time and see the data as it was at different times, all while the table still be updated with new data.
Have been involved in projects to recreate a "snap shot" of data for a file as it was at a specific time, I can say that it is a lengthy process to roll back changes using data from journals or trigger output files. After those projects I welcome the usefulness of Temporal tables.
Once in a while I find a program in one the libraries I am responsible for that is missing the source it was compiled from. Looking at the object description I can see that the program was compiled from a source member in the right source file, but the member is not there. Other times a program was compiled from a member in a source file or library that no longer exists, but I can find a member of that name elsewhere, and I do not know if this source is the same that was used to create the program. Both scenarios should not happen in a perfect world, but most of us work in places that are not perfect.
I know there are programs various people have written to retrieve the source from a program, but I do not have time to do that today. I need to get to the source quickly to find out what this program is suppose to do.
There was a good question posted as a comment on the The different flavors of free format RPG post asking:
How do you move a array to field in free format. I did field1= arr1
In fixed format RPG I could use the Move Array operation code, MOVEA, to move data to and from arrays.
In a previous post I wrote how to create a simple trigger in RPG. Comments made upon that post suggested I could also create a trigger using SQL, using a lot less code than the RPG equivalent.
Whether I use a RPG trigger program or a SQL trigger the same logic applies. The trigger is attached to a file, and every time a database operation is performed to the file the logic within the trigger is performed. I am going to apply my SQL trigger to a file called TESTFILE (I know I get no marks for originality with object names) and whenever an insert, delete, or update performed I will output to my trigger output file, TRGOUTFILE.
There is a difference in the Trigger output file compared to the one I used with the RPG trigger program. In SQL I can retrieve the entire job name (job number/user/job name) in one value, so I decided, for speed purposes, not to split it into its constituent parts. Therefore, my output file looks like:
Several people have asked if I would give the source code for the program I wrote to generate the Trigger Output file I mentioned in my earlier post about writing a Trigger program. When I looked at the source code I was a bit embarrassed. By the looks of it I had written it as one of my first free form RPG programs when it was released, 2001, and I used the output file from the Display Field File Description command, DSPFFD. This request gave me an excuse to rewrite it using modern RPG.
The purpose of the program is to build a Trigger Output file. When the trigger, I talked about in my prior post, executes I want to write the before and after images of the data to an output file. Therefore, the output file must contain every field that is found in the original file and I always include these extra fields:
IBM has replaced their New to IBM i page in developerWorks with a new page called Getting started with IBM i. In it the author, John Eberhard, does a great job introducing the IBM i operating system, its features, and providing links to other IBM pages for more details.
If you are truly new to the IBM i then it is a must read. If, like me, you have been using it for years it is still a good read. And if you have the misfortune of having one of those "The AS400 is dead!" people around send them the link.
For future reference I have added a link to it in the Links to useful sites, on the right, of this blog.
In July I wrote about running SQL statements on remote IBM i systems using Query Management queries, and almost all of the comments upon that post mention using the three part qualified name to achieve the same result.
The three part qualified name, which I believe came out as part of IBM i 7.1 or one of its Technology Refreshes (TRs), consists the following elements, separated by a period:
database.schema.table |
Anonymous asked me the following question regarding the Send SMTP Email command, SNDSMTPEMM:
If multiple email id's are given in SNDSMTPEMM using a parameter, it does not work as the parameter is in CHAR type and it automatically uses quotes when passed as a parameter.
Any ideas how to resolve it?
As there is a lack of information available about this command I thought rather than answer the comment on the original post, Email IFS files, I would create new post as I am sure that Anonymous is not the only person to have encountered this.
The germ for this post harks back to an earlier one I wrote about how to extract data from a journal about changes made to a file, see Extracting data from journals. If you only want to track what changes were made to a file, perhaps a journal is not the way to go due to the size it will become. To that end a Trigger maybe a better approach.
In very simple terms a Trigger is a program that is attached to a file. No matter how the file is used, whether by a program, DFU, ODBC, etc, the Trigger executes. This means it is a great tool for controlling access to the data, validating data before it is written or updated to the file, and recording what data has changed. It pushes this kind of processing out of programs down into the file itself. It also ensures that no-one can circumvent the rules within the Trigger.
In this post I am going to demonstrate a simple Trigger program, in RPG, that records all the adds, changes, and deletes made to an output file.
Previously I wrote about how to extract data from a Journal using the Display Journal command, DSPJRN. There is an alternative approach with SQL using a User Defined Table Function, UDTF, called DISPLAY_JOURNAL. When I use a SQL Select statement this will return the same information as the output I was using from the DSPJRN command. Unlike the output from the DSPJRN command that comes in four different formats, the output from DISPLAY_JOURNAL has to be all things to all men, and contains more information than I need. You can see all the available columns in the IBM documentation that I have provide a link to at the bottom of this post.
I am just going to do the same as I did in the previous post, I want to get the changes made to a file on a specific date. As this is really a continuation of the previous post I am not going to repeat a lot of what I talked about there, if you feel I have missed something then check in that post.
The information I want is:
Many of the posts in the blog come from questions asked by its readers, this is another example. I received a message asking if I would explain how to extract data about changes made to a file from a journal.
Journaling on files, simply put, is a record of all changes made to one or more files. This information can be used in commitment control operations, allowing for changes to be rolled back, or for replicating changes to a disaster recovery replica of the production system. As the files the questioner asked about are being journaled we can get a copy of the changes made to a file.
How can you tell if a file is being journaled?
I am sure there are some who are going to criticize me for saying this, but I do use Query. While I do not use it for "production" reports, I do use it is a quick and easy way to check data, or even to generate a quick "one off" output file. Yes, I know I could use SQL to do the same, but sometimes it is just, in my opinion, quicker and easier to use Query.
One of the drawbacks of using Query are the Column Headings it produces for outfiles. If for example I join a file to itself and/or "define a result field" the Column Heading produced is unclear or outright confusing. Even if I go into the option to "Specify report column formatting" and change the Column Headings, I still get the original column headings in the outfile.
I could email myself/download the file and change the column headings in Microsoft Excel, but there must be an easy way to do it? Fortunately there is using SQL.
I spent the end of last week assisting my colleagues with a project that involved creating CL programs, and over the weekend I was asked to check someone's CL code. In both cases I was disappointed to find that while these individuals have modernized their RPG skills, their CL skills are still date back to the 20th century.
In my opinion CL is a programming language which is undervalued in IBM i circles. I have met many developers who would rather use a complicated API in their RPG code, rather than use a simple CL command. It is time for everyone to look at all the tools that are available in IBM i, rather than limit themselves to just be a "RPG programmer".
Here are some of the things I think we should all do with our CL code.
New columns added to the SYSTEM_VALUE_INFO View. Read about the enhancements here.
I am sure we have all had to retrieve a System Value. I would use the Retrieve System Value command, RTVSYSVAL, to do so. In one of the Technology Refreshes to IBM i 7.1 a SQL View, SYSTEM_VALUE_INFO, was introduced to allow us to access the same information using a simple SQL Select statement.
For those of you who worry that this might allow people to change System Values using SQL, rest assured that as this is a View it cannot be used to change data.
SYSTEM_VALUE_INFO contains three columns/fields:
The germ for this post came from a question I was asked by a reader of this blog:
In SQLRPG do I have to define cursor, open cursor, fetch, close cursor if I only want one record?
That is a lot of work just to retrieve one record. Fortunately there is a very simple method where I can retrieve a field, multiple fields, the entire record, or join to another record, etc providing I am just returning one record/row.
When I wrote about using DEFAULT in SQL statements it made me think if there is an equivalent in RPG. The Clear operation code sprung to mind. CLEAR has been around for many releases, including in RPGIII. It initializes the value of a variable, field, data structure, array, record format, etc. to the data type's default value.
I use CLEAR extensively because…
In an earlier post I wrote about prompting commands in a CL program, and allow you to change some of the command's parameters. But I did not give the logic on how to cope if the F3, Exit, or F12, Cancel, keys were pressed.
How to differentiate between the F3 and F12 keys being pressed was a subject of a discussion in one of the Facebook groups I am a member of. As I should have mention this in my earlier post I thought I would give an example of how I would do this.
The person asking the question was prompting the Display User Profile command, DSPUSRPRF, and need to:
I work in an environment with many different IBM i systems. I use the word "systems" as some of these are partitions on the same PowerSystems server, while others reside on different servers. Often there is a need to extract information from one system, and send it to another where it will be used.
I have previously written about using DDM files to get information from other systems. But as I use SQL more I find they are limiting, as I cannot extract information from a DDM file using SQL statements. If only there was a way to extract data from one system by using a SQL Select statement issued from a second system.
After searching the new KnowledgeCenter I discovered a way!
Regular readers of this blog know that I like to keep things simple and easy. Partly for my own peace of mind, and partly if it is simple then other programmers can quickly understand what my code does. A colleague calls this the K.I.S.S. methodology, which stands for "Keep It Simple Stupid", although she often changes the last "S" to "Simon" (I am not sure what she is trying to imply by this).
A good example is when using SQL to insert a row/record into a table/file. If not all the columns/fields need to be filled I need to give them a default value. This means that I need to look at the table and determine the data type of each column and whether it can be null or not, which takes time and can leave to errors if I try and accidentally insert a columns/field with the wrong default value.
Before using what is described below consider using SQL to write to IFS files.
After writing about how to read an IFS file in a RPG program I received a message from Domenico asking me how to do the opposite.
Very interesting article, as always, the only suggestion is: since, surfing internet, it is possible to find many examples about using _c_ifs_open but not one (!!) of _c_ifs_write .. it would be very very interesting and useful for our community.
Before I get started explaining how to write to an IFS file using RPG, let me say I am not going to repeat what I said in my earlier post A better way to read a file in the IFS with RPG, so you might want to review that before continuing with this post.
Today is the 28th anniversary of the launch of the AS400.
You can watch a video of the launch announcement here.
Tonight I will be raising a glass of my favorite beverage to wish the AS400 and its descendents, up to the IBM i, a happy birthday!
You can also learn what your PC would have been like at the time of the launch here.
And the family tree that took the AS400 and evolved to became the IBM i here.
I received a question from a reader asking if there was a way to copy data from a central IBM i to a number of remote IBM i using SNADS, and then have the central server call a program on the remote ones. Fortunately this is easy to do just using a few simple commands and a bit of common sense.
Distributed Data Management (DDM) files have been available on IBM computers for as many years as I can remember. DDM files are a quick and easy way to copy data to and from one IBM computer to another. Most of my experience with them has between AS400 and later IBM i, but I have also worked receiving data from an IBM mainframe via DDM files.
I received a call to action from my superior. "The Order Close Date in the Order History file has been messed up. Can you write a program to fix it?"
"When do you want this for?" I replied.
"Next week" he shrugged.
I have more than enough time, four days, to write a RPG program, but where is the fun in that when I could do something with SQL? Especially when this is something I have never done before: compare two values and use the greater to update another value.
It is a surprisingly common misconception that you cannot debug a program that runs in batch. While you have to perform some extra steps, which identify the job you want to debug within, the debugging interface is identical to that of a interactive debug.
To be able to run source debug, whether interactive or batch, you need to create (compile) your CLLE, RPGLE, and SQLRPGLE objects with the appropriate debug view parameter. To learn about the various debug views see the post Debug views finding your favorite.
The new IBM's KnowledgeCenter is now online, and after a few minutes of using it I have to say that it is an improvement on the old. It has addressed both of the issues I had with the old version: web site speed and ease of search.
This morning when I went into IBM's KnowledgeCenter web site a pop up message, below, informing me that a "brand new" version of the site will be "live" on May 29.
Being a curious fellow I copied the URL to a new browser window to see what is coming in the new site (come on IBM this should have been a link, not just text). You don't have to copy that URL as you can just click here to reach the same destination.
The link goes to a blog post by James Roberts where he explains some of the improvements we will see:
How many of us have considered the different types of debug views that are available with the create (compile) program and module commands? We may have customized the create commands when installing a new release or, if we are using PDM, created our own customized PDM option for the create command. After that we get lazy, and this includes me, and we just leave the parameters the way they are without bothering to check what the various options do. By doing this we miss out on some very useful features that would make our use of debug easier.
In this post I am just going to cover the following create commands and what the different debug views show:
I am sure that I have confused readers with the names I call the different flavors of free format RPG. I thought it would be best to have a post where I describe each flavor, and then refer the reader to this post from others when I need to explain the differences.
Before I start I just want to explain that these are the names I call the different flavors. These might not be (probably definitely not) the names that IBM would call them, but I do call them this.
In my opinion there are three flavors of free format RPG:
The end of the usefulness RPG's Output specifications, O-specs, was signaled by IBM when they did not give a free format equivalent when they gave us "free format definition" RPG, IBM i 7.1 TR7 in November 2013. If you start programming in "fully free" RPG, 7.2 TR1 and 7.1 TR11 November 2014, you will find that you cannot easily use non-free format code, such as O-specs. So this is the time to move away from O-specs and embrace printer files.
I am writing this post in response to a couple of people who contacted me asking about the differences in coding printer files, to O-specs, and how to write the RPG to use them. I know that there are tools to convert O-specs to externally described printer files, and I have used a couple myself. But this post is going to assume that this is a "vanilla" configuration of IBM i with none of those tools present.