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.

So let's start with looking to see if the file I will be using has any triggers on it. I use the Display File Description command, DSPFD, with the information type for trigger, see below:

  DSPFD FILE(MYLIB/TESTFILE) TYPE(*TRG)

I am not going to give all of what is displayed on the screen, just the details for the insert trigger:

Trigger Description
  Trigger name  . . . . . . . . . . . . . . . : TRG        QSYS_TRIG_MYLIB_
        _____TESTFILE___000001
    Trigger library . . . . . . . . . . . . . :            MYLIB
    Trigger state . . . . . . . . . . . . . . : STATE      *ENABLED
    Trigger status  . . . . . . . . . . . . . :            *OPERATIVE
    Trigger event . . . . . . . . . . . . . . : TRGEVENT   *INSERT
    Trigger time  . . . . . . . . . . . . . . : TRGTIME    *AFTER
    Allow repeated change . . . . . . . . . . : ALWREPCHG  *NO
    Program Name  . . . . . . . . . . . . . . : PGM        TRGPGM
      Library . . . . . . . . . . . . . . . . :            MYLIB

This shows me that there is a trigger for the insert. The Trigger calls the program TRGPGM in the library MYLIB. I am going to replace that program with a new one, which I will create in this post.

When a Trigger is executes two parameters are passed to the Trigger program. It will not surprise those of you familiar with this site that I call them Parm1 and Parm2. Both are data structures. Parm1 contains all the information about the Trigger and the data, and Parm2 contains the length of that data.

In my program the data structures could look like:

01  dcl-ds Parm1 ;
02    File char(10) ;            // File name
03    Library char(10) ;         // Library file is in
04    Member char(10) ;          // Member name
05    TriggerEvent char(1) ;     // Trg event 1=Add 2=Delete 3=Change 4=Read
06    TriggerTime char(1) ;      // Trigger time
07    CommitLock char(1) ;       // Commit lock level
08    *n char(3) ;               // Reserved
09    CCSID int(10) ;            // CCSID
10    *n char(8) ;               // Reserved
11    BeforeOffset int(10) ;     // Offset to Before image
12    BeforeLength int(10) ;     // Length of Before image
13    BeforeNullOffset int(10) ; // Offset to Before null byte map
14    BeforeNullLength int(10) ; // Length of Before null byte map
15    AfterOffset int(10) ;      // Offset to After image
16    AfterLength int(10) ;      // Length of After image
17    AfterNullOffset int(10) ;  // Offset to After null byte map
18    AfterNullLength int(10) ;  // Length of After null byte map
19    *n char(16) ;              // Reserved

      // This part is file dependent
20    BeforeImage char(203) ;    // Before image (= Record length)
21    BeforeNulls char(30) ;     // Before null byte map (1 byte per field)
22    *n char(7) ;               // Just to make the second record line up
23    AfterImage char(203) ;     // After image
24    AfterNulls char(30) ;      // After null byte map
25  end-ds ;

26  dcl-ds Parm2 ;
27    Parm1Length int(10) ;      // Length of Parm1
28  end-ds ;

A fixed format equivalent of this is available at the bottom of this post here

The fixed part of the data structure, lines 1 – 19, remain the same the rest of it, lines 20 – 24, do change depending upon the file.

In the above example the file has a record length of 203, so the Before image, line 20, and After image, 23, have to be that length. The file has 30 fields so the null byte maps, lines 11 and 24, have to be 30 long, one character for each field. And there is a filler between the Before and After images is 7 characters. I call it a filler as I need 7 characters in that place to line up the After image correctly.

This part of the data structure changes with a different file, for example:

     // This part is file dependent
20    BeforeImage char(53) ;   // Before image (= Record length)
21    BeforeNulls char(6) ;    // Before null byte map (1 byte per field)
22    *n char(21) ;            // Just to make the second record line up
23    AfterImage char(53) ;    // After image
24    AfterNulls char(6) ;     // After null byte map

This file has a record length of 53 with 6 fields. The filler between the before and after images is 21 characters.

I have not been able to come up with a formula to calculate what the size of the filler. Which is why I use an alternative method that has allowed me to create a Trigger program that will work with any file.

But before I start showing RPG code let me show you the file that I will be applying the trigger upon:

01  A          R TESTFILER
02  A            F1             3A
03  A            F2             5P 2
04  A            F3              L
05  A            F4              T
06  A            F5              Z
07  A            F6             3S 0

My Trigger output file, which I am calling T_TESTFILE, is very similar, just with five new fields added to the start of the record:

