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?

Wednesday, August 10, 2016

Changing column headings in output file

using sql to change names of fields or columns in output file produced by query

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.

Tuesday, August 9, 2016

Modernize your CL too

modernize your cl code using these techniques

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.

Wednesday, August 3, 2016

Retrieving System Values using SQL

retrieve 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: