Simple trigger to save changed data

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.

Extracting data from journals using SQL

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:

Extracting data from journals

Addendum

Rather than use the DSPJRN command you should consider using the DISPLAY_JOURNAL SQL table function.

---

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?

Changing column headings in output file

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.

Modernize your CL too

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.

Retrieving System Values using SQL

Addendum (March 6, 2024)

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:

Use Select to retrieve one record of data

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.

The versatile Clear operation code

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…

How to monitor for function keys when prompting a command

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:

Run a SQL statement on another IBM i

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!

Easy way to initialize columns when using SQL insert

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.

Write to a file in the IFS using RPG

 

Update

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.