Validation trigger

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:

1. The same business rules are used every time. If the rules change I just need to change the logic in the trigger, rather than find all of the programs that output to the file and change the logic within them.
2. It is not possible to circumvent the trigger. No matter which programming language or tool I use with the file the trigger will be executed. I will no longer have to worry about a rogue colleague changing data behind the scenes using something like DFU.

Using Null indicators with program's variables

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.

Creating User Defined Functions using only SQL

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:

Creating User Defined Functions to make my SQL statements easier

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.

Scan has a new parameter and a new BiF

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

1. Addition of a fourth parameter to the BiF for the length of the string to scan.
2. A new BiF Scan reverse, which the name suggests starts the scan at the end of the variable and moves towards the start.

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:

IBM i 7.3 TR 1 and 7.2 TR5 announced

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

IBM i Southampton – UK’s newest user group

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.

Looking back into the past of your data with Temporal Tables

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.

Quickly retrieving the source for a 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.

The equivalent of MOVEA in all free 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.

Simple SQL trigger

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:

Program to make Trigger Output file

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: