Pulling the (DB2) Trigger (A Solution Example)

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Pulling the (DB2) Trigger (A Solution Example)

Adsero Optima
Administrator
This post was updated on .
Author: Tommy Atkins, TEMBO Technology Labs

Abstract

This article is the follow-up to the original article on “Pulling the Trigger”. As promised, this article provides a set of code snippets and samples which have been documented in detail to provide an actual technical solution to the theoretical solution proposed in the first article.

Introduction

As it is considered as a “best practice” for each file in the database to have its own *BEFORE trigger program to manage all necessary data validations, it is necessary to customize each trigger program to match its associated file. For this reason the solution defined below is an example based on a pseudo FILEA and will need to be understood in its entirety and then customized to fit a specific file.

The snippets and samples in this document are from a “partially free” RPG IV (ILE) source, containing calculations in free-form and all other lines in fixed-form. This style is compatible with version 5 and upwards of the operating system and also with 8.5.1 and upwards of RDi.

A completely free version of this same code is also provided in the AO Websites Open Source section. This fully-free version is compatible with version 7.3 upwards and RDi 9.5.3 upwards.

AO Open Source Project

All source components making up this complete solution “template” can be downloaded from the AO Website using the link http://www.adsero-optima.com/open-source.html. This source is provided under the standard MIT license in support of the international open source initiative.

The File (FILEA)

     
  CREATE TABLE FILEA (
       STATUS CHAR(2)       NOT NULL DEFAULT,
       FIELD1 INTEGER       NOT NULL DEFAULT,
       FIELD2 CHAR(30)      NOT NULL DEFAULT,
       FIELD3 CHAR(1)       NOT NULL DEFAULT,
       FIELD4 SMALLINT      NOT NULL DEFAULT)
       RCDFMT FILEAR;
LABEL ON TABLE FILEA IS 'Dummy File for Trigger Template';
LABEL ON COLUMN FILEA (
	    STATUS IS 'R/S                 '      ,
	    FIELD1 IS 'Field l             '      ,
	    FIELD2 IS 'Field 2             '      ,
	    FIELD3 IS 'Fld                 3'     ,
	    FIELD4 IS 'Field 4             ')     ;
LABEL ON COLUMN FILEA (
	    STATUS TEXT IS 'Record Status       '      ,
	    FIELD1 TEXT IS 'Field l             '      ,
	    FIELD2 TEXT IS 'Field 2             '      ,
	    FIELD3 TEXT IS 'Field 3             '      ,
	    FIELD4 TEXT IS 'Field 4             ')     ;

This is the file on which the coding for this template will be based.

The Trigger Program

This trigger program should be attached to whichever *BEFORE events need some form of validation for the file. The “H” specs are required as this is to be an ILE program and must always be run from the *CALLER activation group.

 
   h dftactgrp(*no) actgrp(*caller) usrprf(*owner) aut(*use)
     h bnddir('AAADIR') option(*nodebugio) debug

In addition a binding directory specification is required as the program makes use of a number of procedures bound into service programs (*SRVPGM), which must be defined in the nominated binding directory.

Trigger Program Parameters

The relational database management system (RDBMS) provides each and every trigger program call, with exactly the same two parameters. The parameter interface for these parameters are defined in the trigger program as follows;

   
  d FILEA_B1        pr                  extpgm('FILEA_B1')
     d  P                                  likeds(P1) options(*varsize)
     d  PL                           10i 0 const
     d FILEA_B1        pi
     d  P                                  likeds(P1) options(*varsize)
     d  PL                           10i 0 const

The first parameter (P) is a variable length parameter as it is dependent on the file to which the trigger is attached. The second parameter (PL) indicates the length of “P” and is seldom needed in my experience.

To avoid defining “P” twice the LIKEDS keyword is used to point to a single definition (P1) of the parameter list. Click on the “Trigger Buffer” section of the following link to view a complete description of all the parameters provided by the RDBMS. http://publib.boulder.ibm.com/html/as400/v4r5/ic2979/info/db2/rbafomstrzahftrb.htm

   
  d P1              ds                  template qualified
     d  PFName                 1     10
     d  LibName               11     20
     d  MbrName               21     30
     d  Event                 31     31
     d  Time                  32     32
     d  CmtLock               33     33
     d  Resvd1                34     36
     d  CCSID                 37     40b 0
     d  RRN                   41     44b 0
     d  Resvd2                45     48
     d  OldOS                 49     52b 0
     d  OldLen                53     56b 0
     d  OldNMOS               57     60b 0
     d  OldNMLen              61     64b 0
     d  NewOS                 65     68b 0
     d  NewLen                69     72b 0
     d  NewNMOS               73     76b 0
     d  NewNMLen              77     80b 0
     d  Resvd3                81     96

Note: Defining a parameter list using the LIKEDS keyword automatically qualifies the individual elements in the structure, therefore referring to the “PFName” would not be correct. “P.PFName” is the correct way to refer to this parameter element.

Record Definitions

Contained in the parameter structure provided by the RDBMS are either one or two record images from the file that initiated the event. The original record image, in the case of an *UPDATE or *DELETE event, and the new record image, in the case of *INSERT or *DELETE.

The offsets to these images are in the fixed portion of the parameter list (first 95 bytes) and are based on the address of the first byte of the parameter list, in this case P1. The code which is used to setup these record overlays is described below.

Note that the data structures for the original (O) and new (N) records are based on pointers.

   
 d PP            s              *

     d OP            s              *
     d O             e ds                  extname(FILEA) based(OP) qualified
     d NP            s              *
     d N             e ds                  extname(FILEA) based(NP) qualified

Error Handling

The following section of code defines the prototypes for the 4 error handling procedures required by a *BEFORE trigger program. These “Error Handlers” are used by the trigger program to process and forward messages to the RDBMS as is required in order to notify the application of any problems.

These 4 “Error Handlers” are procedures defined inside various modules and then bound into a service program (*SRVPGM).br>
Full documentation and all required source code for these handlers is available from the “Open Source” section of the AO Website at the same link as provided earlier on in this article.

     
 * Move *DIAG and Re-Send *ESCAPE Messages
     d ERR03           pr
     d  MT                            1    const options(*nopass)
      * Move *DIAG and *ESCAPE Messages as *DIAG
     d ERR05           pr
     d  MT                            1    const options(*nopass)
      * Send *DIAG Message
     d ERR10           pr
     d  MI                            7    const
     d  MF                           10    const options(*omit:*nopass)
     d  MD                          128    const options(*omit:*nopass)
     d  MT                            1    const options(*nopass)
      * Send *ESCAPE Message
     d ERR11           pr
     d  MI                            7    const
     d  MF                           10    const options(*omit:*nopass)
     d  MD                          128    const options(*omit:*nopass)
     d  MT                            1    const options(*nopass)

Check for Legacy

The most important part of this trigger program, and all the others that use the same model, is the ability to determine whether or not the application program which initiated the event is a “Legacy” program or a “New” program which is able to handle trigger program errors.

As this checking routine is likely to be used by many different trigger programs, I decided to create a procedure and imbed it, along with the I/O Server for the “Error Log File”, into a Utility Service program from where both could be accessed by any trigger.

The following code defines the “CheckLegacy” procedure in the trigger program and allows it to be called as required. The procedure returns a Boolean indicator as a ‘1’ if the program is a legacy program and a ‘0’ if not. The actual name of the program is returned in the parameter of the call as this is required for the logging of an error in the case of a legacy program.

     d CheckLegacy     pr              n   extproc('UTLSRV@001A')
     d  Program                      10

The procedure inside the service program is named “UTLSRV@001A”

Error Log File (ERRLOGF)

The following table is offered as a starting definition, but obviously would need to be modified for any additional requirements you may have. Changes to this file would also require changes to the ERRLOGF$ I/O Server module and the re-creation of the service program UTLSRV@@.

This is the file into which the error log records are written for all errors encountered by a trigger program when called from a legacy program.

CREATE TABLE ERRLOGF (
       FILENM CHAR(10)      NOT NULL DEFAULT,
       EVENTI CHAR(1)       NOT NULL DEFAULT,
       PROGNM CHAR(10)      NOT NULL DEFAULT,
       LOGDTS TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
       ERRMSG CHAR(7)       NOT NULL DEFAULT,
       ERRTXT CHAR(132)     NOT NULL DEFAULT,
       BEFORE VARCHAR(2048) NOT NULL DEFAULT,
       AFTER  VARCHAR(2048) NOT NULL DEFAULT)
       RCDFMT ERRLOGFR;
LABEL ON TABLE ERRLOGF IS 'Legacy App. Error Log File' ;
LABEL ON COLUMN ERRLOGF (
	 FILENM IS 'File Name           ' ,
	 EVENTI IS 'Event               Ind.' ,
	 PROGNM IS 'Program             Name' ,
	 LOGDTS IS 'Log Entry           Timestamp' ,
	 ERRMSG IS 'Error               Message' ,
	 ERRTXT IS 'Error Text          ' ,
	 BEFORE IS 'Before Image        ' ,
	 AFTER  IS 'After Image         ');
LABEL ON COLUMN ERRLOGF (
	 FILENM TEXT IS 'File Name' ,
	 EVENTI TEXT IS 'Event Ind.' ,
	 PROGNM TEXT IS 'Program Name' ,
	 LOGDTS TEXT IS 'Log Entry Timestamp' ,
	 ERRMSG TEXT IS 'Error Message' ,
	 ERRTXT TEXT IS 'Error Text' ,
	 BEFORE TEXT IS 'Before Image' ,
	 AFTER  TEXT IS 'After Image');

The following code is used to define the ERRLOGF structure and the I/O Server for the Error Log File.

 
    d ERRLOGFP        s               *   inz(%ADDR(ERRLOGFR))
     d ERRLOGFR      e ds                  extname('ERRLOGF')
      * Write Log Record Service Procedure
     d ERRLOGF$        pr
     d  ParmPointer                    *   const
     d  RecPointer                     *   const
     d  MsgData                     128    const

Additional “D” Specs.

   
  d DFT             pr
     d VAL             pr
     d INS             pr
     d DLT             pr
     d UPD             pr
     d Legacy          s               n
     d Error           s               n   inz('0')

Trigger Program Mainline

This mainline portion of this trigger program controls the conditional processing of the trigger program, determining what the event was and what needs to be done. Remember that this trigger is only attached to the *BEFORE time of the file and therefore the only processing that needs doing is for the *BEFORE events.

Another thing worth remembering is that this program only processes one record during each call from RDBMS and therefore things such as repetitive (cycle) type processing are not a factor.

   
 /free
       monitor;    // Catch-All Monitor Group for Unexpected Errors
          Legacy = CheckLegacy(PROGNM); // Set Legacy Indicator & Program Name
          reset Error;                  // Reset Error Indicator
          PP = %addr(P);                // Set the Parameter Pointer to the Address of “P”    
          select;
              when P.Event='1';      // Insert Event
                   NP = PP + P.NewOS;   // Set Pointer for New Record Image
                   DFT();               // Execute “Default”  Procedure
                   INS();               // Execute “Insert”   Procedure
                   VAL();               // Execute “Validate” Procedure    
              when P.Event='2';      // Delete Event
                   OP = PP + P.OldOS;   // Set Pointer for Original Record Image 
                   DLT();               // Execute “Default”  Procedure 
              when P.Event='3';      // Update Event
                   OP = PP + P.OldOS;   // Set Pointer for Original Record Image 
                   NP = PP + P.NewOS;   // Set Pointer for New Record Image 
                   DFT();               // Execute “Default”  Procedure
                   UPD();               // Execute “Update”   Procedure
                   VAL();               // Execute “Validate” Procedure    
          endsl;
          if Error;                                 // At Least One Error has been found
             ERR05();                               // Percolate *DIAG and *ESCAPE Messages
             ERR11('ERR0035':'ERRMSGF':'FILEA_B0'); // Send *ESCAPE Message
          endif;                                    // Legacy Errors are Logged at Validation,
                                                    // and do not generate a final *ESCAPE Error
       on-error;                        // Unexpected Error Condition.
          if Legacy;                                // Legacy Program
             ERRMSG = 'ERR0030';                    // Set Specific Error Message Number
             ERRLOGF$(PP:ERRLOGFP:'Unexpected');    // Log One Error Record for Legacy
          else;                                     // not Legacy
             ERR05();                               // Percolate *DIAG and *ESCAPE Messages
             ERR11('ERR0030':'ERRMSGF':'FILEA_B0'); // Send *ESCAPE Message
          endif;
       endmon;
       return;
      /end-free

Defaults” Procedure

This procedure is included in *BEFORE trigger programs and is used as required to ensure that correct default values are inserted into fields not supplied by the application.

This routine can also be used for such things as inserting the users id. into an appropriate field in the record and thereby overwriting the value supplied by the application program, as well as supplying values for fields not provided by the application. Below is an example.

     
p DFT             b
      /free
       monitor;
          if N.FIELD3 = *blank;
             N.FIELD3 = 'N';
          endif;
       on-error;
          ERR03();
       endmon;
      /end-free
     p DFT             e

“Insert” Procedure

This procedure is specifically for the “Insert” event and contains code such as validations, the setting of defaults or anything else which is applicable to the inserting of a record into the database.

The example below shows the setting of a timestamp for the creation of the record. This field is not included in the sample file.

     p INS             b
      /free
       monitor;
          N.CREATE_DTS = %timestamp();                         
       on-error;
          ERR03();
       endmon;
      /end-free
     p INS             e

“Delete” Procedure

The “Delete” procedure is generally not required in the *BEFORE trigger program, but is included for completeness.

It is used to provide delete specific coding which is applied before the record is deleted from the file.

This procedure is more common in an *AFTER trigger program where database synchronization often follows the deletion of a record from a file.

   
 p DLT             b
      /free
       monitor;
          // Deletion coding
       on-error;
          ERR03();
       endmon;
      /end-free
     p DLT             e

“Update” Procedure

The “Update” procedure contains coding which is specific to the *UPDATE event of the trigger.

One of the most common uses of this procedure is to protect certain fields in the record from change by the application.

A good example of this would be the example field used in the “Insert” procedure above. As this CREATE_DTS field is populated on insert it should never be changed by any subsequent update.

The example code below shows how this is done.

   
  p UPD             b
      /free
       monitor;
          N.CREATE_DTS = O.CREATE_DTS;
       on-error;
          ERR03();
       endmon;
      /end-free
     p UPD             e

Validation Procedure

The “Validation” procedure is always executed by both the *INSERT and *UPDATE events in the trigger program because the validation rules need to be applied to all fields in both cases to ensure integrity.

Any event specific validations are previously applied by the “Insert” and “Update” procedures. These event specific field validations are not repeated in the general “Validation” procedure.

The sample code below contains examples of two field validations.

First a check is done to determine if the field is in error. If not, the validation simply passes to the next field to be checked.
if the field is in error then one of two actions needs to be performed depending on whether the "Legacy" indicator is on or not.

If Legacy then the error is logged and validation continues, otherwise the error indicator is set on and the *DIAG message for the error is sent. All *DIAG errors and the final *ESCAPE error are percolated to the RDBMS, for handling by the application, when the validation routine is complete.

It is probably worth mentioning here that a condition can arise in which a field found to be in error makes it impossible for a subsequent field or fields to be successfully validated.

In a case like this the sequence in which fields are validated becomes important (dependent validations last) and the first of the significant validations to fail should trigger a "return;" from the Validation routine.

The error message associated with this "significant" error should indicate that there are other fields not validated as a result of this error. This is done to warn the user that the correction of an error may still result in additional errors.

   
 p VAL             b
      /free
       monitor;

          if %check('YN':N.FIELD3) <> 0;   // FIELD3 must contain "Y" or "N"
             if Legacy;                              // Log Error Record for Legacy
                ERRMSG = 'ERR0014';                  // Set Specific Error Message Number
                ERRLOGF$(PP:ERRLOGFP:'FIELD3');      // Log Error Record for Legacy
             else;
                Error = *on;
                ERR10('ERR0014':'ERRMSGF':'FIELD3'); // Send *DIAG Message to DBMS
             endif;
          endif;
          
          if N.FIELD4 < 1;                     // FIELD4 may not be less that 1.
             if Legacy;                              // Log Error Record for Legacy
                ERRMSG = 'ERR0026';                  // Set Specific Error Message Number
                ERRLOGF$(PP:ERRLOGFP:'FIELD4');      // Log Error Record for Legacy
             else;
                Error = *on;
                ERR10('ERR0026':'ERRMSGF':'FIELD4'); // Send *DIAG Message to DBMS
             endif;
          endif;
       on-error;
          ERR03();
       endmon;
      /end-free
     p VAL             e

Legacy Check Module

This module, which can be bound into any service program, in this case UTLSRV@@, has only one section of the code that needs to be customized for an organization.

The data structure "Libs", shown below, needs to be updated to contain a list of all libraries (schemas) which are considered "Legacy" schemas. A program calling the trigger from one of these schemas will be flagged as a legacy program and treated accordingly by the trigger program

Simply by moving a program out of one of these libraries into another, which is not listed here, will cause the trigger program to respond to an error with the conventional method of error percolation.

   
  *===============================================================
     d Libs            ds
     d  Lib1                         10    inz('Library1')
     d  Lib2                         10    inz('Library2')
     d  Lib3                         10    inz('Library3')
     d  Lib4                         10    inz('Library4')
     d  Lib5                         10    inz('Library5')
     d  Lib6                         10    inz('Library6')
      *===============================================================
The complete source member for this module can be found in the Open Source section of the AO Website

Conclusion

This trigger program will need to be customized for each file to which it is added and will provide a small-step, no-risk method for tuning the business rules to enhance the integrity of the database.

It must be noted that the errors which are logged for legacy programs would in all probability be logged to the same ERRLOGF file for all files to which a trigger program of this style is attached. It is not recommended that each file logs its errors to a different log file as the management of such a situation would become very complicated.

Once the addition of this type of trigger to the database has been started, it is important that a process of viewing the ERRLOGF file and resolving the logged errors be put in place, to avoid a buildup of repeated errors and minimized benefits from the exercise.