01  A                                      REF(TESTFILE)
02  A          R RCDFORMAT
03  A            TRGTIME         Z
04  A            JOBNAME       10A
05  A            JOBUSER       10A
06  A            JOBNBR         6S 0
07  A            TRGTYPE        2A
08  A            F1        R
09  A            F2        R
10  A            F3        R
11  A            F4        R
12  A            F5        R
13  A            F6        R

I have written a program that generates the Trigger output files for me, as all of mine are, basically, the same. It saves me time rather than I have to manually code all the file fields.

My trigger program is very, very simple it just looks like:

01  ctl-opt option(*srcstmt) ;

02  dcl-c TheFile const('TESTFILE') ;
03  dcl-c NbrOfFields const(6) ;

04  dcl-f Outfile usage(*output)
                    extfile('MYLIB/T_TESTFILE')
                    extdesc('MYLIB/T_TESTFILE')
                    rename(RCDFORMAT:OutMember) ;

05  /copy mylib/devsrc,triggerpgm

Yes that is it, just five lines of code.

Line 1: In my own programs I do use a lot of Control Options, to see which ones I use see Which Control options/H-specs do you use?. In this example I want to have a decent view when I use debug so I am using the *SRCSTMT keyword.

Line 2: This constant contains the name of the file that the Trigger will be placed upon.

Line 3: This constant contains the number of field that the file has.

Line 4: I am using a generic name for my Trigger output file, Outfile. But I am overriding it to the file I want using the EXTFILE keyword. The EXTDESC keyword is needed so that the RPG compiler knows what file is called. I use RENAME rename the record format name of the Trigger output file to be something generic.

That's it. The code for my Trigger program is just five statements. This is because all the code to do the Trigger "magic" in the source code copied into this program from the member TRIGGERPGM when this is compiled.

When I compile this program I do so with the Debug view parameter set to list, DBGVIEW(*LIST). To learn why see Debug views finding your favorite.

Warning: I have used the code listed below for numerous different Trigger programs and found that it works from small to large files. You may have something different with the files on your IBM i, therefore, I cannot guarantee this will work for you.

To make it easier for me to explain the code I am going to give in two parts. First the definitions, and then the code that actually does the work of outputting to the Trigger output file.

01  dcl-ds PgmDs psds qualified ;
02    JobName char(10) pos(244) ;
03    JobUser char(10) pos(254) ;
04    JobNumber zoned(6) pos(264) ;
05  end-ds ;

06  dcl-ds Parm1 ;
07    TriggerEvent char(1) pos(31) ;
08    BeforeOffset int(10) pos(49) ;
09    BeforeNullOffset int(10) pos(57) ;
10    AfterOffset int(10) pos(65) ;
11    AfterNullOffset int(10) pos(73) ;
12  end-ds ;

13  dcl-s Parm2 char(2) ;

14  dcl-s BeforeRecordPointer pointer ;
15  dcl-ds BeforeSpace extname(TheFile)
                         qualified
                         based(BeforeRecordPointer) ;
16  end-ds ;

17  dcl-s BeforeNullPointer pointer ;
18  dcl-ds BeforeNullSpace extname(TheFile)
                             qualified
                             based(BeforeNullPointer) ;
19  end-ds ;

20  dcl-s AfterRecordPointer pointer ;
21  dcl-ds AfterSpace extname(TheFile)
                        qualified
                        based(AfterRecordPointer) ;
22  end-ds ;

23  dcl-s AfterNullPointer pointer ;
24  dcl-ds AfterNullSpace extname(TheFile)
25                          qualified
                            based(AfterNullPointer) ;
26  end-ds ;

27  dcl-ds FileFields extname(TheFile) ;
28  end-ds ;

29  dcl-s FieldNulls char(1) dim(NbrOfFields) ;

Lines 1 – 5: Just a standard Program Status Information for the Job name, User profile, and Job number of the job that is causing this Trigger to execute.

Lines 6 – 12: In this example these are just the bits of the Trigger information data structure I am going to be using. In my own program I have the whole data structure here.

Line 13: I need to define the second input parameter, Parm2, even though I am not going to use it.

The "magic" starts on line 14. To enable this program to work with any size file with any number of fields I am using pointers so that I can dynamically allocate and access areas of memory where the data is, rather that have to hard code these areas using traditional fields.

Lines 14 – 16: The pointer for the Before image of the data is on line 14, followed by a data structure to contain the retrieved data.

Lines 17 – 19: The pointer for the Before null byte map, and then a data structure to contain that data. If you are unfamiliar with nulls, null byte maps, and how to handle null in RPG I recommend you read Handling null in RPG.

