Overview
One tenet of database and application modernization is the practice of placing data validation, referential-integrity constraints, business rules, and other logic incidental to database updates into trigger programs.
One alternative to a trigger program is a service program that I refer to as a "database-event handler", which is depicted in the lower-right hand corner of the diagram here.
Database-event handlers export a set of procedures named as follows:
| on_init() | on_read() | on_write() |
| on_update() | on_delete() | on_term() |
| is_valid() |
Placing substantial portions of logic in a "database layer" was a big adjustment for me. It appears that the same may be true for most developers. The practice doesn't appear to be wide-spread. But the benefits are worth the effort.
It appears that the majority of business applications place such logic in an "application layer" rather than the "database layer". Database-event handlers offer the potential to change that.
The most efficient way to implement broadly-scoped business applications is to bind together sets of components that implement predefined interfaces - that follow patterns. This is similar to an assembly-line process that builds complex products from smaller sub-assemblies. Database-event handlers are a type of sub-assembly.
One of the nice things about this type of sub-assembly, is that the only prerequisite is the existence of a database. Database-event handlers can be written independently of user-interface and other application components. However their deployment may impact existing applications, which might require mitigation - such as partial or incremental rollout that includes event logging, monitoring, and testing.
If written in conjunction with other components, then database-event handlers might be written before all others. One exception to that advise might be if the database design is still in-flux.
I believe that the implementation of database-event handlers is the best way to ensure the validity and integrity of data, to prevent orphaned records in databases that define parent-child relationships, to implement a "single source of truth" (so to speak), in regard to data-validation and other types of rules.
It is a great way to prevent unwanted updates from unauthorized interfaces - to prevent intruders from making a mess of your data.
Step 1 - Generate Skeleton
The deployment of database-event handlers might be part of an initiative to move certain logic out of application layers and into the database layer. They might be part of new application design as delineated in this series of articles about Web application architecture. They might be deployed as one of the steps in database design.
In order to facilitate their wide-spread deployment, and to underscore the point about deploying them in connection with new database design, I created an IBM i command that generates ILE RPG source as a skeleton that can be fleshed out and compiled.
Generate DB Event Handler (GENDBEHLR)
Type choices, press Enter.
Handler Name . . . . . . . . . . frp001xb Name
Database File Name . . . . . . . frp001 File must be in *LIBL
Product Library . . . . . . . . avtsh Library for Handler Source
Bottom
F3=Exit F4=Prompt F5=Refresh F12=Cancel F13=How to use this display
F24=More keys
Pressing the <Enter> key on that command-prompt generates the following RPG source skeleton:
h copyright('2017 Relational Data, LLC') nomain
/copy *libl/qdbesrc,rddbehlr#1
/copy *libl/qdbesrc,rddbeinf#1
/copy *libl/qrpglesrc,rdmsgapi#1
//-----------------------------------------------------------------
// module level data
//-----------------------------------------------------------------
d dbeptr s * import
d rec e ds extname(frp001) qualified
d based(recptr)
//-----------------------------------------------------------------
// on initilization
//-----------------------------------------------------------------
p on_init b export
/free
msgFileOpen();
/end-free
p on_init e
//-----------------------------------------------------------------
// on read
//-----------------------------------------------------------------
p on_read b export
/free
recptr = %addr(dbe.after);
/end-free
p on_read e
//-----------------------------------------------------------------
// on write
//-----------------------------------------------------------------
p on_write b export
/free
recptr = %addr(dbe.after);
is_valid();
/end-free
p on_write e
//-----------------------------------------------------------------
// on update
//-----------------------------------------------------------------
p on_update b export
/free
recptr = %addr(dbe.after);
is_valid();
/end-free
p on_update e
//-----------------------------------------------------------------
// on delete
//-----------------------------------------------------------------
p on_delete b export
/free
recptr = %addr(dbe.before);
/end-free
p on_delete e
//-----------------------------------------------------------------
// clean up
//-----------------------------------------------------------------
p on_term b export
/free
*inlr = *on;
/end-free
p on_term e
//-----------------------------------------------------------------
// is the record valid?
//-----------------------------------------------------------------
p is_valid b
d is_valid pi n
/free
if rec = *blanks;
dbeMsgAdd(dbe_error : msgGetText('WLMAINT' : 1));
return *off;
endif;
return *on;
/end-free
p is_valid e
For those who are familiar with IBM's interface for external triggers, you might notice the absence of code pertaining to the calculation of address offsets for before and after images of the record.
You might also notice the absence of code performing branching based on the type of event that is being handled, which is common for external and SQL trigger programs. That type of logic, and more is automatically handled by plumbing that is external to the service program.
Notice the presence of initialization and clean-up procedures, which suggests the existence of a life-cycle. You have the ability to free resources (i.e. close files, etc.) after a period of inactivity.
Step 2 - Register DB Event Handler
The following command may be run in order to register a database-event handler and enable it:
Register DB Event Handler (REGDBEHLR)
Type choices, press Enter.
File . . . . . . . . . . . . . . frp001 Name
Library . . . . . . . . . . . . avtshdb Name
Handler . . . . . . . . . . . . frp001xb Name
Time . . . . . . . . . . . . . . B B=Before, A=After (DB Event)
On Read . . . . . . . . . . . . N Y=Yes, N=No
On Write . . . . . . . . . . . . Y Y=Yes, N=No
On Update . . . . . . . . . . . Y Y=Yes, N=No
On Delete . . . . . . . . . . . Y Y=Yes, N=No
Run Asynchronously . . . . . . . N Y=Yes, N=No
Enabled . . . . . . . . . . . . Y Y=Yes, N=No
Keep Alive Minutes . . . . . . . 30 1 - 9999
Bottom
F3=Exit F4=Prompt F5=Refresh F12=Cancel F13=How to use this display
F24=More keys
Pressing the <Enter> key on the command-prompt will add or update a registration entry in file DHLR100 in library RDDBSDB, and will add a generic physical file trigger named RDDBECON (depicted in the diagram) for each database event (read, write, update, delete) that has been flagged with a "Y".
Let's review the prompt parameters:
- File - the name of the database table (physical file) that the registration applies to.
- Library - the name of the physical-file library that the registration applies to.
- Handler - the name of the service program that implements DB event procedures.
- Time - whether DB event procedures will be invoked B=before or A=after DB record read, write, update, and delete events.
- On Read - whether to invoke the handler's on_read() procedure, where Y=Yes, N=No.
- On Write - whether to invoke the handler's on_write() procedure, where Y=Yes, N=No.
- On Update - whether to invoke the handler's on_update() procedure, where Y=Yes, N=No.
- On Delete - whether to invoke the handler's on_read() procedure, where Y=Yes, N=No.
- Run Asynchronously - whether the calling program should wait for the completion of DB event procedures or not. This flag is only valid for "after-event" registry entries and may be used to shield users from waiting for longer running procedures to complete.
- Enabled - whether the registration is currently enabled, where Y=Yes, N=No.
- Keep Alive Minutes - the number of minutes that database-event listeners will keep a handler "alive" after a period of inactivity, before the handler's on_term() procedure will be invoked.
Step 3 - Fill in Code
The type of code added to before-event handlers might include:
- Set default values on empty fields.
- Auto generate incrementing values.
- Mathematical calculations based on content in numeric fields.
- Transformations of values in character fields.
- Field-level encryption.
- Field-validation against business rules.
- Field-validation against referential-integrity constraints.
- Validate the source of the change (i.e. Application ID, Job Name, Program Name, User ID, etc. against authentication-authorization rules.
- Evaluate changes against environmental rules such as the state of other records, time of day, a date-period, completion of prerequisites, etc.
It would likely be a shame to duplicate such logic in the various languages and language environments that might be employed, that perform database updates. This type of logic belongs in the "database layer".
The type of code added to after-event handlers might include time consuming procedures such as generating reports or email messages or any other type of logic incidental to database updates. The use-cases are varied. Such logic can run asynchronously so that users don't have to wait.
The following shows just a small sampling of code added to an is_valid() procedure, which implements nine (9) referential-integrity constraints on a file:
aap030k.ct1tid = rec.fhot;
if not rlaExists('AAP030':aap030k);
dbeMsgAdd(dbe_error:msgGetText('WLMAINT':9:'Terminal') : 'FHOT');
return *off;
endif;
aap030k.ct1tid = rec.fhrt;
if not rlaExists('AAP030':aap030k);
dbeMsgAdd(dbe_error:msgGetText('WLMAINT':9:'Terminal') : 'FHRT');
return *off;
endif;
aap030k.ct1tid = rec.fhdt;
if not rlaExists('AAP030':aap030k);
dbeMsgAdd(dbe_error:msgGetText('WLMAINT':9:'Terminal') : 'FHDT');
return *off;
endif;
aap002k.tbdkey = 'FHTRM';
aap002k.tbdak1 = rec.fhtrm;
if not rlaExists('AAP002':aap002k);
dbeMsgAdd(dbe_error:msgGetText('WLMAINT':9:'Bill Term') : 'FHTRM');
return *off;
endif;
aap002k.tbdkey = 'FHSTAT';
aap002k.tbdak1 = rec.fhstat;
if not rlaExists('AAP002':aap002k);
dbeMsgAdd(dbe_error:msgGetText('WLMAINT':9:'Ctl Status') : 'FHSTAT');
return *off;
endif;
aap002k.tbdkey = 'FHSSUF';
aap002k.tbdak1 = rec.fhssuf;
if not rlaExists('AAP002':aap002k);
dbeMsgAdd(dbe_error:msgGetText('WLMAINT':9:'Suffix') : 'FHSSUF');
return *off;
endif;
aap002k.tbdkey = 'FHDACD';
aap002k.tbdak1 = rec.fhdacd;
if not rlaExists('AAP002':aap002k);
dbeMsgAdd(dbe_error:msgGetText('WLMAINT':9:'Reason') : 'FHDACD');
return *off;
endif;
aap002k.tbdkey = 'FHDC';
aap002k.tbdak1 = rec.fhdc;
if not rlaExists('AAP002':aap002k);
dbeMsgAdd(dbe_error:msgGetText('WLMAINT':9:'Code') : 'FHDAC');
return *off;
endif;
dsp001k.bbcode = rec.fhbbc;
if not rlaExists('DSP001':dsp001k);
dbeMsgAdd(dbe_error:msgGetText('WLMAINT':9:'Status') : 'FHBBC');
return *off;
endif;
- rlaExists() - is a generic utility that checks the existence of any record in any file based on the file name and key value (passed as parameters).
- msgGetText() - retrieves messages from a database file (an alternative to hard-coding messages in programs). Substitution text may be inserted into the message by the program.
- dbeMsgAdd() - returns messages to programs and service programs that may be active in the call stack. Parameters may include the type of message, the name of the column of concern, and a reference to additional help text, in the event that records fail to pass validation checks.
Database-Event Interface
Database-event handlers have access to quite a bit of information about the event, the Job that triggered the event, and other contextual information that may support the logic that may be included in the handler. Perhaps the best way to delineate this is via the following code that is included in the service program via the RDDBEINF#1 /copy member.
d dbeMsgClr pr extproc('dbeMsgClr')
d dbeMsgAdd pr extproc('dbeMsgAdd')
d type 1a const
d text 256a const varying options(*varsize)
d column 10a const options(*nopass)
d help 10s 0 const options(*nopass)
d rddbejob e ds extname(rddbejob) qualified
d rddbeevt e ds extname(rddbeevt) qualified
d rddbemsg e ds extname(rddbemsg) qualified
d en e ds extname(ptl100en) qualified
d dtaara(*lda)
d dbe_info s 1a inz('1')
d dbe_warn s 1a inz('2')
d dbe_error s 1a inz('3')
d dbe_write s 1a inz('1')
d dbe_delete s 1a inz('2')
d dbe_update s 1a inz('3')
d dbe_read s 1a inz('4')
d dbe_after s 1a inz('1')
d dbe_before s 1a inz('2')
d dbe ds based(dbeptr) qualified
d evt likeds(rddbeevt)
d job likeds(rddbejob)
d en likeds(en)
d pgm 10a
d before 32767a
d after 32767a
d beflen 10i 0
d aftlen 10i 0
d escape n
d msgcnt 3i 0
d msg likeds(rddbemsg) dim(30)
The "dbe" data structure in particular contains additional data structures that delineate various other data that accompanies each event. This nice packaging and accessibility of applicable data is one thing that distinguishes database-event handlers from SQL and external trigger interfaces in IBM i.
The external plumbing that accompanies database-event handlers reduces the amount of code that one might otherwise have to code in SQL and external trigger programs.
Error Handling
SQL and external trigger programs write messages to the program's call-stack message queue, which often entails the percolation messages up through preceding call-stack message queues.
I find that the use of call-stack message queues and the APIs that support them may be useful for generating Job logs, but are not really amenable to reporting application errors to end-users.
Database-event handlers invoke the dbeMsgAdd() function, which is exported from a service program named RDDBEINF, which other programs and service programs in the call stack may bind to, in order to receive messages.
This is more efficient, transparent, functional, reliable, less work, and less messy than interfaces and code that relies on call-stack message queues.
This type of error interface enables applications to position the cursor on and highlight columns of concern.
Database Event Listeners
Rather than invoking database-event procedures directly from trigger programs, our program uses a shared-memory interface with a pool of "database-event listeners" that are running out-of-process, which dynamically loads database-event handlers into memory and invokes their procedures.
This setup allows the database-event listeners to perform after-event procedures asynchronously, to log changes, to monitor database-event activity, to run initialization and cleanup procedures, without negative impact to the performance of applications that invoke database events.
Asynchronous processing also facilitates the use of code in after-events that might trigger another before-event on the same file.
IBM i Commands
A number of IBM i commands are available that support the database-event interface, including:
- STRDBESVR - launches a pool of database-event listeners for a configured environment.
- ENDDBESVR - ends a pool of database-event listeners for a configured environment.
- RCLDBESVR - recycles a pool of database-event listeners for a configured environment.
- GENDBEHLR - generates an ILE RPG skeleton based on a specified database table.
- REGDBEHLR - registers a database-event handler and adds relevant physical-file triggers.
- RMVDBEHLR - removes a database-event handler and associated physical-file triggers.
- SETDBESTS - updates database-event handler status (enabled, etc.).
Summary
I believe that the deployment of certain types of logic in the "database layer" is critical to future-proofing IBM i applications, ensuring the integrity of data, and preventing intruders or inattentive developers from making a mess of it.
Database-event handlers include a framework that significantly streamlines the programming interface, makes it easy to deploy them, and provides functionality that is helpful.
They are a valuable part of an overall Web application framework that can be used to develop robust, broadly-scoped applications.