This is a very easy example, however, it clarifies several things.

Whenever the system calls this program , it(System) passes basically two parameters. The first parameter contains all information we need in the program. e.g. the file, library and member names, Relative record number of the inserted record, commitment lock type, trigger event (Insert, Update etc), trigger time(Before/After), the offset of the old and new records and before and after(Trigger event) images of the database record etc.

The second parameter contains the length of the first parameter. This is because the system passes a variable length parameter(first entry parameter). Though it will be fixed for a specific file.

---

About this example
In the current example I want the backup of a records of a file to be written to a separate file. For this, I write a trigger program which is to be invoked after a new record has been written to the main file (EMPDATA1). In this example, I am interested in only the offset length after which the newly inserted record begins in the first parameter passed to this program.

Before seeing the code I think it will be very appropriate to give the source of the DDS of the EMPDATA1 and EMPDATA2.

EMPDATA1
A          R EMPDTA01                          
A            NAME          10                  
A            ADDRESS       10                  

EMPDATA2
A          R EMPDTA02                          
A            NAME          10                  
A            ADDRESS       10               

---

0001.00 FEMPDATA2  IF A E             Disk                                     
0002.00  *                                                                     
0002.01  * Define the prototype of the program                                 
0002.02  *                                                                     
0002.03 D DB2TRG          PR                                                   
0002.04 D @Parm3                      4096    Options(*VarSize)                
0002.05 D @Parm4                         4B 0                                  
0002.06  *                                                                     
0002.07  * Define the procedure interface of the program                       
0002.08  *                                                                     
0002.09 D DB2TRG          PI                                                   
0002.10 D @Parm1                      4096    Options(*VarSize)                
0002.11 D @Parm2                         4B 0                                  
0002.12  *                                                                     
0002.13  * Define the data structure which will be used to retrieve the database
0002.14  * properties.                                                         
0002.15  *                                                                      
0002.16 D @DBProps        DS                                                    
0002.17 D File                    1     10                                      
0002.18 D Lib                    11     20                                      
0002.19 D Member                 21     30                                      
0002.20 D TrgEvent               31     31                                      
0002.21 D TrgTime                31     31                                      
0002.22 D CmtLockType            31     31                                      
0002.23 D CCSID                  37     40B 0
        D RRN                    41     44B 0 //Added later                           
0002.24 D OrigOffSet             49     52B 0                                   
0002.25 D OrigLength             53     56B 0                                   
0002.26 D OffSetMap              57     60B 0                                   
0002.27 D LengthOfMap            61     64B 0                                   
0002.28  * New records offset length                                            
0002.29 D NewOffSet              65     68B 0                                   
0002.30 D NewLength              69     72B 0                                   
0002.31 D NewSetMap              73     76B 0                                   
0002.32 D LengthOfNMap           77     80B 0                                   
0002.33 D OldRcdNull             81     96                                      
0002.34 D OldRcd                 97    116                                      
0002.35 D OldRcdMap             117    120                                      
0002.36 D NewRcd                121    140                                      
0002.37 D NewRcdNull            141    144                                      
0002.38  *                                                                      
0002.39  * This parameter tells the exact length of the buffer                  
0002.40  *                                                                      
0002.41 D @Buffer         DS                                                    
0002.42 D LengthOfParms           1      4B 0                                   
0005.01 C                   Eval      @DBProps = @Parm1                         
0005.03 C                   Eval      NAME = %SubSt(@Parm1: NewOffSet+1 : 10)   
0005.04 C                   Eval      ADDRESS = %SubSt(@Parm1: NewOffset + 11:  
0005.05 C                                       10)                             
0006.00 C                   Write     EMPDTA02                                 
0007.00 C                   Eval      *InLr = *On                              
0008.00 C                   Return

---

Please note that this program uses a procedure interface hence compile this program with 15 option from wrkmbrpdm (command CRTRPGMOD) and then create program using the CRTPGM command. Alternatively, specify the keyword EXTPGM at the prototype level and compile with 14 option (Command CRTBNDRPG).

---

The steps we followed
The sequence of operations performed in the example can be given as below.

1. Define two entry parameters, one of variable length second of 4B 0.
2. Define a Data structure to retrieve the offset values. Receive the first entry parameter into this Data structure.
3. Get different fields values by using the substring function. The first field value begins from the offset +1 position of the first entry parameter of the program.
4. Once you have got your fields' values you are free to do whatever you wish to do with them!
5. Test your program by inserting a record using the SQL utility.

The above program has been tested and it works absolutely as expected. May be I will post a video tutorial on this website in coming future to illustrate exactly how it worked.

Anyways, Now that we have written the program we need to associate this program (as a trigger program) to the physical file EMPDATA1. How do we do that? We do this using the ADDPFTRG command. More about this and other commands relevant to AS400 DB2 Triggers will be discussed on the next page of this article.