Lines 20 – 26: Is the After image and null byte maps for pointers and associated data structures.

Lies 27 – 28: This is an external data structure based upon the file we are going to put the Trigger on. This will allow me to move data from the Before and After images to, and then write out to the Trigger output file.

Line 29: I have defined a generic null byte map just in case my file contains null capable fields.

And now the code that writes to the Trigger output file:

30  C     *entry        plist
31  C                   parm                    Parm1
32  C                   parm                    Parm2

33  BeforeRecordPointer = %addr(Parm1) + BeforeOffset ;

34  BeforeNullPointer = %addr(Parm1) + BeforeNullOffset ;

35  AfterRecordPointer = %addr(Parm1) + AfterOffset ;

36  AfterNullPointer = %addr(Parm1) + AfterNullOffset ;

37  TRGTIME = %timestamp() ;
38  JOBNAME = PgmDs.JobName ;
39  JOBUSER = PgmDs.JobUser ;
40  JOBNBR = PgmDs.JobNumber ;


41  if (TriggerEvent = '1') ;  //Add
42    TRGTYPE = 'I' ;
43    FileFields = AfterSpace ;
44    FieldNulls = AfterNullSpace ;
45    write OutMember ;

46  elseif (TriggerEvent = '2') ;  //Delete ;
47    TRGTYPE = 'D' ;
48    FileFields = BeforeSpace ;
49    FieldNulls = BeforeNullSpace ;
50    write OutMember ;

51  elseif (TriggerEvent = '3') ;  //Update ;
52    TRGTYPE = 'U0' ;  //= Update before image
53    FileFields = BeforeSpace ;
54    FieldNulls = BeforeNullSpace ;
55    write OutMember ;

56    TRGTYPE = 'U1' ;  //= Update after image
57    FileFields = AfterSpace ;
58    FieldNulls = AfterNullSpace ;
59    write OutMember ;

60    else ;
61      return ;

62  endif ;

63  feod Outfile ;

64  return ;

Lines 30 – 32: Yes, I have used a PLIST to receive the parameters passed to this Trigger program. Trust me it makes it lot easier to have one than a MAIN procedure when writing a generic Trigger program.

Lines 33 – 36: These lines allocate the areas of memory to the various pointers.

Lines 37 – 40: These fields are used so that I can tell which job caused the insert, delete, or update and at what time.

Lines 41 – 45: When a record is added/inserted into the file this part of the code is executed. As this is an insert I do not have to bother with the Before image as there is none. Therefore, I move the content of the After space data structure and write to the Trigger output file. I am not bothering with the null byte map as I know that the files I work with don't have nulls. If they did I would need to look at the After null byte map to determine which fields to make null.

Lines 46 – 50: For a delete I only want the Before image as there cannot be anything in the After.

lines 51 – 59: With an update I want to capture both the Before and After images. I use "U0" to indicate the before data, and "U1" for the after. Why? Before "UB" (Update Before) would come after "UA (Update After) if I sorted by the Trigger Type field, TRGTYPE.

Lines 60 – 61: There is a fourth Trigger Event value, "4", used to indicate the record is being read. In this scenario I do not care about the file being read as it does not update the file.

Line 63: What is FEOD? It stands for Force End Of Data, and if there is any data in the output buffer when I use the FEOD it is written to the file. If I do not use the FEOD that data resides in the output buffer until it reaches the capacity when it is written to the file. This could happen sometime later. FEOD makes sure the data is written now.

Line 64: Just a RETURN no *INLR as by returning the output file is not closed, and the next time the trigger executes it does not have to spend time to open the output file.

In my opinion Trigger programs be written to execute quickly as control is not returned to the original program until the Trigger program finishes. This is why I do not key my Trigger output files.

Now I have my Trigger program, compiled with DBGVIEW(*LIST), I need to attach it to the file. To do this I use the Add Physical File Trigger command, ADDPFTRG. I have to use the command three times, once for the insert trigger, again for the delete, and lastly for the update. I use the Replace trigger keyword, RPLTRG(*YES), to replace the existing triggers:

  ADDPFTRG FILE(MYLIB/TESTFILE) TRGTIME(*AFTER) +
             TRGEVENT(*INSERT) PGM(MYLIB/TRGPGM1) +
             RPLTRG(*YES)

  ADDPFTRG FILE(MYLIB/TESTFILE) TRGTIME(*AFTER) +
             TRGEVENT(*DELETE) PGM(MYLIB/TRGPGM1) +
             RPLTRG(*YES)

  ADDPFTRG FILE(MYLIB/TESTFILE) TRGTIME(*AFTER) +
             TRGEVENT(*UPDATE) PGM(MYLIB/TRGPGM1) +
             RPLTRG(*YES)

