Wednesday, September 28, 2016

Quickly retrieving the source for a program

retrieve source of rpg program

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.

Monday, September 26, 2016

The equivalent of MOVEA in all free RPG

how to movea in new rpg

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.

Wednesday, September 21, 2016

Simple SQL trigger

create a trigger using sql

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:

Wednesday, September 14, 2016

Program to make Trigger Output file

sql qsys2 qcmdexc trigger

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:

Monday, September 12, 2016

New IBM page: Getting started with IBM i

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.

Wednesday, September 7, 2016

Run a SQL statement on another IBM i, part 2

sql to remote database using three party qualified name

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

Tuesday, September 6, 2016

Multiple email addresses in SNDSMTPEMM

sndsmtpemm multiple email addresses

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.

Wednesday, August 31, 2016

Simple trigger to save changed data

using triggers in rpg

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.

Wednesday, August 24, 2016

Extracting data from journals using SQL

display_journal journal dspjrn

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:

Wednesday, August 17, 2016

Extracting data from journals

extract data from journal using dspjrn

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?