Notice that I have the time the Trigger is called set to "after", TRGTIME(*AFTER), this means that the Trigger will be execute after the insert, delete, or update. Therefore, if the Trigger program errors the data was output to TESTILE without problem.

Now if I use the DSPFD with TYPE(*TRG) I will see my Trigger program in place.

if I want to remove a Trigger from a file I can just use the Remove Physical File Trigger command, RMVPFTRG. I could remove each trigger, but fortunately this command offers an option of "*ALL".

  RMVPFTRG FILE(MYLIB/TESTFILE)

I can use Trigger programs for more than just saving data when a record is changed. I can also validate the data before the record is written file, and if it is not within the business rules, return with an error rather than update. I can also write SQL triggers. Both of these subjects could become future posts.

 

You can learn more about this from the IBM website:

 

This article was written for IBM i 7.2, and should work for earlier releases too.

 

Fixed format data structures for Parm1 and Parm2

01  D Parm1           DS
02  D  File                   1     10
03  D  Library               11     20
04  D  Member                21     30
05  D  TriggerEvent          31     31
06  D  TriggerTime           32     32
07  D  CommitLock            33     33
08  D  Reserved1             34     36
09  D  CCSID                 37     40B 0
10  D  Reserved2             41     48
11  D  BeforeOffset          49     52B 0
12  D  BeforeLength          53     56B 0
13  D  BeforeNullOffset...
14  D                        57     60B 0
15  D  BeforeNullLength...
16  D                        61     64B 0
17  D  AfterOffset           65     68B 0
18  D  AfterLength           69     72B 0
19  D  AfterNullOffset...
20  D                        73     76B 0
21  D  AfterNullLength...
22  D                        77     80B 0
23  D  Reserved3             81     96

     * This part is file dependent
24  D  BeforeImage                 203
25  D  BeforeNulls                  30
26  D  Filler                        7
27  D  AfterImage                  203
28  D  AfterNulls                   30

29  D Parm2           DS
30  D  Parm1Length            1      4B 0

Return

24 comments:

  1. That's wonderful work Simon. Thank you for sharing such a useful information.

    ReplyDelete
  2. Hi Simon

    An even better approach in my opinion is the use of Temporal Tabels (a new feature of DB2 Version 7.3).

    Regards
    Jan

    ReplyDelete
    Replies
    1. I must admit I have not yet had a chance to use Temporal Tables. I am looking forward to doing so.

      But can we see what data was changed, in say a 24 hours period, using Temporal Tables?

      Delete
    2. Most shops are on 7.1 with TR, at the best. It will be some time before 7.3 kicks in, and even more before one can convince management to use them. SQL triggers were introduced 15 years ago, and I am still struggling to convince that they are the way to go, not RPG or Cobol triggers.

      Delete
    3. Yes you can!

      SELECT *
      FROM stockFile
      FOR SYSTEM_TIME between '2016-09-04-09.00.00.000000'
      and '2016-09-05-09.00.00.000000'
      WHERE product = 'MY_PRODUCT'

      Delete
    4. I agree that temporal tables look to be an excellent invention (I cannot wait to have a go with them). But in this scenario I can see what the record was at that time, but when did it change? Who changed it?

      Delete
    5. When and who Simon?
      In our db every record has 13 informational fields including this info.
      CRTPGNM Creation program ............................. : EW023RGB
      CRTUSER Creation user ................................ : PEPPI
      CRTDATE Creation date ................................ : 20160825
      CRTTIME Creation time ................................ : 130555
      CHGPGNM Change program ............................... : EW023RGB
      CHGUSER Change user .................................. : KOKKI
      CHGDATE Change date .................................. : 20160912
      CHGTIME Change time .................................. : 095026
      JOBNAM Job name last event (CRT or CHG)............ : QPADEV0019
      JOBUSR Job user last event (CRT or CHG)............ : KOKKI
      JOBNBR Job number last event (CRT or CHG)............ : 815768
      CALLER1 Caller of CRTPGNM (if CRT) or CHGPGNM (if CHG) : QCMDEXC
      CALLER2 Caller of CALLER1 ............................ : MN051RGI

      Delete
    6. Hi : SYSTEM_TIME is filed of table or its General , i am not quite clear can you please put some light ?

      SYSTEM_TIME between '2016-09-04-09.00.00.000000'
      and '2016-09-05-09.00.00.000000'

      Delete
    7. SYSTEM_TIME is a column you use with Temporal tables.
      See here.

      Delete
  3. RPG triggers are a pain, and that is why 15 years ago when SQL triggers were introduced, I never went back to RPG triggers. Got them replaced with SQL triggers wherever I went. They are native to DB2, they can be on columns, they are lean, and they process in sets. Simply no comparison in efficiency.

    ReplyDelete
    Replies
    1. Hola Hassan, por favor me regalas un ejemplo de desencadenantes SQL en DB2. Gracias

      Delete
  4. Great article Simon. Couple of thoughts for what it's worth. For high volume environments asynchronous logging performs much better and doesn't significantly slow down the job that makes the changes.

    Also trigger maintenance requires exclusive lock on the table,therefore it makes sense to externalize the code and leave the trigger program as a simple path-thru.

    Cheers!
    Dima

    ReplyDelete
  5. I use a comparable technique, with a generic "launcher" that serves as the actual trigger program, but does no business logic. It reads through a rules table looking for a match on file and event (plus other criteria), and contains the name of a "handler" program that does the actual business logic. A variable prototype is used to call the handler. This way you can make on-the-fly changes to the handler without being locked out of it by DB2. Plus the handler honors the job's library list, so it's better for testing. There's even a kill switch if I need to disable the handler without having to get everyone out of the file.

    The launcher's code is in 4 /COPY members, so I have a command program that accepts the file name & event, and actually creates the RPG source, compiles it and performs the ADDPFTRG. It only has to be done once per file/event.

    ReplyDelete
  6. Hi All ,

    For what its worth, Alan Campin wrote a "Trigger Mediator" that utilizes also a similar approach, one might be able to retro fit the new changes and make the triggers more dynamic in nature..

    Goto this site to have a look : http://www.think400.dk/downloads.htm

    ReplyDelete
  7. The default for CRTDUPOBJ is TRG(*YES). This means if someone like a developer or power user clones the PF and starts updating data in the close, perhaps for unrelated testing, it is firing the trigger(s)!

    For this reason, I check the system name and object name and library name in the trigger.

    On a development system where the PF can travel through developer, integration, Q/A and final libraries, I allow any library. And the PF name must match the expected PF name like PARTMAST instead of anything else like PARTMASTWK or PARTTEST.

    On a production system, it must be the expected library name and object name else the trigger program just ends.

    Ringer

    ReplyDelete
  8. If you want the program to run a nanosecond faster, use
    feod(n) instead of feod.

    ReplyDelete
  9. You might also want to name your constants like this:

    dcl-c trEvent_add ‘1’;
    dcl-c trEvent_update ‘2’;
    dcl-c trEvent_delete ‘3’;

    That way you can remove the redundant end of line comments

    ReplyDelete
    Replies
    1. Okay, I messed up. Delete and update naming should be switched places ;-)

      Delete
  10. In 7.2 and 7.3, you can define the data structures for the null-byte-maps using EXTNAME(filename:*NULL).

    I would define FieldNulls that way too; then you probably wouldn't need to hardcode NbrOfFields.

    But if you can't use *NULL, it would probably be better to define BeforeNullSpace and AfterNullSpace as arrays of CHAR(1) with DIM(NbrOfFields) rather than defining them as externally-described data structures.

    Basically, define FieldNulls and BeforeNullSpace and AfterNullSpace the same. The way it is now, when you assign FieldNulls = BeforeNullSpace where FieldNulls is an array of CHAR(1) and BeforeNullSpace is a data structure, every element of FieldNulls gets assigned the first byte of the null-byte-map.

    ReplyDelete
  11. Hi Simon,

    Thanks for this post. but it is very confusing for me as there are many source program mentioned I am really not sure which one to follow.

    ReplyDelete
  12. There are multiple program's source mentioned above.
    Check the sequence numbers to the left, if one snippet of code continues from the previous one the numbers continue. if the number restarts at 1 then this is a different source member.

    ReplyDelete
  13. Our trigger pgm is submitting another pgm to update a 3rd table with the info that is in our trigger outfile. The problem is that the same update pgm is getting called once for each record that got updated. So if 10 records get changed our update program gets called 10 times to update those same 10 records. One call does all of the updates that are needed. Is there a way to get the trigger to just submit one time for all records in the file?

    ReplyDelete
    Replies
    1. Each update is a separate database event, with control "owned" by the database.

      That is why the trigger program is called once for every time an update is performed.

      Delete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.